How to configure mysqldump to avoid max_allowed_packet error
- by Leopd
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???