Taking MySQL dumps with a twist

Taking mysql dumps is pretty straightforward, but what if you needed to take a dump excluding certain tables?
For this, you have two options:

1. use the –ignore-table option:

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

This works fine as long as you don’t have too much tables to exclude.

2. The second option is to list all the tables you wish to dump:

mysqldump -u username -p database table1 table2 > database.sql

Again, pretty clean solution, but the problem still exists. If you have a database that has a lot of tables, you might be in for a treat.

Take for instance the database of a Drupal installation. Drupal databases tend to hold a large number of tables. Among those tables, you will find a lot of tables holding cached data, that you normally would not include in your dump.
So here’s a small trick to list all needed tables, without having to type them manually.

First, query the schema information for all tables names you need. As you see below, we are selecting all table names where database equals dbname and table_name does not contain the word cache. This output is then send to the tables.txt file.

mysql -N information_schema -e "select table_name from tables where table_schema = 'dbname' and table_name not like '%cache%'" > tables.txt

The final step is running the mysqldump command on your database and list all needed tables. Those table names are stored in your tables.txt file, so you simply get the text from that file using the cat command and again, store the mysql output in your sql dump file.

mysqldump dbname `cat tables.txt` > dump_file.sql

Using the backticks, the cat command will be evaluated during the mysqldump command, so only the table names listed in your file will be dumped.