【转载】mysql导入大批量数据出现MySQL server has gone away的解决方法
因工作需要，需要导入一个200M左右的sql到user库
执行命令
mysql> use user
Database changed
mysql> source /tmp/user.sql
- 1
- 2
- 3
- 1
- 2
- 3
导入的过程中出现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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
开始以为是超时导致，因此调大了 connect_timeout 和 wait_timeout 的值。
重新执行后问题依旧。
解决方法：
查看资料，发现了 max_allowed_packet
参数，
官方解释是适当增大 max_allowed_packet
参数可以使client端到server端传递大数据时，系统能够分配更多的扩展内存来处理。
查看mysql max_allowed_packet的值
mysql> show global variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
- 1
- 2
- 3
- 4
- 5
- 6
- 1
- 2
- 3
- 4
- 5
- 6
可以看到是4M，然后调大为256M(1024*1024*256)
mysql> set global max_allowed_packet=268435456;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'max_allowed_packet';
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| max_allowed_packet | 268435456 |
+--------------------+-----------+
1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
修改后执行导入，一切正常，解决问题。
注意：
使用set global命令修改 max_allowed_packet 的值，重启mysql后会失效，还原为默认值。
如果想重启后不还原，可以打开 my.cnf 文件，添加 max_allowed_packet = 256M 即可。
转载地址：http://blog.csdn.net/fdipzone/article/details/51974165
