我可以从完整的 mysql mysqldump 文件中恢复单个表吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1013852/
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
Can I restore a single table from a full mysql mysqldump file?
提问by Mobius
I have a mysqldump backup of my mysql database consisting of all of our tables which is about 440 megs. I want to restore the contents of just one of the tables form the mysqldump. Is this possible? Theoretically, I could just cut out the section that rebuilds the table I want but I don't even know how to effectively edit a text document that size.
我有我的 mysql 数据库的 mysqldump 备份，其中包含我们所有的表，大约 440 兆。我想从 mysqldump 中恢复其中一个表的内容。这可能吗？从理论上讲，我可以剪掉重建我想要的表格的部分，但我什至不知道如何有效地编辑这样大小的文本文档。
回答by uloBasEI
You can try to use sed in order to extract only the table you want.
您可以尝试使用 sed 来仅提取您想要的表。
Let say the name of your table is mytableand the file mysql.dump is the file containing your huge dump:
假设您的表的名称是mytable，文件 mysql.dump 是包含您的巨大转储的文件：
$ sed -n -e '/CREATE TABLE.*`mytable`/,/CREATE TABLE/p' mysql.dump > mytable.dump
This will copy in the file mytable.dumpwhat is located between CREATE TABLE mytableand the next CREATE TABLEcorresponding to the next table.
这将在文件中复制mytable.dump位于CREATE TABLE mytable与下CREATE TABLE一个表对应的下一个之间的内容。
You can then adjust the file mytable.dumpwhich contains the structure of the table mytable, and the data (a list of INSERT).
然后，您可以调整mytable.dump包含表结构mytable和数据（一个列表INSERT）的文件。
回答by bryn
I used a modified version of uloBasEI's sed command. It includes the preceding DROP command, and reads until mysql is done dumping data to your table (UNLOCK). Worked for me (re)importing wp_usersto a bunch of Wordpress sites.
我使用了 uloBasEI 的 sed 命令的修改版本。它包括前面的 DROP 命令，并读取直到 mysql 完成将数据转储到您的表（解锁）。为我工作（重新）将wp_users导入到一堆 Wordpress 网站。
sed -n -e '/DROP TABLE.*`mytable`/,/UNLOCK TABLES/p' mydump.sql > tabledump.sql
回答by Martijn Kools
This can be done more easily? This is how I did it:
这可以更轻松地完成吗？我是这样做的：
Create a temporary database (e.g. restore):
创建临时数据库（例如恢复）：
mysqladmin -u root -p create restore
mysqladmin -u root -p 创建恢复
Restore the full dump in the temp database:
恢复临时数据库中的完整转储：
mysql -u root -p restore < fulldump.sql
mysql -u root -p restore < fulldump.sql
Dump the table you want to recover:
转储要恢复的表：
mysqldump restore mytable > mytable.sql
mysqldump 恢复 mytable > mytable.sql
Import the table in another database:
在另一个数据库中导入表：
mysql -u root -p database < mytable.sql
mysql -u root -p 数据库< mytable.sql
回答by Brom558
A simple solution would be to simply create a dump of just the table you wish to restore separately. You can use the mysqldump command to do so with the following syntax:
一个简单的解决方案是简单地创建您希望单独恢复的表的转储。您可以使用 mysqldump 命令通过以下语法执行此操作：
mysqldump -u [user] -p[password] [database] [table] > [output_file_name].sql
Then import it as normal, and it will only import the dumped table.
然后照常导入，它只会导入转储的表。
回答by JCCyC
One way or another, any process doing that will have to go through the entire text of the dump and parse it in some way. I'd just grep for
无论如何，任何执行此操作的进程都必须遍历转储的整个文本并以某种方式对其进行解析。我只想为
INSERT INTO `the_table_i_want`
and pipe the output into mysql. Take a look at the first table in the dump before, to make sure you're getting the INSERT's the right way.
并将输出通过管道传输到 mysql。之前查看转储中的第一个表，以确保您以正确的方式获取 INSERT。
Edit: OK, got the formatting right this time.
编辑：好的，这次格式正确。
回答by Maxime Biette
You should try @bryn command but with the ` delimiter otherwise you will also extract the tables having a prefix or a suffix, this is what I usually do:
您应该尝试@bryn 命令，但使用` 分隔符，否则您还将提取具有前缀或后缀的表，这就是我通常所做的：
sed -n -e '/DROP TABLE.*`mytable`/,/UNLOCK TABLES/p' dump.sql > mytable.sql
Also for testing purpose, you may want to change the table name before importing:
同样出于测试目的，您可能需要在导入之前更改表名：
sed -n -e 's/`mytable`/`mytable_restored`/g' mytable.sql > mytable_restored.sql
To import you can then use the mysql command:
要导入，您可以使用 mysql 命令：
mysql -u root -p'password' mydatabase < mytable_restore.sql
回答by y.tk
Backup
$ mysqldump -A | gzip > mysqldump-A.gzRestore single table
$ mysql -e "truncate TABLE_NAME" DB_NAME $ zgrep ^"INSERT INTO \`TABLE_NAME" mysqldump-A.gz | mysql DB_NAME
备份
$ mysqldump -A | gzip > mysqldump-A.gz恢复单表
$ mysql -e "truncate TABLE_NAME" DB_NAME $ zgrep ^"INSERT INTO \`TABLE_NAME" mysqldump-A.gz | mysql DB_NAME
回答by Tim Hoolihan
One possible way to deal with this is to restore to a temporary database, and dump just that table from the temporary database. Then use the new script.
解决此问题的一种可能方法是还原到临时数据库，然后仅从临时数据库转储该表。然后使用新脚本。
回答by user1097790
This tool may be is what you want: tbdba-restore-mysqldump.pl
这个工具可能就是你想要的：tbdba-restore-mysqldump.pl
https://github.com/orczhou/dba-tool/blob/master/tbdba-restore-mysqldump.pl
https://github.com/orczhou/dba-tool/blob/master/tbdba-restore-mysqldump.pl
e.g. Restore a table from database dump file:
例如从数据库转储文件中恢复一个表：
tbdba-restore-mysqldump.pl -t yourtable -s yourdb -f backup.sql
tbdba-restore-mysqldump.pl -t yourtable -s yourdb -f backup.sql
回答by Weston Ganger
sed -n -e '/-- Table structure for table `my_table_name`/,/UNLOCK TABLES/p' database_file.sql > table_file.sql
This is a better solution than some of the others above because not all SQL dumps contain a DROP TABLEstatement. This one will work will all kinds of dumps.
这是比上述其他一些更好的解决方案，因为并非所有 SQL 转储都包含DROP TABLE语句。这个将适用于各种转储。
