Mysqldump only tables with certain prefix / Mysqldump wildcards?

Solution 1:

You can specify table names on the command line one after the other, but without wildcards. mysqldump databasename table1 table2 table3

You can also use --ignore-table if that would be shorter.

Another idea is to get the tables into a file with something like

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

Edit the file and get all the databases onto one line. Then do

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

To drop tables in one line (not recommended) you can do the following

mysql -NB  information_schema -e "select table_name from tables where table_name like 'bak_%'" | xargs -I"{}" mysql dbname -e "DROP TABLE {}"

Here is an easy way:

mysql [dbname] -u [username] -p[password] -N -e 'show tables like "bak\_%"' | xargs mysqldump [dbname] -u [username] -p[password] > [dump_file]

My favorite:

mysqldump DBNAME $(mysql -D DBNAME -Bse "show tables like 'wp\_%'") > FILENAME.sql

All the answers take nearly the same approach, but this is the most concise syntax.