Mysqldump 仅带有某些前缀/Mysqldump 通配符的表？

小开

最佳答案

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]

小开

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

小开

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.

小开

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

小开

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

小开

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

小开

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

小开

This work for me

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