导读

接上一篇，在上一篇文章中我们介绍了如何利用binlog来进行增量恢复，其中提到了用binlog server伪装master来进行增量恢复，那么今天我们来演示一下具体过程。

环境说明

准备工作

备份前数据情况

MySQL [xucl]> select * from t1;+----+----+| id | c1 |+----+----+|  1 | a  ||  2 | b  ||  3 | c  ||  4 | d  ||  5 | e  |+----+----+5 rows in set (0.00 sec)MySQL [xucl]> select * from t2;Empty set (0.00 sec)

为了简单起见，我们这里利用mysqldump来进行数据备份（这里最重要的是需要获取全备的GTID位点）

[root@VM_0_9_centos node1]# ~/sandboxes/mysql_base/5.7.30/bin/mysqldump \> -h127.0.0.1 -umsandbox -pmsandbox -P24731 \> -A --single-transaction > /tmp/xucl.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. [root@VM_0_9_centos node1]# less /tmp/xucl.sql

我们看到备份时间点的GTID位点为：00024731-1111-1111-1111-111111111111:1-20

---- GTID state at the beginning of the backup --SET @@GLOBAL.GTID_PURGED='00024731-1111-1111-1111-111111111111:1-20';

这个时候，我们在t2表写入一些数据来模拟增量数据的产生

node1 [localhost:24731] {msandbox} (xucl) > insert into t2 select * from t1 where id<=3;Query OK, 3 rows affected (0.04 sec)Records: 3  Duplicates: 0  Warnings: 0node1 [localhost:24731] {msandbox} (xucl) > select * from t2;+----+----+| id | c1 |+----+----+|  1 | a  ||  2 | b  ||  3 | c  |+----+----+3 rows in set (0.00 sec)

接着，t1表被误删除数据了。

node1 [localhost:24731] {msandbox} (xucl) > truncate table t1;Query OK, 0 rows affected (0.02 sec)node1 [localhost:24731] {msandbox} (xucl) > show master status;+------------------+----------+--------------+------------------+-------------------------------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |+------------------+----------+--------------+------------------+-------------------------------------------+| mysql-bin.000001 |     5479 |              |                  | 00024731-1111-1111-1111-111111111111:1-22 |+------------------+----------+--------------+------------------+-------------------------------------------+1 row in set (0.00 sec)

OK，我们用一个表格来梳理一下这个过程

过程

OK，我们需要恢复的是全备+增备（这里对应GTID uuid:21的那个事务）

开始恢复

我们首先在node3完成全量恢复

node3 [localhost:24733] {msandbox} (xucl) > reset master;node3 [localhost:24733] {msandbox} (xucl) > source /tmp/xucl.sqlnode3 [localhost:24733] {msandbox} (xucl) > show master status;+------------------+----------+--------------+------------------+-------------------------------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |+------------------+----------+--------------+------------------+-------------------------------------------+| mysql-bin.000001 |      154 |              |                  | 00024731-1111-1111-1111-111111111111:1-20 |+------------------+----------+--------------+------------------+-------------------------------------------+1 row in set (0.00 sec)

接下来，我们来做增量恢复。

首先你需要将binlog从binlog server上拷贝到一台新的MySQL实例，或者你可以直接在binlog server上新建一个实例，我们这里就利用node2

我们先查看node2的binlog

node2 [localhost:24732] {msandbox} ((none)) > show binary logs;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |      4089 |+------------------+-----------+1 row in set (0.00 sec)node2 [localhost:24732] {msandbox} ((none)) > flush binary logs;Query OK, 0 rows affected (0.03 sec)node2 [localhost:24732] {msandbox} ((none)) > show binary logs;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |      4136 || mysql-bin.000002 |       194 |+------------------+-----------+2 rows in set (0.00 sec)

为了验证后续的binlog注册步骤，这里我进行了binlog切换

关闭node2实例

[root@VM_0_9_centos node2]# ./stopstop /root/sandboxes/multi_msb_5_7_30/node2

将node1的binlog拷贝过来进行注册

# 移走本实例的两个binlog[root@VM_0_9_centos data]# mv mysql-bin.000001 ../[root@VM_0_9_centos data]# mv mysql-bin.000002 ../# 将node1的binlog拷贝到本地[root@VM_0_9_centos data]# cp ~/sandboxes/multi_msb_5_7_30/node1/data/mysql-bin.000001 .# 修改mysql-bin.index进行注册[root@VM_0_9_centos data]# cat mysql-bin.index ./mysql-bin.000001

启动node2

[root@VM_0_9_centos node2]# ./start . sandbox server started[root@VM_0_9_centos node2]# ./useWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.30-log MySQL Community Server (GPL)Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.node2 [localhost:24732] {msandbox} ((none)) > show binary logs;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |      5479 || mysql-bin.000002 |       194 |+------------------+-----------+2 rows in set (0.00 sec)node2 [localhost:24732] {msandbox} ((none)) > show master status;+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                    |+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+| mysql-bin.000002 |      194 |              |                  | 00024731-1111-1111-1111-111111111111:1-22,00024732-2222-2222-2222-222222222222:1-16 |+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+1 row in set (0.00 sec)

启动完成以后看到node1的binlog已经注册上来了，但是多了mysql-bin.000002文件，具体没有深入研究，猜测大概是dbdeployer进行了初始化的动作。不管怎么样，node1的binlog已经注册上来了。接下来我们将node3作为node2的从库来进行数据恢复。

