mysql导入大批量数据出现MySQL server has gone away的解决方法

向右看齐 2022-04-03 05:10 214阅读 0赞

因工作需要，需要导入一个200M左右的sql到user库

执行命令

mysql> use user  
Database changed  
mysql> source /tmp/user.sql  
导入的过程中出现MySQL server has gone away错误，数据导入失败。

错误信息如下：

ERROR 2006 (HY000): MySQL server has gone away  
ERROR 2006 (HY000): MySQL server has gone away  
No connection. Trying to reconnect...  
Connection id:    11  
Current database: user

ERROR 2006 (HY000): MySQL server has gone away  
No connection. Trying to reconnect...  
Connection id:    12  
Current database: user

ERROR 2006 (HY000): MySQL server has gone away  
ERROR 2006 (HY000): MySQL server has gone away  
No connection. Trying to reconnect...  
Connection id:    13  
Current database: user  
  
开始以为是超时导致，因此调大了 connect\_timeout 和 wait\_timeout 的值。

重新执行后问题依旧。

解决方法：

查看资料，发现了 max\_allowed\_packet 参数，   
官方解释是适当增大 max\_allowed\_packet 参数可以使client端到server端传递大数据时，系统能够分配更多的扩展内存来处理。

查看mysql max\_allowed\_packet的值

mysql> set global max\_allowed\_packet=268435456;  
Query OK, 0 rows affected (0.00 sec)