mysql恢复报ERROR 2006 (HY000) at line 5303856: MySQL server has gone away错误处理

一、问题描述
mariadb 10.1.16
centos 6.6
在恢复binlog时，报错：
[apps@lvxl087 backup]$ time /apps/svr/mariadb10/bin/mysqlbinlog mysql-bin.000096 --start-datetime='2017-03-31 01:01:41'|/apps/svr/mariadb10/bin/mysql -u root -p --

socket=/tmp/mysql3306.sock
Enter password:
ERROR 2006 (HY000) at line 5303856: MySQL server has gone away
real 3m43.697s
user 0m21.772s
sys 0m12.850s

二、问题分析
查看相关参数如下：
MariaDB [(none)]> show global variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| deadlock_timeout_long | 50000000 |
| deadlock_timeout_short | 10000 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 45 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 600 |
| net_read_timeout | 3 |
| net_write_timeout | 6 |
| slave_net_timeout | 30 |
| thread_pool_idle_timeout | 60 |
| wait_timeout | 28800 |
+-----------------------------+----------+
14 rows in set (0.00 sec)

其中lock_wait_timeout为300秒，wait_timeout为28800秒，参数设置不小，再查看 max_allowed_packet参数大小为67M，大小还好。

通过业务表查询恢复到的时间点为2017-03-31 15:32:51,如下：

MariaDB [midea_gls]> select modify_time from gtyr order by modify_time desc limit 1,5;
+---------------------+
| modify_time |
+---------------------+
| 2017-03-31 15:32:51 |
| 2017-03-31 15:32:51 |
| 2017-03-31 15:32:51 |
| 2017-03-31 15:32:51 |
| 2017-03-31 15:32:51 |
+---------------------+
5 rows in set (0.64 sec)
而mysql-bin.000096 binlog文件最后生成时间为15:32，时间点上刚好一致。

[apps@lvxl087 backup]$ ls -lt
total 1112044
drwxr-xr-x 8 apps apps 4096 Apr 4 18:38 xtrabackup_20170331010001
-rw-rw---- 1 apps apps 260286601 Apr 4 18:35 mysql-bin.000097
-rw-rw---- 1 apps apps 676032158 Mar 31 15:32 mysql-bin.000096
drwxr-xr-x 8 apps apps 4096 Dec 8 23:14 xtrabackup_20161207010001
-rw-rw---- 1 apps apps 202401487 Dec 8 23:11 mysql-bin.000064

这个报错ERROR 2006 (HY000) at line 5303856: MySQL server has gone away,应该是恢复没有明确指定一个结束位置，引起恢复未正常退出的报错，但上面这个binlog文件实际上恢复完成。

三、问题解决
可忽略报错，继续做下一步操作。
[apps@lvxl087 backup]$ time /apps/svr/mariadb10/bin/mysqlbinlog mysql-bin.000097 --stop-datetime='2017-03-31 23:59:59'|/apps/svr/mariadb10/bin/mysql -u root -p --

socket=/tmp/mysql3306.sock
Enter password:

real 0m50.866s
user 0m7.726s
sys 0m3.768s
这里恢复没有再报错。
查询业务表的最新时间：
MariaDB [gls]> select modify_time from gtyr order by modify_time desc limit 1,5;
+---------------------+
| modify_time |
+---------------------+
| 2017-04-01 00:04:01 |
| 2017-04-01 00:04:01 |
| 2017-04-01 00:04:01 |
| 2017-04-01 00:01:33 |
| 2017-03-31 23:52:01 |
+---------------------+
5 rows in set (0.11 sec)

更多相关文章

随机推荐