How to Exclude Tables from a mysqldump SQL Export Software & Sites 15 MAY 2013

20090910-MySQL-logoWhen backing up or copying across a MySQL database, the command line mysqldump executable is invaluable in automating the creation of SQL dump files which can be used to create or populate database instances.

Now there are a myriad of options available to this little command, but the one I want to highlight today is the ability to ignore specific tables when doing a mysqldump of an entire database. This is particularly useful if you have an overly large table which doesn’t play nicely with an import process, say for example if you were storing base-encoded images in your database. –ignore-table is the switch, and in practice it looks like this:

mysqldump -u username -p database --ignore-table=database.table1 --ignore-table=database.table2 > database.sql

As you can see from the line above, it is possible to specify multiple tables to exclude by simply passing it in multiple times. Also worthwhile to note is the fact that you do need to prefix the table which is to be omitted with the database name. Failing to do this will result in an Illegal Usage error from mysqldump.


About Craig Lotter

Software developer, husband and dad to two young ladies. Writer behind An Exploring South African. I don't have time for myself any more.