mysql导入大批量数据涌现MySQL server has gone away的解决方法【MySQL教程】,mysql,导入

因工作须要，须要导入一个 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 awayERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    11Current database: userERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    12Current database: userERROR 2006 (HY000): MySQL server has gone awayERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    13Current database: 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)

修正后实行导入，一切正常，解决题目。
注重：
运用set global敕令修正 max_allowed_packet 的值，重启mysql后会失效，复原为默认值。

假如想重启后不复原，能够翻开 my.cnf 文件，增加 max_allowed_packet = 256M 即可。

本篇讲解了mysql导入大批量数据涌现MySQL server has gone away的解决要领，更多相干内容请关注ki4网。

相干引荐：

mysql 征采四周N千米内数据的实例

mysql 衔接闪断自动重连的要领

php 完成HTML实体编号与非ASCII字符串互相转换类

以上就是mysql导入大批量数据涌现MySQL server has gone away的解决要领的细致内容，更多请关注ki4网别的相干文章！

标签：mysql导入