Reference: solution to MySQL server has gone away error – time blog
When we use Mysql to import large file SQL, we may report the MySQL server has gone away error. The problem is the default value setting of max_allowed_packet configuration is too small. You only need to increase the value of this item and import it again to succeed. This item is used to limit the size of the package received by the MySQL server. Therefore, if the imported file is too large, it may exceed the value set in this item, resulting in unsuccessful import! Let’s take a look at how to view and set the value of this item.
View the value of Max_allowed_packet
show global variables like 'max_allowed_packet'; +--------------------+---------+ | Variable_name | Value | +--------------------+---------+ | max_allowed_packet | 4194304 | +--------------------+---------+
You can see that the size of this item is only 4m by default. Next, set the value to 150m (1024 * 1024 * 150)
set global max_allowed_packet=157286400;
View the size again
show global variables like 'max_allowed_packet';
By increasing this value, generally speaking, importing SQL with a large amount of data should be successful again. If an error is still reported, continue to increase it. Please note that setting in the command line is only valid for the current time. After restarting the MySQL service, restore the default value, but you can modify the configuration file (you can add max_allowed_packet = 150m in the configuration file my.cnf) To achieve the purpose of permanent validity, but in fact, we do not often import such a large amount of data, so I think the current configuration can take effect through the command, and there is no need to modify the configuration file.