Mysqldump - 如何从此文件恢复单个表？

I need to restore just a single table in my database.

我需要在我的数据库中只恢复一个表。

I have a .sql file that has all the info I need for one table but the rest would overwrite important information for other tables.

我有一个.sql文件,其中包含一个表所需的所有信息,但其余的将覆盖其他表的重要信息。

Instead of using the solution here - using a tool I've never heard of, I figured it would be more sure fire to do it manually.

而不是在这里使用解决方案 - 使用我从未听说过的工具,我认为手动执行它会更加确定。

Unfortunately, the MySqlDump generated a GIANT insert line too long to paste into mysql command line...

不幸的是,MySqlDump生成的GIANT插入行太长,无法粘贴到mysql命令行中......

What should I do?

我该怎么办?

Should I use sed like the link above describes?

我应该像上面的链接一样使用sed吗?

Or could I copy paste the commands for that specific table from the mysqldump.sql into a new .sql file then call:

或者我可以将mysqldump.sql中特定表的命令粘贴到新的.sql文件中,然后调用:

mysql -u root -p -h localhost < copyPasteFile.sql

3 个解决方案

#1

u can try it

你可以尝试一下

mysql -u root -p databasename -h localhost < copyPasteFile.sql

#2

mysql -uuser -ppassword -e "create database temporary"

mysql -uuser -ppassword temporary < copyPasteFile.sql

mysqldump -uuser -ppassword temporary yourtable > onlythattable.sql

mysql -uuser -ppassword therealdb < onlythattable.sql

mysql -uuser -ppassword -e "drop database temporary"

make sure that copyPasteFile.sql does not have a "use somedatabase;" if it was exported with phpmyadmin, it probably has that, if it was exported with mysqldump it wont.

确保copyPasteFile.sql没有“使用somedatabase;”如果它是用phpmyadmin导出的,它可能有,如果它是用mysqldump导出它不会。

#3

I am not sure if its the best way but I just spin up a new schema and restore the backup in to it, Dump the data I need and import that in to the production database.

我不确定它是否是最佳方式,但我只是启动一个新架构并将备份恢复到它,转储我需要的数据并将其导入生产数据库。

I use the MYSQL work bench which makes it easier but the below steps can probably be reproduced at the command line

我使用MYSQL工作台使其更容易,但可以在命令行中重现以下步骤

Create new MYSQL Schema

创建新的MYSQL架构

Import self-contained backup

导入自包含备份

Set "Target Schema" to the new empty database (.sql Dump must not contain schema creation code)

将“Target Schema”设置为新的空数据库(.sql Dump不能包含模式创建代码)

Restore backup
Dump table to csv and import it in to the production live database

转储表到csv并将其导入生产实时数据库

Delete Restore Schema

删除还原架构

To restore to a schema at the command line it looks like you use:

要在命令行还原到架构,它看起来像您使用:

mysql -u <user name> -p <password> <database name> < sqlfilename.sql

mysql -u <用户名> -p <密码> <数据库名称>

#1