当前位置: 动力学知识库 > 问答 > 编程问答 >

backup - How to reliably restore MySQL blobs

问题描述:

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...

I then wanted to restore one of the backups to a different named database so I did:

mysql myNewDatabaseName -u root < myBackupFile.sql

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

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

My max-allowed-packet is set to 32M

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.

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.

The environment is windows server 2003 sp2

Any help appreciated!

George

网友答案:

I managed to get the blobs to backup /restore by using the following mysqldump command:

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

Not sure if its specifting max_allowed_packet on the command line or the skip-extended-insert that did the trick.

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 dont understand why I got no errors on either the dump or the restore.

G.

网友答案:

mysqldump --skip-extended-insert works but can reduce performance by 100x on restore, making it not a viable choice.

When you do the backup, max_allowed_packet is ignored by mysqldump (by design?) The actual complement is net_buffer_length. So make sure your max_allowed_packet is bigger than your net_buffer_length and it should work. As in:

mysqldump -u root --net_buffer_length=100k oldDB > backup.sql
mysql -u root --max_allowed_packet=10M newDB < backup.sql
分享给朋友:
您可能感兴趣的文章:
随机阅读: