The max_allowed_packet error when restoring a MySQL database

If you’re reading this note, you’ve run into the following error while restoring a MySQL database:

Got a packet bigger than 'max_allowed_packet'

There are a few ways to beat it. The simplest is to pass the max_allowed_packet size as an argument to mysql:

mysql -uroot -p -max_allowed_packet=100M **database** < **dump**.sql

If that doesn’t work, connect to the console:

mysql -uroot

Update the global max_allowed_packet value:

set global max_allowed_packet=1000000000;

This value is only active for your current mysql console session. As soon as you disconnect from mysql it resets to the default. So you need to restore the database within that same session:

use database  
source dump.sql

To make the setting stick, update the mysql config (/etc/my.cnf). Add this to the [server] section:

max_allowed_packet=100M