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.
The restore now completes with no errors but one of the three tables which contains blobs is never restored.
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!
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.
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