Mysql server出现“Mysql server has gone away”的错误的解决方式

一、最常见的原因分析及解决方法 wait_timeout 和 interactive_timeout

在官方的文档中给出了出现“Mysql server has gone away”的错误的分析原因：https://dev.mysql.com/doc/refman/5.7/en/gone-away.html

出现该问题的主要原因是：Mysql server服务器超时，并且关闭了与客户端的连接导致的。在这种情况下，你一般会得到下面的两种错误中的一种（具体取决于你的操作系统）：

|————–错误码———— | ———————————–错误描述——————————-|

• CR_SERVER_GONE_ERROR:The client couldn’t send a question to the server.

• CR_SERVER_LOST:The client didn’t get an error when writing to the server, but it didn’t get a full answer (or any answer) to the question.

默认情况下，如果在8小时没有对mysql进行查询请求的话，服务器就会自动断开连接。可以通过修改全局变量 wait_timeout和interactive_timeout两个变量的值来进行修改。

$ mysql -u root -p

$ mysql>show variables like '%timeout';

$ mysql>set global wait_timeout = 2880000;

$ mysql>set global interactive_timeout = 2880000;

对这两个变量（wait_timeout 和 interactive_timeout）的解释和比较见：http://cenalulu.github.io/mysql/mysql-timeout/

• interactive_timeout：交互式客户端连接的超时时间。

• wait_timeout：是非交互式的超时时间。

• client_interactive：运行mysql server 在交互式超时时间interactive_timeout（而不是wait_timeout）时间之后断开连接。

简单的说 interactive就是交互式的终端，例如在shell里面直接执行mysql，出现形如mysql>的提示符后就是交互式的连接。而mysql -e ‘select 1’ 这样的直接返回结果的方式就是非交互式的连接。

继承关系

1.通过socket连接 timeout会从哪个global timeout继承 A：由下例可见，通过socket登录，timeout 继承于global.interactive_timeout;

mysql> set global interactive_timeout =  11111;
Query OK, 0 rows affected (0.00 sec)

mysql> set global wait_timeout = 22222;
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_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 11111    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 22222    |
+----------------------------+----------+
10 rows in set (0.00 sec)

mysql -uroot -ppassword -S /usr/local/mysql3310/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show session variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 11111    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 11111    |
+----------------------------+----------+
10 rows in set (0.00 sec)

通过TCP/IP client 连接， timeout会从哪个global timeout继承 A：由下例可见，通过TCP/IP client 连接后的wait_timeout 仍然继承于 global.interactive_timeout

mysql -uroot -ppassword -h 127.0.0.1 --port 3310
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show session variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 11111    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 11111    |
+----------------------------+----------+
10 rows in set (0.00 sec)

起效关系

timeout值，对于处于运行状态SQL语句是否起效（即是否等价于执行超时）？ A：由下例可见SQL正在执行状态的等待时间不计入timeout时间。即SQL运行再久也不会因为timeout的配置而中断

mysql> set session wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)

mysql> set session interactive_timeout=10;
Query OK, 0 rows affected (0.00 sec)

mysql> select 1,sleep(20) from dual;
+---+-----------+
| 1 | sleep(20) |
+---+-----------+
| 1 |         0 |
+---+-----------+
1 row in set (20.00 sec)

mysql> 
mysql> show session variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 10       |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 10       |
+----------------------------+----------+

同一个session中，wait_timeout 和 interacitve_timeout是否都会生效。 A：只有wait_timeout 会真正起到超时限制的作用

mysql> set session interactive_timeout=10;
Query OK, 0 rows affected (0.00 sec)

mysql> set session wait_timeout=20;
Query OK, 0 rows affected (0.00 sec)

mysql> show full processlist;
+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
| Id | User        | Host            | db   | Command | Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |
+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
|  1 | system user |                 | NULL | Connect | 103749 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL                  |         0 |             0 |         1 |
|  2 | system user |                 | NULL | Connect | 103750 | Connecting to master                                                        | NULL                  |         0 |             0 |         1 |
|  3 | root        | localhost       | NULL | Query   |      0 | NULL                                                                        | show full processlist |         0 |             0 |        11 |
| 10 | root        | localhost:58946 | NULL | Sleep   |     20 |                                                                             | NULL                  |         0 |             0 |        11 |
+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
4 rows in set (0.00 sec)

mysql> show full processlist;
+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
| Id | User        | Host      | db   | Command | Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |
+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
|  1 | system user |           | NULL | Connect | 103749 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL                  |         0 |             0 |         1 |
|  2 | system user |           | NULL | Connect | 103750 | Connecting to master                                                        | NULL                  |         0 |             0 |         1 |
|  3 | root        | localhost | NULL | Query   |      0 | NULL                                                                        | show full processlist |         0 |             0 |        11 |
+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
3 rows in set (0.00 sec)

