On many occasions we need to install in our local environment a copy of production sites for development or debugging process, this entails that need to import large databases.

The most common error is that you cannot import the database because for some reason you lose the communication with the MySQL server, as shown below.

$ mysql -f -uroot -proot mydb -h 127.0.0.1 \
< ~/Downloads/prod-mysql-2013-01-29.sql

> ERROR 2006 (HY000) at line 6565: MySQL server has gone away

To fix this error you must change the default configuration of MySQL.

MySQL store his configuration in a file commonly named my.cnf and could be located in different locations depending on the OS version you have installed.

If you do not know the location of this file, you can use the following command shown below to determine where you expect to find locations MySQL settings.

$ mysql --help | grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /Applications/MAMP/conf/my.cnf ~/.my.cnf

In my case I had MySQL as part of MAMP and this file is not created by default. For this reason, I will create a new file located at /Applications/MAMP/conf/my.cnf because is one of the locations where MySQL will look his configuration file.

I recommend using the sample provided by MySQL documentation for large databases located at /usr/share/doc/mysql-server-5.0/examples/my-large.cnf.gz.

After copy the suggested configuration from the documentation, I have to apply two changes to warranty the import process don’t fail.

• Remove Directive # skip-locking
• Increase setting max_allowed_packet = 100M

Then just be enough restart the MySQL server and try to import the database again.

You can check my version of my.cnf here