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.

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

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

Method 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]

Method 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.

Method 4

This work for me

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

Method 5

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

Method 6

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.

Method 7

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).

Method 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

Method 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'`

Method 10

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating