旗下导航:搜·么
当前位置:网站首页 > MySQL教程 > 正文

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

作者:搜教程发布时间:2019-12-01分类:MySQL教程浏览:78评论:0


导读:因工作须要,须要导入一个200M摆布的sql到user库实行敕令mysql>useuserDatabasechangedmysql>sour...
因工作须要,须要导入一个 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_timeoutwait_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导入


欢迎 发表评论: