binlog server伪装master恢复增量数据

导读

接上一篇《一种MySQL备份恢复设计思路》，在上一篇文章中我们介绍了如何利用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.sql
mysqldump: [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: 0

node1 [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.sql
node3 [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]# ./stop
stop /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]# ./use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server 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 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.

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: NULL
Master_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: NULL
Master_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以上的版本，甚至可以用到并行加速恢复，缩短整个增量恢复的时间

全文完。

Enjoy MySQL :)

---

叶老师的「MySQL核心优化」大课已升级到MySQL 8.0，扫码开启MySQL 8.0修行之旅吧