Mysqldump only tables with certain prefix / Mysqldump wildcards?

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.

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

Answer 2

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]

Answer 3

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.

Answer 4

Another oneliner to extract list of tables' name with mysql -sN … and then use each item in a "for … in … " shell loop to drop them:

for f in `mysql dbname -sN -e "SHOW TABLES LIKE 'bak\_%' "` ; do mysql dbname -rsN -e "DROP TABLE $f"; done

or (expanded version)

for f in `mysql dbname -sN -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname' AND TABLE_NAME LIKE 'bak\_%' "` ; do mysql dbname -rsN -e "DROP TABLE $f"; done

Or use "group_concat" to concatenate* names of tables, if they are short enough:

tables=`mysql dbname -srN -e "SELECT GROUP_CONCAT(TABLE_NAME SEPARATOR ',') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname'  AND TABLE_NAME LIKE 'bak\_%' "`; mysql dbname -rsN -e "DROP TABLE $tables"

*some limits like the value of "group_concat_max_len" (typically equals to 1024, cf your 70 tables) may interfere.

---

Same principle, but for dumping all tables except the ones starting with "bak_":

for f in `mysql dbname -sN -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname' AND NOT(TABLE_NAME LIKE 'bak\_%') "` ; do mysqldump -u [u] -p dbname "$f" >> dump_dbname.sql; done

Answer 5

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.

Answer 6

As of MySQL 5.7, the mysqlpump tool supports table name filtering with patterns.

Note that it's a half-baked tool, so you need to make sure it supports the required functionalities, and that it performs them correctly (eg. as of MySQL 5.7.12, the triggers export is broken).

Answer 7

This work for me

mysqldump -u USER -p DATABASE $(mysql -u USER -p -D DATABASE -Bse "show tables like 'PREFIX%'") > /tmp/DATABASE.sql

Answer 8

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

Answer 9

My solution:

mysqldump -u username -p mydatabase `mysql -B --disable-column-names -u username -p mydatabase -e "SHOW TABLES LIKE 'bak\_%'" | sed ':a;N;$!ba;s/\n/ /g'`

Answer 10

mysql DATABASE -u USERNAME -p -e 'show tables like "PREFIX%"' | grep -v Tables_in | xargs mysqldump DATABASE -u USERNAME -p > DUMP.sql