MySQL Mysqldump 仅具有某些前缀/ Mysqldump 通配符的表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5268936/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysqldump only tables with certain prefix / Mysqldump wildcards?
提问by thaddeusmt
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.
我正在清理这个庞大而凌乱的数据库。它包含 500 多个表，这是将 Magento Enterprise 与 Joomla 合并到一个数据库中的结果。
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_.
更糟糕的是，有一组 70 多个 Joomla 表根本没有使用。这些都带有前缀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:
删除这些bak_表很容易，但我想先“烤”它们（看看我在那里做了什么？）。在我的脑海中，我可以想象这样的命令：
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.
编辑：是的，我可以明确列出要包含的 70 个表，或要排除的 ~430 个表，但如果可能的话，我正在寻找更好的方法来做到这一点。
回答by sreimer
You can specify table names on the command line one after the other, but without wildcards.
mysqldump databasename table1 table2 table3
您可以在命令行上一个接一个地指定表名，但不能使用通配符。
mysqldump databasename table1 table2 table3
You can also use --ignore-tableif that would be shorter.
--ignore-table如果那会更短，您也可以使用。
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 {}"
回答by minaz
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]
回答by Stephen M. Harris
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.
所有答案都采用几乎相同的方法，但这是最简洁的语法。
回答by Andrinux
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.
通过这个操作，我通过像 %mask% 这样的掩码从数据库到单个文件进行了表转储。希望有人会发现它有用。
回答by Marcus
As of MySQL 5.7, the mysqlpumptool supports table name filtering with patterns.
从 MySQL 5.7 开始，该mysqlpump工具支持使用模式进行表名过滤。
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).
请注意，它是一个半生不熟的工具，因此您需要确保它支持所需的功能，并正确执行它们（例如，从 MySQL 5.7.12 开始，触发器导出已损坏）。
回答by eleg
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:
另一个用于提取表名称列表的单行程序mysql -sN …，然后在“for ... in ...” shell 循环中使用每个项目来删除它们：
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:
或者使用“group_concat”连接*表的名称，如果它们足够短：
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.
*某些限制，例如“group_concat_max_len”的值（通常等于 1024，参见您的 70 个表）可能会干扰。
Same principle, but for dumping all tables except the ones starting with "bak_":
相同的原理，但用于转储除以“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
回答by dtbarne
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.
以这里的其他一些不错的答案为基础，我创建了 shell 脚本以使这更容易。该脚本在输出中生成 3 个文件——一个包含所有表的结构，一个包含所有非排除表的数据，另一个包含所有“排除”表的数据（如果你真的不这样做，你可以注释掉）不需要）。然后你可以使用你需要的。
#!/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
回答by user3453061
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'`
回答by bhrached
mysql DATABASE -u USERNAME -p -e 'show tables like "PREFIX%"' | grep -v Tables_in | xargs mysqldump DATABASE -u USERNAME -p > DUMP.sql
mysql DATABASE -u USERNAME -p -e 'show tables like "PREFIX%"' | grep -v 表_in | xargs mysqldump DATABASE -u 用户名 -p > DUMP.sql
