In linux, the MYSQL database is too large to set the max_allowed_packet size.

In the evening, when the website is busy again, a problem occurs when a large database is recovered. In Linux, backup data is directly backed up using commands, which is more complete than importing data directly from PHPMYADMIN, the same problem occurs when data is restored. The following ERROR message is displayed: "ERROR 2006 (HY000) at Line 179: MySQL server has gone away.

Captain tells us the solution because the default value of max_allowed_packet in Linux is too small. The database table is large and the default value is 1 MB. Therefore, you need to adjust the parameters.

Here, I am using Linux. In The etc/my. cnf file, find max_allowed_packet settings. The default value is 1 MB. We can change it to 10 MB or 20 MB. Then, restart the website environment and run the import database command to import the database.

View current configuration

Show VARIABLES like '% max_allowed_packet % ';

The result is as follows:

+ -------------------- + --------- +

| Variable_name | Value |

+ -------------------- + --------- +

| Max_allowed_packet| 1048576 |

+ -------------------- + --------- +

The preceding description shows the current configuration: 1 M.

 

Modification method

1. Modify the configuration file

You can edit my. cnf (my. ini in windows) and modify it in the [mysqld] section or mysql server configuration section.

Max_allowed_packet = 20 M
If you cannot find my. cnf, you can use

Mysql -- help | grep my. cnf
Find the my. cnf file.

In linux, the file is in/etc.

2. Modify it in the mysql command line

Run in mysql command line

Set global max_allowed_packet = 2*1024*1024*10
Then exit the command line, restart the mysql service, and then enter.

Show VARIABLES like '% max_allowed_packet % ';
Check whether max_allowed_packet is successfully edited.

PS: In Windows, find the MYSQL installation directory my. ini and add set-variable = max_allowed_packet = 20 M in the [mysqld] script ). Then restart MYSQL to take effect.