[MySQL案例]之恢复进程莫名被杀

今天上班就发现一起数据库例行恢复作业失败，失败提示为：“数据库恢复失败”，也就是说是在执行mysql < dumpfile的时候失败了。

1
2
[ root@localhost ]#/usr/local/mysql56/bin/mysql -S ./mysql.sock bi_monitor < /home/mysql/backup/2014-09-21_bi_monitor_3346.sql 
ERROR 2006 (HY000) at line 294: MySQL server has gone away

MySQL server has gone away是指客户端与MySQL服务端之间的连接段开，一般来说原因有这么几个：

  1. MySQL crash：MySQL Server宕机
  2. connection timeout：客户端连接超时
  3. kill connection：连接进程被杀，与connection timeout差不多，区别在于一个是MySQL Server主动，一个是被动
  4. max_allowed_packet too small：返回结果集大于max_allowed_packet限制
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> show global status like 'uptime';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| Uptime        | 230493834  | 
+---------------+------------+
mysql> show global variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| interactive_timeout         | 28800    | 
| wait_timeout                | 28800    | 
+-----------------------------+----------+
mysql> show global status like 'com_kill';         
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_kill      | 0     | 
+---------------+-------+

由此看来原因1、2、3都不是造成这次失败的原因了，那应该就是原因4了。

1
2
3
4
5
6
7
8
9
10
mysql> show global variables like 'max_allowed_packet';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_allowed_packet | 1024  | 
+--------------------+-------+
1 row in set (0.00 sec)
mysql> set global max_allowed_packet=1024*1024*1024;
Query OK, 0 rows affected (0.00 sec)

再导入测试，果然顺利结束不会报错了。再深入验证下果然这个备份文件的value超大。