mysql dump - 排除一些表数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13593148/
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
mysql dump - exclude some table data
提问by Marty Wallace
Is it possible, using mysql dump to export the entire database structure, but exclude certain tables data from export.
有没有可能，使用mysql dump导出整个数据库结构，但是将某些表数据排除在export之外。
Say the database has 200 tables, I wish to export the structure of all 200 tables, but i want to ignore the data of 5 specific tables.
假设数据库有200个表，我想导出所有200个表的结构，但我想忽略5个特定表的数据。
If this is possible, how is it done?
如果这是可能的，它是如何完成的？
采纳答案by Marc B
As per the mysqldump docs:
根据mysqldump 文档：
mysqldump name_of_db --ignore-table=name_of_db.name_of_table
回答by AmitP
This will produce export.sql with structure from all tables and data from all tables excluding table_name
这将生成具有来自所有表的结构和来自除 table_name 之外的所有表的数据的 export.sql
mysqldump --ignore-table=db_name.table_name db_name > export.sql
mysqldump --no-data db_name table_name >> export.sql
回答by kantholy
I think that AmitP's solution is great already - to improve it even further, I think it makes sense to create all tables (structure) first and then fill it with data, except the ones "excluded"
我认为 AmitP 的解决方案已经很棒了 - 为了进一步改进它，我认为首先创建所有表（结构）然后用数据填充它是有意义的，除了“排除”的那些
mysqldump --no-data db_name > export.sql
mysqldump --no-create-info --ignore-table=db_name.table_name db_name >> export.sql
if you want to exclude more than 1 table, simply use the --ignore-tabledirective more often (in the 2nc command) - see mysqldump help:
如果要排除 1 个以上的表，只需--ignore-table更频繁地使用该指令（在 2nc 命令中）-请参阅 mysqldump 帮助：
--ignore-table=name Do not dump the specified table. To specify more than one
table to ignore, use the directive multiple times, once
for each table. Each table must be specified with both
database and table names, e.g.,
--ignore-table=database.table
回答by Nick D
I am a new user, and do not have enough reputation to vote or comment on answers, so I am simply sharing this as an answer.
我是新用户，没有足够的声誉来对答案进行投票或评论，所以我只是将其作为答案分享。
@kantholy clearly has the best answer.
@kantholy 显然有最好的答案。
@AmitP's method dumps all structure and datato a file, and then a drop/create tablestatementat the end. The resulting file will still require you to importall of your unwanted data before simply destroying it.
@AmitP 的方法将所有结构转储data到 a file，然后是最后的drop/create table语句。生成的文件仍然需要您import在简单地销毁它之前获得所有不需要的数据。
@kantholy's method dumps all structurefirst, and then only datafor the tableyou do not ignore. This means your subsequent importwill not have to take the time to importall the datayou do not want - especially important if you have very large amounts of datayou want to ignore to save time.
@ kantholy的方法转储全部结构，然后再只data为table您不要忽略。这意味着您的后续import将不必花时间处理import所有data您不想要的 - 如果您有非常多的data要忽略以节省时间，则尤其重要。
To recap, the most efficient answer is:
回顾一下，最有效的答案是：
mysqldump --no-data db_name > export.sql
mysqldump --no-create-info --ignore-table=db_name.table_name1 [--ignore-table=db_name.table_name2, ...] db_name >> export.sql
回答by Steak
Another possibility that I use is to avoid the lines inserting data into the wanted table.
我使用的另一种可能性是避免将数据插入所需表的行。
The principle is to filter out the INSERT INTOlines using grep -v
原理是使用grep -v过滤掉INSERT INTO行
mysqldump name_of_db | grep -v 'INSERT INTO \`name_of_table\` VALUES'
or
或者
mysqldump name_of_db | grep -v 'INSERT INTO \`name_of_db\`.\`name_of_table\` VALUES'
That you can easily get into a gziped file and a separated error file
您可以轻松进入 gzip 压缩文件和单独的错误文件
mysqldump name_of_db | grep -v 'INSERT INTO \`name_of_db\`.\`name_of_table\`' | gzip > /path/dumpfile.sql.gz 2> /path/name_of_db.err
and therefore get a nice backup of what you want and know what failed if any :-)
因此，获得您想要的内容的良好备份，并知道失败的内容（如果有）:-)
回答by Fabien Salles
Previous answers don't fix the issue with the AUTO_INCREMENTwhen we export the structure and don't show how to export some specific data in tables.
以前的答案没有解决AUTO_INCREMENT我们导出结构时的问题，也没有显示如何导出表中的某些特定数据。
To go further, we must do :
要走得更远，我们必须这样做：
1/ Export the structure
1/ 导出结构
mysqldump --no-data db_name | sed 's/ AUTO_INCREMENT=[0-9]*\b//g' > export-structure.sql
2/ Export only data and ignores some tables
2/只导出数据忽略部分表
mysqldump --no-create-info --ignore-table=db_name.table_name1 [--ignore-table=db_name.table_name2, ...] db_name >> export-data.sql
3/ Export specific data in one table
3/ 导出一张表中的特定数据
mysqldump --no-create-info --tables table_name --where="id not in ('1', '2', ...)" > export-table_name-data.sql
I tried to use the --skip-optoption to reset AUTO_INCREMENTbut this also delete the AUTO_INCREMENTdefinition on the field, the CHARSETand other things
我尝试使用--skip-opt重置选项，AUTO_INCREMENT但这也删除AUTO_INCREMENT了字段上的定义，CHARSET以及其他内容
回答by CarlosRuiz
In my opinion the best answer is from Steak, the only answer really working on any case.
在我看来，最好的答案来自 Steak，这是唯一真正适用于任何情况的答案。
All the answers suggesting two dumps are wrong, or at least they can work just under certain premises.
所有暗示两个转储都是错误的答案，或者至少它们可以在某些前提下工作。
As many have pointed above you can have problems with sequences.
正如许多人在上面指出的那样，您可能会遇到序列问题。
But I find more criticalthat the database can have triggers that validate or process information (suppose a trigger that insert records on table B when inserting on table A) - in this case, the sequence of creating the full schema (including triggers) and then inserting the data will create a different set of results.
但我发现更重要的是，数据库可以具有验证或处理信息的触发器（假设一个触发器在插入表 A 时在表 B 上插入记录）——在这种情况下，创建完整模式（包括触发器）的顺序，然后插入数据将创建一组不同的结果。
