Answer a question

I have this huge, messy database I am cleaning up. It houses 500+ tables, which is the result of combining Magento Enterprise with Joomla in one single DB.

To make things worse, there is a set of 70+ Joomla tables that are not in use at all. These are all prefixed with bak_.

Just deleting these bak_ tables will be easy, but I want to 'bak' them up first (see what I did there?). In my mind I can picture a command like this:

mysqldump -u username -p mydatabase bak_*

But this doesn't work. What would be the best way to do this? Thanks!

EDIT: Yes, I could explicitly list the 70 tables to include, or the ~430 tables to exclude, but I am looking for a better way to do this, if possible.

Answers

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 {}"