实行敕令
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网别的相干文章!