导读
接上一篇《一种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 :)
