How to configure mysqldump to avoid max_allowed_packet error

Question

Honestly it baffles me that with a completely default installation of mysql if I run mysqldump with default parameters it generates a SQL file that can't be imported into another completely default installation of mysql. From what I can gather it's got something to do with the max_allowed_packet setting and/or the net_buffer_length setting. I've read a bunch about this, and tried tweaking it a bunch of ways on both the export and import sides, but it still doesn't work. I keep getting the packet too big error on import. From everything I've read, here's my best guess:

mysqldump --net_buffer_length=50000 myschema > giant_file.sql

Because I read here that mysqldump refers to max_allowed_packet as net_buffer_length because ... uhh ... anyway. Then to import

mysql --max_allowed_packet=999999 myschema < giant_file.sql

But this still doesn't work. How do I export / import the database???

Answer 1

You may have to make the supreme sacrifice for mysqldumps: bypass extended INSERTs.

This makes each row happen in its own INSERT.

It also increase the size of the mysqldump output.

mysqldump --skip-extended-insert --net_buffer_length=50000 myschema > gigantic_file.sql

Give it a Try !!!