binlog server伪装master恢复增量数据

比眉伴天荒 2022-11-21 09:29 133阅读 0赞

### 导读 ###

*接上一篇**[《一种MySQL备份恢复设计思路》][MySQL]，在上一篇文章中我们介绍了如何利用binlog来进行增量恢复，其中提到了用binlog server伪装master来进行增量恢复，那么今天我们来演示一下具体过程。*

**环境说明**

![format_png][]

### 准备工作 ###

备份前数据情况

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，我们用一个表格来梳理一下这个过程

**过程**

![format_png 1][]

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 :)

![format_png 2][]

--------------------

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

![format_png 3][]

[MySQL]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49bc8a4cc0aaeab1fa32e4ad43b21964f21d4202d124ff5c7b43b03d9fa16f7b5daea0dc&idx=1&mid=2653934550&scene=21&sn=e2a89dce3fc72e6e11341b30866791e4#wechat_redirect
[format_png]: /images/20221120/ba148ae7e72a436aa6903997e7c9f1b5.png
[format_png 1]: /images/20221120/6eb333f3f49b4ce68599c4a5e7fc159e.png
[format_png 2]: /images/20221120/39ae53f8bbd74006bf07559edbe5890a.png
[format_png 3]: /images/20221120/0ed41016a87041daa1ccf3f199a073c5.png

发表评论取消回复

表情：

评论列表（有 0 条评论，133人围观）

还没有评论，来说两句吧...

相关阅读

相关 Mysql-BinLog数据恢复

介绍 binlog,即二进制日志,它记录了数据库上的所有改变，并以二进制的形式保存在磁盘中；它可以用来查看数据库的变更历史、数据库增量备份和恢复、Mysql的复制（主

雨点打透心脏的1/2处/ 2023年10月02日 18:38/ 0 赞/ 19 阅读

相关 MySQL——通过binlog恢复数据

目录 1.binlog基本概念 2.MySQL开启binlog 3.使用binlog日志恢复数据 3.1.恢复前准备工作 3.2.数据恢复 3.2.1.通过mysq

短命女/ 2023年09月25日 22:22/ 0 赞/ 54 阅读

相关 mysql 利用binlog恢复数据

1.日志文件路径 ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4u

一时失言乱红尘/ 2023年01月17日 07:41/ 0 赞/ 189 阅读

相关 binlog server伪装master恢复增量数据

导读接上一篇[《一种MySQL备份恢复设计思路》][MySQL]，在上一篇文章中我们介绍了如何利用binlog来进行增量恢复，其中提到了用binlog server伪装

比眉伴天荒/ 2022年11月21日 09:29/ 0 赞/ 134 阅读

相关 mysql的binlog文件删除及从binlog恢复数据_从binlog恢复数据及Mysqlbinlog文件删除

用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除，这样被给定的日志成为第一个。例如： PURGE MA

水深无声/ 2022年10月25日 00:52/ 0 赞/ 159 阅读

相关 mysql binlog恢复数据

常用binlog操作命令： 1、查看所有binlog日志列表 mysql> show master logs; 2、查看当前正在写入的binlog文件 mysql

叁歲伎倆/ 2022年09月25日 01:16/ 0 赞/ 223 阅读

相关 MySQL(23) 数据恢复之binlog

文章目录一、前言 1、查看自己的数据库版本 2、官方文档 3、

Myth丶恋晨/ 2022年09月10日 05:23/ 0 赞/ 238 阅读

相关使用canal 监听mysql binlog获取增量数据

配置mysql sudo vi /etc/my.cnf [mysqld] log-bin=/var/lib/mysql/mysql-bin

系统管理员/ 2022年09月02日 08:13/ 0 赞/ 251 阅读

相关 MySQL binlog 增量数据解析服务

1. 起因做过后端开发的同学都知道, 经常会遇到如下场景: 1. 后端程序根据业务逻辑, 更新数据库记录 2. 过了几天, 业务需求需要更新搜索索引 3. 又过

谁践踏了优雅/ 2022年05月04日 04:30/ 0 赞/ 183 阅读

相关 binlog2sql恢复mysql数据

1.查看数据库是否开启binlog show variables like '%log\_bin%'; ![watermark_type_ZmFuZ3poZW5naGVpd

迈不过友情╰/ 2021年12月23日 22:33/ 0 赞/ 270 阅读