MySQL: Dump tables with wildcard

Dumping a certain table with mysqldump goes like this:

mysqldump -u root -pPWD exampledb table1 > exampledb_table1.dump

This command exports the table “table1” from DB exampledb and stores it in the file “exampledb_table1.dump”.

To export more than one table we pass their names to this command:

mysqldump -u root -pPWD exampledb table1 table2 table3 > exampledb_tables.dump

If we need to export some tables e.g. with a given prefix (like “smw_”), we may want to use a wildcard to save us from stringing together all tablenames. This can be done like this:

mysqldump -u root -pPWD exampledb `echo "show tables like 'smw_%';" | mysql -u root -pPWD exampledb | sed '/Tables_in/d'` > exampledb.dump

Edit 2016-11-13:

Or a little smarter:

mysqldump -u root -pPWD exampledb `mysql -u root -pPWD -B -N -e "show tables like 'smw_%'" exampledb` > exampledb.dump

The “-B” and “-N” suppress the table-format and colum-header and “-e” executes the given command “show tables…”.