This little tutorial shows how to dump wildcard selected tables from a MySQL database. That’s useful if you don’t want to dump the whole database.

Background

Recently I had to export a few selected tables from a very huge database. For that I could have run a query like that to export only the required tables:

mysqldump -u USER -p --single-transaction --quick --opt --host HOST SCHEMANAME table table2 table3 table4 table5 [ ... ]

But unfortunately it was a Magento database with over 500 tables and I had to export 25 tables that I didn’t want to list manually. But fortunately there was a pattern in the requested tables (all customer tables, order tables and a few other tables completely named):

core_store
customer_*
eav_attribute
eav_entity_type
sales_flat_order
sales_flat_order_*

So I thought I could just use wildcards like that:

$> mysqldump -u USER -p --single-transaction --quick --opt --host HOST ACTUALSCHEMANAME core_store customer_* [ ... ]

But it wasn’t that simple because the mysql command doesn’t support wildcards for the tablenames to export! :-/

Solution (how to dump wildcard selected tables)

That’s why I had to find a way to dump wildcard selected tables. I found an approach in the answer from sreimer on StackOverflow.

The solution where these shell commads:

$> mysql -u USER -p -N information_schema -e "SELECT table_name FROM  information_schema.tables WHERE table_schema = 'ACTUALSCHEMANAME' AND table_name LIKE 'core_store' OR table_name LIKE 'customer_%' OR table_name LIKE 'eav_attribute' OR  table_name LIKE 'eav_entity_type' OR table_name LIKE 'sales_flat_order%';" -h HOST > tables.txt
$> mysqldump -u USER -p --single-transaction --quick --opt --host HOST ACTUALSCHEMANAME `cat tables.txt` | gzip -9 > selected_tables.sql.gz

As you can see, the first command just selects our resolved “wildcardtablenames” from the information_schema database and writes them into a text file. This file is read by the second command. So the tablenames from the textfile ar given to the mysqldump command as table parameters and only theses tables are exported to a gzipped export file.

To run it you need not only access to the actual database but also  to the information_schema database of the MySQL host (at least read-access).