I use mysqldump to create a flat file for backup purposes. I have used this file to recreate the database on an alternate server. I ran the import process through ssh on the command line and I received multiple Packet too Large
errors.
I restarted mysql with a much larger max_allowed_packet (i.e.- 1000M) and still received the error. I even attempted setting the max_allowed_packet in the import file, still received the error.
Is there a way to ensure the max_allowed_packet is set and/or use mysqldump that will create a file that does not cause this problem?
For reference:
the uncompressed mysqldump file is ~2GB
database type is INNODB
Best Answer
The first I thought about was what max_allowed_packet actually controls. Here is what I found:
According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining it:
Given that definition of max_allowed_packet, I then discovered something else from ServerFault: innodb_log_file_size and innodb_log_buffer_size combined must be larger than ten times your biggest blob object if you have a lot of large ones
Keeping these two things in mind, I would increase innodb_log_file_size in /etc/my.cnf to the max size allowed for it, 2047M. This of course requires the following
This will accommodate any big blobs you may have in your data.