Error importing large MySQL dump file which includes binary BLOBs in Windows

Solution 1:

For this reason I always use mysqldump --hex-blob.

Re-dump the database encoding the blobs using this switch and it will work.

You can try to import it using a windows mysql client IDE like sqlyog or mysql administrator. It worked for me once.

Solution 2:

You do not necessarily need to use the --hex-blob option. I have just resolved this problem my self and the issue was that I needed the --max_allowed_packet to be set to a large enough value to accommodate the largest data blob I would be loading. Your restore command should look something like:

mysql -u user -h hostname --max_allowed_packet=32M dbname < dumpfile.sql

If you use the --hex-blob option you will significantly increase the size of your backup - by a factor of 2 or more. NOTE: to restore the same data that I restored with the above command required setting the --max_allowed_packet=64M in my.ini(cnf) and restarting the server AS WELL AS setting it to 64M on the command line to restore a dump created with the --hex-blob option.

---

Solution 3:

There could still be a problem because of large file size so make sure you set max-allowed-packet to some high value (param for mysql command).