由于上一步引入了新的GTID set，我们需要修改一下node3的gtid_purged

node3 [localhost:24733] {msandbox} ((none)) > show master status;+------------------+----------+--------------+------------------+-------------------------------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |+------------------+----------+--------------+------------------+-------------------------------------------+| mysql-bin.000001 |      154 |              |                  | 00024731-1111-1111-1111-111111111111:1-20 |+------------------+----------+--------------+------------------+-------------------------------------------+1 row in set (0.00 sec)node3 [localhost:24733] {msandbox} ((none)) > show global variables like 'gtid_purged';+---------------+-------------------------------------------+| Variable_name | Value                                     |+---------------+-------------------------------------------+| gtid_purged   | 00024731-1111-1111-1111-111111111111:1-20 |+---------------+-------------------------------------------+1 row in set (0.00 sec)node3 [localhost:24733] {msandbox} ((none)) > reset master;Query OK, 0 rows affected (0.04 sec)node3 [localhost:24733] {msandbox} ((none)) > set global gtid_purged='00024731-1111-1111-1111-111111111111:1-20,00024732-2222-2222-2222-222222222222:1-16';Query OK, 0 rows affected (0.01 sec)node3 [localhost:24733] {msandbox} ((none)) > show master status;+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                    |+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+| mysql-bin.000001 |      154 |              |                  | 00024731-1111-1111-1111-111111111111:1-20,00024732-2222-2222-2222-222222222222:1-16 |+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+1 row in set (0.00 sec)

到这里增量恢复前期准备工作已经全部准备就绪了，开始恢复到指定GTID位点

node3 [localhost:24733] {msandbox} ((none)) > change master to master_host='127.0.0.1', \    -> master_port=24732 , \    -> master_user='rsandbox', \    -> master_password='rsandbox', \    -> master_auto_position=1;Query OK, 0 rows affected, 2 warnings (0.05 sec)node3 [localhost:24733] {msandbox} ((none)) > show slave status\G*************************** 1. row ***************************               Slave_IO_State:                   Master_Host: 127.0.0.1                  Master_User: rsandbox                  Master_Port: 24732                Connect_Retry: 60              Master_Log_File:           Read_Master_Log_Pos: 4               Relay_Log_File: mysql-relay.000001                Relay_Log_Pos: 4        Relay_Master_Log_File:              Slave_IO_Running: No            Slave_SQL_Running: No              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 0              Relay_Log_Space: 154              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 0                  Master_UUID:              Master_Info_File: mysql.slave_master_info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State:            Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set: 00024731-1111-1111-1111-111111111111:1-20,00024732-2222-2222-2222-222222222222:1-16                Auto_Position: 1         Replicate_Rewrite_DB:                  Channel_Name:            Master_TLS_Version: 1 row in set (0.00 sec)node3 [localhost:24733] {msandbox} ((none)) > start slave until SQL_BEFORE_GTIDS ='00024731-1111-1111-1111-111111111111:22';Query OK, 0 rows affected (0.01 sec)node3 [localhost:24733] {msandbox} ((none)) > show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 127.0.0.1                  Master_User: rsandbox                  Master_Port: 24732                Connect_Retry: 60              Master_Log_File: mysql-bin.000002          Read_Master_Log_Pos: 194               Relay_Log_File: mysql-relay.000002                Relay_Log_Pos: 686        Relay_Master_Log_File: mysql-bin.000001             Slave_IO_Running: Yes            Slave_SQL_Running: No              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 5330              Relay_Log_Space: 1494              Until_Condition: SQL_BEFORE_GTIDS               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 24732                  Master_UUID: 00024732-2222-2222-2222-222222222222             Master_Info_File: mysql.slave_master_info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State:            Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set: 00024731-1111-1111-1111-111111111111:21-22            Executed_Gtid_Set: 00024731-1111-1111-1111-111111111111:1-21,00024732-2222-2222-2222-222222222222:1-16                Auto_Position: 1         Replicate_Rewrite_DB:                  Channel_Name:            Master_TLS_Version: 1 row in set (0.00 sec)

查看t1表数据

node3 [localhost:24733] {msandbox} (xucl) > select * from t1;+----+----+| id | c1 |+----+----+|  1 | a  ||  2 | b  ||  3 | c  ||  4 | d  ||  5 | e  |+----+----+5 rows in set (0.00 sec)node3 [localhost:24733] {msandbox} (xucl) > select * from t2;+----+----+| id | c1 |+----+----+|  1 | a  ||  2 | b  ||  3 | c  |+----+----+3 rows in set (0.00 sec)

OK，到这里t1表数据已经找回来了，接下来的事情就比较简单了，跟研发确认数据以后，就可以在node3上将数据导出，然后导入到node1，或者利用表空间传输也能够完成。

总结一下

整个过程实际上并不复杂，需要做的主要的就是如下几点：

• 找到需要恢复的起始GTID位点和终止GTID位点

• 从binlog server上拉取对应的binlog或者直接在binlog server上部署一个空实例

• 注册binlog，这一步比较关键

• 设置异机恢复实例的gtid_purged，配置主从关系

• 利用命令start slave until SQL_BEFORE_GTIDS恢复到指定的位点

• 假如你是5.7以上的版本，甚至可以用到并行加速恢复，缩短整个增量恢复的时间