Solve the problem of ERROR 2006 (HY000) at line XX: MySQL server has gone away

Today, an ERROR message "ERROR 2006 (HY000) at line 534: MySQL server has gone away" is prompted when you import a database to a customer's website to migrate a VPS host, generally, this problem occurs when the database is too large and the configuration file may limit the maximum database file. The solution is recorded by mistake so that you can solve the problem directly when you use it next time.

First, find the my. cnf file.

Find the my. cnf file on the current server based on the WEB environment configured on different servers, and then edit and modify the configuration parameters.

2. Modify the configuration parameters of max_allowed_packet

Modify the max_allowed_packet parameter in mysqld and mysqldump to increase the value later.

Third, add wait_timeout

We need to add the wait_timeout = 6000 parameter in MYSQLD in the screenshot above.

My personal opinion on wait_timeout is added:

Wait_timeout has many drawbacks. It is reflected that a large number of SLEEP processes in MySQL cannot be released in time, dragging down system performance, but it cannot be set too small, otherwise, you may encounter problems such as "MySQL has gone away". Generally, it is a good choice to set wait_timeout to 10, but in some cases, there may also be problems. For example, if there is a CRON script, and the interval between two SQL queries is greater than 10 seconds, then this setting will be problematic (of course, this is not an unsolved problem. You can ping mysql_ping from time to time in the program so that the server knows that you are still alive and recalculates the wait_timeout time ):

# Vi/etc/my. cnf

[Mysqld]
Wait_timeout = 10

#/Etc/init. d/mysql restart

However, this method is too stiff, so we should try to avoid restarting the online service no matter what, to see how to SET it in the MySQL command line:

Mysql> set global wait_timeout = 10;

Mysql> show global variables like 'Wait _ timeout ';
+ ---------------------------- + ------- +
| Variable_name | Value |
+ ---------------------------- + ------- +
| Wait_timeout | 10 |
+ ---------------------------- + ------- +

This is easy to confuse. If show variables is used for query, the settings do not seem to take effect, this is because simply using show variables is equivalent to using show session variables. session variables are queried. The global variables are queried only when show global variables is used.

Many people complain on the network that they haven't found any changes after setting global Using show variables queries. The reason is that they have obfuscated session variables and global variables. If you only want to modify session variables, you can use a syntax like set wait_timeout = 10; or set session wait_timeout = 10.

4. Save and replace and restart MYSQL

After the file is compiled, we save the file, restart the current WEB server database, and then there is no error prompt when importing the database.