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.

Nifty.