问题描述
我需要恢复转储的数据库,但又不丢弃表中的现有行.
I need to restore a dumped database, but without discarding existing rows in tables.
要转储,我使用:
mysqldump -u root --password --databases mydatabase > C:\mydatabase.sql
要恢复,我不不使用mysql命令,因为它会丢弃所有现有的行,但是,显然,mysqlimport应该可以解决问题.但是如何?正在运行:
To restore I do not use the mysql command, since it will discard all existing rows, but instead mysqlimport should do the trick, obviously. But how? Running:
mysqlimport -u root -p mydatabase c:\mydatabase.sql
说表mydatabase.mydatabase不存在".为什么要查找表?如何在不丢弃现有表中现有行的情况下恢复整个数据库的转储?如果mysqlimport想要的话,我可以转储单个表.
says "table mydatabase.mydatabase does not exist". Why does it look for tables? How to restore dump with entire database without discarding existing rows in existing tables? I could dump single tables if mysqlimport wants it.
该怎么办?
推荐答案
如果您担心踩踏现有行,则需要mysqldump它,如下所示:
If you are concerned with stomping over existing rows, you need to mysqldump it as follows:
MYSQLDUMP_OPTIONS="--no-create-info --skip-extended-insert"
mysqldump -uroot --ppassword ${MYSQLDUMP_OPTIONS} --databases mydatabase > C:\mydatabase.sql
这将执行以下操作:
- 删除
CREATE TABLE
语句并仅使用INSERT. - 它将一次精确插入一行.这有助于减轻键重复的行
- remove
CREATE TABLE
statements and use only INSERTs. - It will INSERT exactly one row at a time. This helps mitigate rows with duplicate keys
以这种方式执行mysqldump之后,现在您可以像这样导入
With the mysqldump performed in this manner, now you can import like this
mysql -uroot -p --force -Dtargetdb < c:\mydatabase.sql
尝试一下!
警告:使用--skip-extended-insert
进行转储会使mysqldump变得很大,但是至少您可以控制每个重复项一次完成.这也将增加完成mysqldump的重新加载的时间.
WARNING : Dumping with --skip-extended-insert
will make the mysqldump really big, but at least you can control each duplicate done one by one. This will also increase the length of time the reload of the mysqldump is done.
这篇关于使用转储的mysqlimport的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!