一、问题描述 mariadb 10.1.16 centos 6.6 在恢复binlog时，报错： [apps@abc 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，大小还好。
MariaDB [(none)]> show variables like '%packet%'; +--------------------------+------------+ | Variable_name | Value | +--------------------------+------------+ | max_allowed_packet | 67108864 | | slave_max_allowed_packet | 1073741824 | +--------------------------+------------+ 2 rows in set (0.00 sec)
通过业务表查询恢复到的时间点为2017-03-31 15:32:51,如下：
MariaDB [md_sl]> 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@abc 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@abc 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 [ss]> 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)
