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.

RackNerd customers are welcome to contact our Technical Support department for assistance on restoring MySQL databases. Our support team is available 24×7 to help!

If you have any questions, or need web hosting services for your MySQL application, please feel free to contact the hosting experts at RackNerd! Our server hosting solutions can be found below.

Server Hosting Solutions by RackNerd:

Shared Hosting
cPanel Web Hosting in US, Europe, and Asia datacenters
Logo
Reseller Hosting
Create your new income stream today with a reseller account
Logo
VPS (Virtual Private Server)
Fast and Affordable VPS services - Instantly Deployed
Logo
Dedicated Servers
Bare-metal servers, ideal for the performance-demanding use case.
Logo

Leave a comment

Your email address will not be published.