binlog server伪装master恢复增量数据

微信扫一扫,分享到朋友圈

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

微信扫一扫,分享到朋友圈

binlog server伪装master恢复增量数据

Oracle首席工程师:技术面试中,怎样的问题才是好问题?

上一篇

深入理解RabbitMQ中的prefetch_count参数

下一篇

你也可能喜欢

binlog server伪装master恢复增量数据

长按储存图像,分享给朋友