如何可靠地还原MySQL Blob [英] How to reliably restore MySQL blobs

问题描述

多年来，我一直使用以下命令备份MySQL数据库: mysqldump myDatabaseName -u root > myBackupFile.sql

I have been backing up a MySQL database for several years with the command: mysqldump myDatabaseName -u root > myBackupFile.sql

备份似乎可以正常工作...

The backups have appeared to work fine...

然后我想将其中一个备份还原到另一个命名数据库，所以我这样做了: mysql myNewDatabaseName -u root < myBackupFile.sql

I then wanted to restore one of the backups to a different named database so I did: mysql myNewDatabaseName -u root < myBackupFile.sql

我遇到了有关日志文件大小的一些错误，因此我停止了Mysql并删除了日志文件，并在my.ini文件中设置了以下参数，然后重新启动mysql.

I got some errors about logfile size so I stopped Mysql and removed the logfiles and set the following parameters in the my.ini file and restarted mysql.

innodb_log_file_size=64M

innodb_log_buffer_size=8M

还原现在已完成，没有任何错误，但是包含blob的三个表之一从未还原.

The restore now completes with no errors but one of the three tables which contains blobs is never restored.

我的max-allowed-packet设置为32M

数据库备份大小约为2.2 GB，该大小的大部分位于无法还原的表中.如果我在还原的数据库上运行mysqldump，则大小为185 MB.

The database backup size is about 2.2 GB the majority of that size being in the table that does not restore. If I run a mysqldump on the restored database the size is 185 MB.

我现在已经尝试使用选项--hex-blob进行mysqldump了，但是我还没有尝试还原该文件(3.9 GB).

I have now tried doing a mysqldump with the option --hex-blob but I have not tried to restore that file (3.9 GB) yet.

由于我现有的备份似乎一文不值，因此我确实需要一种防弹备份和还原的方法.我特别担心，据我所知，它无提示地失败"，没有错误日志条目.

I really need to have a bombproof way to backup and restore as my existing backups appear worthless. I am particularly concerned that it "fails silently" with no error log entries as far as I can see.

环境是Windows Server 2003 sp2

The environment is windows server 2003 sp2

任何帮助表示赞赏！

乔治

推荐答案

我设法通过使用以下mysqldump命令来备份和还原blob:

I managed to back up and restore the blobs by using the following mysqldump command:

mysqldump --opt  --skip-extended-insert --max_allowed_packet=128M -u root myDB > filename

不确定是在命令行中指定max_allowed_packet还是完成欺骗的skip-extended-insert.

Not sure if it’s specifying max_allowed_packet on the command line or the skip-extended-insert that did the trick.

我假设正在使用32M的max_allowed_packet，但是我认为在mysql配置文件中，它位于[mysqld]部分，因此可能不适用于转储.

I assumed that my max_allowed_packet of 32M was being used, but I think that in the mysql config file it is in the [mysqld] section and so probably does not apply to dump.

我仍然不明白为什么我的转储或还原都没有错误.

I still don’t understand why I got no errors on either the dump or the restore.

这篇关于如何可靠地还原MySQL Blob的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！