How to configure mysqldump to avoid max_allowed_packet error

Posted by Leopd on Server Fault See other posts from Server Fault or by Leopd
Published on 2011-11-11T17:59:20Z Indexed on 2011/11/14 17:54 UTC
Read the original article Hit count: 299

Filed under:
|

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

© Server Fault or respective owner

Related posts about mysql

Related posts about mysqldump