global timeout和session timeout是否都会作为超时判断依据？ A：只有session级别 timeout 会起作用。即一个session开始后，无论如何修改global级别的timeout都不会影响该session

mysql> set session interactive_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> set session wait_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| interactive_timeout        | 10       |
| wait_timeout               | 10       |
+----------------------------+----------+
10 rows in set (0.00 sec)

mysql> show global variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| interactive_timeout        | 20       |
| wait_timeout               | 20       |
+----------------------------+----------+
10 rows in set (0.00 sec)


mysql> show full processlist;
+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
| Id | User        | Host            | db   | Command | Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |
+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
|  3 | root        | localhost       | NULL | Query   |      0 | NULL                                                                        | show full processlist |         0 |             0 |        11 |
| 17 | root        | localhost:60585 | NULL | Sleep   |     10 |                                                                             | NULL                  |        10 |            10 |        11 |
+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
2 rows in set (0.00 sec)

mysql> show full processlist;
+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
| Id | User        | Host      | db   | Command | Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |
+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
|  3 | root        | localhost | NULL | Query   |      0 | NULL                                                                        | show full processlist |         0 |             0 |        11 |
+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
1 rows in set (0.00 sec)

测试2

mysql> show session variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| interactive_timeout        | 20       |
| wait_timeout               | 20       |
+----------------------------+----------+
10 rows in set (0.00 sec)

mysql> show global variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| interactive_timeout        | 10       |
| wait_timeout               | 10       |
+----------------------------+----------+
10 rows in set (0.00 sec)



mysql> show full processlist;
+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
| Id | User        | Host            | db   | Command | Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |
+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
|  3 | root        | localhost       | NULL | Query   |      0 | NULL                                                                        | show full processlist |         0 |             0 |        11 |
| 19 | root        | localhost:50276 | NULL | Sleep   |     19 |                                                                             | NULL                  |        10 |            10 |        11 |
+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
2 rows in set (0.00 sec)

mysql> show full processlist;
+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
| Id | User        | Host      | db   | Command | Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |
+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
|  3 | root        | localhost | NULL | Query   |      0 | NULL                                                                        | show full processlist |         0 |             0 |        11 |
+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
1 rows in set (0.00 sec)

二、还有一种就是 你发送给服务器的查询是错误的 或者太大了

这种情况下，需要修改 变量max_allowed_packet的大小

$ mysql> set global max_allowed_packet = 64M;

三、其他问题见官方文档

Reference:解决方式参考

修改过程中遇到的其他问题

没有通过mysql command line来修改变量的时候 是通过在/etc/mysql/my.cnf中添加变量来修改的，

max_allowed_packet = 64M
wait_timeout = 2880000
interactive_timeout = 2880000

这种方式。但是修改完之后重启mysql service的时候 就出问题了，报错：“Job for mysql.service failed because the control process exited with error code , see systemctl status mysql.service and journalctl -xe for details”（这里是因为语法出错了，要在变量设置前 加上[mysqld]标志）

于是注释掉了 上面的变量修改，但是还是不起作用

后来通过Stackoverflow查到了，能够解决问题的答案：

1.Check the permission of mysql data dir using below command

# ls -ld /var/lib/mysql/

2.Check the permission of databases inside mysql data dir using below command

# ls -lh /var/lib/mysql/

3.Check the listening network tcp ports using below command

# netstat -ntlp

4.Check the mysql log files for any error using below command.

# cat /var/log/mysql/mysqld.log

5.Try to start mysql using below command

# mysqld_safe --defaults-file=/etc/my.cf

我的问题在执行完最后一条之后 就解决了，但是安装的PHPMyadmin不能连接上了 ，所以又通过下面的命令：

$ sudo service mysql restart 

重启了下 就好了。

附加内容：在使用Mysql server的时候出现 “Mysql server has gone away”的时候 ，大概有两个方面的原因：

• 一是 查询太大 太长

• 二是 连接超时自动断开（这个是最常见的错误）

这两个修改方式都是 设置[mysqld]中的变量:

[mysqld]
max_allowed_packet = 64M
wait_timeout = 2880000
interactive_timeout = 2880000

当时在 /etc/mysql/my.cnf中修改的时候 没有加上 [mysqld]这个标志 导致语法错误 而不能启动mysql

还有网上别人遇到的错误是 修改了变量之后没有生效。

这个方面的原因大概就是：

my.cnf中 还有引用了两个地方的设置：

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

这两个文件里面的mysql.conf配置也会被引进来 ，导致my.cnf中的配置被这两个文件中的配置覆盖。

所以尽量到这两个文件中，去修改需要修改的变量。