Knowledgebase

Restoring a large MySQL database

When we restore a large database, usually we come across two errors as shown below:

“MySQL server has gone away” & “Lost connection to MySQL server during query”

These errors means that MySQL server (mysqld) timed out and closed the connection if nothing happened after the specified time duration.

Here are some examples of the errors:

• General error: 2006 MySQL server has gone away
• Error Code: 2013. Lost connection to MySQL server during query
• Warning: Error while sending QUERY packet
• PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

To resolve the issue, please follow the steps mentioned below:

There are multiple parameters which need to be tuned to restore a large database.

• Connect-timeout
• Max-allowed-packet
• net-buffer-length
• wait_timeout
• interactive_timeout
• net_write_timeout
• net_read_timeout

Here, we consider restoring a 16GB database:

When restoring huge database, we need to set the values as specified below:

• connect_timeout = 3600
• max_allowed_packet = 1024M
• net_buffer_length = 1000000
• wait_timeout = 86400 (which means 24 hours)
• interactive_timeout = 86400 (which means 24 hours)
• net_write_timeout = 3600
• net_read_timeout = 3600

By using the above values, with the commands mentioned below, you will able to restore the database without any issues.

Also, the restoration time will depend on the Processor & RAM of your server.

Use the command shown below to restore the database from command line.

Restoring a large database will require longer time, so use screen to run the command in case SSH connection gets disconnected:

• Create a screen session:

# screen -S screen_name

• Now, run the command mentioned below:

mysql -u root -p –max_allowed_packet=1024M –connect_timeout=3600 –net_buffer_length=1000000 –wait_timeout=86400 –interactive_timeout=86400 —net_write_timeout=3600 –net_read_timeout=3600 database-name < database.sql

Alternatively, you can use MySQL workbench to restore a huge database.