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

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

执行命令

mysql> use user
Data 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 data : user

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    12
Current data : 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 data : user

开始以为是超时导致，因此调大了 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 |
+--------------------+---------+

可以看到是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)


修改后执行导入，一切正常，解决问题。