Mysqldump only tables with certain prefix / Mysqldump wildcards?

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

---

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.

---

There are already a lot of good answers, but I came here with such variation:

mysql MY_DATABASE -N -u MY_MYSQLUSER -p -e 'show tables like "%MY_LIKE_CODE%";' |
xargs mysqldump MY_DATABASE -u MY_MYSQLUSER -p |
gzip > ~/backup/`date +%Y%m%d:::%H:%M:%S-MY_DAMP.sql.gz`

By this action I made a table dump by the mask like %mask% from the database to a single file. Hopefully someone will find it useful.

---

Building on some of the other nice answers here, I created shell script to make this even easier. This script generates 3 files in the output - one with the structure for all tables, one with the data for all non-excluded tables, and one with the data for all "excluded" tables (you could comment this out if you really don't need it). Then you can use which one(s) you need.

#!/bin/bash

echo -n "DB Password: "
read -s PASSWORD

HOST=yourhostname.com
USER=youruser
DATABASE=yourdatabase

MAIN_TABLES=$(mysql -h $HOST -u $USER -p$PASSWORD -D $DATABASE -Bse "SHOW TABLES WHERE Tables_in_dashboard NOT LIKE 'bigtable_%';")
STATS_TABLES=$(mysql -h $HOST -u $USER -p$PASSWORD -D $DATABASE -Bse "SHOW TABLES LIKE 'bigtable_%';")

echo "Dumping structure..."
mysqldump -h $HOST -u $USER -p$PASSWORD $DATABASE --no-data | gzip > structure.sql.gz

echo "Dumping main data..."
mysqldump -h $HOST -u $USER -p$PASSWORD $DATABASE --no-create-info $MAIN_TABLES | gzip > data.sql.gz

echo "Dumping big table data..."
mysqldump -h $HOST -u $USER -p$PASSWORD $DATABASE --no-create-info $STATS_TABLES | gzip > big_table_data.sql.gz