I have been trying to figure out a way to do this for years! I’ve had customers ask to dump tables from a database that match a certain prefix, however mysqldump doesn’t understand how to use wildcards. I used to have to list each and every table by hand in order to create a dumpfile that matched the customer’s requirements. No longer!
The following example can be used to dump all tables that begin with “javier_” from the database called “bigdatabase“:
mysql -s -r -p bigdatabase -e 'SHOW TABLES LIKE "javier_%"' | grep -v Tables_in | xargs mysqldump -p --add-drop-table bigdatabase > javier_.sql
The “-s -r” options tell the mysql client to produce a silent and raw output. The “grep -v” gets rid of the header line, and finally the xargs portion tells mysqldump what to do.
Enjoy!