MySQL 报 'MySQL server has gone away'
Contents
问题
经常在终端里连接MySQL，它经常报如下问题:
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
原因
MySQL服务器超时并且关闭了这次的连接.即相关的超时参数设置得太小.也有可能是你的packet太大，MySQL掉弃该包.
解决
超时问题
wait_timeout
查看当前的wait_timeout值
mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 30 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 120 |
| lock_wait_timeout | 600 |
| net_read_timeout | 3 |
| net_write_timeout | 6 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 30 |
| wait_timeout | 120 |
+-----------------------------+----------+
12 rows in set (0.01 sec)
从这，可以看到wait_timeout时间太小了（120秒，它表示服务器在关闭一条连接时，它的非活动时间，即在wat_timeout秒内没有活动过，MySQL就会将它关闭。默认是28800秒，即8小时）
设置session级别的wait_timeout
mysql> set wait_timeout = 280000;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 30 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 120 |
| lock_wait_timeout | 600 |
| net_read_timeout | 3 |
| net_write_timeout | 6 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 30 |
| wait_timeout | 280000 |
+-----------------------------+----------+
12 rows in set (0.00 sec)
mysql>
设置全局级别的wait_timeout
mysql> set global wait_timeout = 280000;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 30 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 120 |
| lock_wait_timeout | 600 |
| net_read_timeout | 3 |
| net_write_timeout | 6 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 30 |
| wait_timeout | 280000 |
+-----------------------------+----------+
12 rows in set (0.00 sec)
mysql>
设置永久性的wait_timeout
配置文件`my.conf`里的[mysqld]中，添加或修改
wait_timeout = 280000
设置 packet
max_allowed_packet
查看当前max_allowed_packet
mysql> show global variables like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| max_allowed_packet | 1073741824 |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)
mysql>
它的作用是：包或任何生成的/中间字符串的最大大小。
它的单位是byte，即字节。(1073741824 / 1024 / 1024 = 1GB)。
最小允许值是1024,最大为1073741824
设置
同wait_timeout 设置类似.