本文共 13815 字,大约阅读时间需要 46 分钟。
接上一篇,在上一篇文章中我们介绍了如何利用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以上的版本,甚至可以用到并行加速恢复,缩短整个增量恢复的时间
转载地址:http://imvsf.baihongyu.com/