Eduardo Garcia bio photo


Eduardo Garcia

Knowmad by definition

Location: Australia

Twitter Facebook  QQ交谈 Google+ Github LinkedIn Feed

On many occasions we need to install in our local machine production sites for development or just for faster debugging, this entails that we need to import very large databases.

The most common mistake is that you can not import the database, returning an error that communication with the MySQL server has been lost, as shown below.

$ /mysql -f -uroot -proot mydb -h < ~/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, if you do not know the location of this file can use the command shown below to determine at which locations MySQL expects to find the configuration.

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

In my case I have MySQL provided by MAMP, so I need create a file in the location /Applications/MAMP/conf/my.cnf, based on a configuration for large databases which is available in the documentation MySQL and should be available in the path /usr/share/doc/mysql-server-5.0/examples/my-large.cnf.gz.

I will apply two changes to enable big importations.

  • Remover Directive # skip-locking
  • Increase max_allowed_packet = 100M

Then you just need restart the MySQL server and try to import the database again.

You can find my version of my.cnf here.


comments powered by Disqus