MySQL运维实战之备份和恢复（8.7）将数据库恢复到指定时间点的另外一种方法

作者：俊达

使用mysql原生复制功能实现时间点恢复

使用mysqlbinlog解析并执行binlog是实现mysql时间点恢复的一种常用的方法。这里提供另外一种实现时间点恢复的方法：使用mysql的复制功能来实现。

方法如下：

1、启动一个空的mysql实例，将需要恢复的binlog注册到该实例。

2、待恢复实例上，从binlog实例复制数据。

启动binlog实例

初始化一个空白实例：

## 创建相关目录
mkdir -p /data/servebinlog/{data,log,binlog,relaylog,tmp,run}

## 准备my.cnf

## 初始化数据库
/opt/mysql/bin/mysqld --defaults-file=/data/servebinlog/my.cnf --initialize


tail -1 /data/servebinlog/log/alert.log
2023-06-26T08:54:33.758630Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ar2sWPCf4.++

## 修改文件owner，并启动实例
chown -R mysql:mysql /data/servebinlog/

mysqld_safe --defaults-file=/data/servebinlog/my.cnf &

## 修改默认密码，创建复制账号
mysql -uroot -par2sWPCf4.++ -h127.0.0.1 -P16380

mysql> alter user 'root'@'localhost' identified by 'abc123';
Query OK, 0 rows affected (0.01 sec)

mysql> create user 'rep'@'%' identified by 'rep123';
Query OK, 0 rows affected (0.02 sec)

mysql> grant replication slave on *.* to 'rep'@'%';
Query OK, 0 rows affected (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

注册binlog：

将需要的binlog复制到binlog实例相关目录，并编辑binlog.index文件，重启binlog实例。

## 停实例
mysqladmin -h127.0.0.1 -P16380 -uroot -pabc123 shutdown

## 清空原有binlog，将需要的binlog复制到指定目录
rm /data/servebinlog/binlog/binlog.00000*
cp /data/mysql8.0/binlog/binlog.0000{20,21,22} /data/servebinlog/binlog/


## 编辑binlog.index，最终内容如下
# cat /data/servebinlog/binlog/binlog.index
/data/servebinlog/binlog/binlog.000020
/data/servebinlog/binlog/binlog.000021
/data/servebinlog/binlog/binlog.000022

## 启动实例
chown -R mysql:mysql /data/servebinlog/
mysqld_safe --defaults-file=/data/servebinlog/my.cnf &
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

到这一步，binlog实例准备完成，

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000020 |   5598638 | No        |
| binlog.000021 |   4751928 | No        |
| binlog.000022 |   3944712 | No        |
| binlog.000023 |       237 | No        |
+---------------+-----------+-----------+


mysql> select @@gtid_purged\G
*************************** 1. row ***************************
@@gtid_purged: 0b06b27c-13ff-11ee-8d21-fab81f64ee00:1-3,
58224b02-09b7-11ee-90bd-fab81f64ee00:1-13191,
7caa9a48-b325-11ed-8541-fab81f64ee00:27
1 row in set (0.00 sec)

mysql> select @@gtid_executed\G
*************************** 1. row ***************************
@@gtid_executed: 0b06b27c-13ff-11ee-8d21-fab81f64ee00:1-3,
58224b02-09b7-11ee-90bd-fab81f64ee00:1-51514,
7caa9a48-b325-11ed-8541-fab81f64ee00:27
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

从binlog实例复制数据

查看全备的binlog位点：

# cat xtrabackup_binlog_info
binlog.000020	610	58224b02-09b7-11ee-90bd-fab81f64ee00:1-13191,7caa9a48-b325-11ed-8541-fab81f64ee00:1-27
1
2

到需要进行时间点恢复的实例上执行：

mysql> reset slave all;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> change master to master_host='172.16.121.234', master_port=16380, master_user='rep', master_password='rep123', get_master_public_key=1, master_log_file='binlog.000020', master_log_pos=610;
Query OK, 0 rows affected, 9 warnings (0.13 sec)

mysql> start slave until master_log_file='binlog.000021', master_log_pos=340009;
Query OK, 0 rows affected, 3 warnings (0.03 sec)
1
2
3
4
5
6
7
8

等待SQL线程执行完成，执行完成的标志是Slave_SQL_Running=No，并且Relay_Master_Log_File和Exec_Master_Log_Pos和start slave until命令中指定的位点一样。

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.16.121.234
                  Master_User: rep
                  Master_Port: 16380
                Connect_Retry: 60
              Master_Log_File: binlog.000023
          Read_Master_Log_Pos: 237
               Relay_Log_File: relaylog.000004
                Relay_Log_Pos: 340210
        Relay_Master_Log_File: binlog.000021
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
......
          Exec_Master_Log_Pos: 340040
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

和直接使用mysqlbinlog解析binlog并执行的方法对比，使用binlog实例进行时间点恢复需要创建一个临时空白实例，看起来更加复杂，但是具备以下优点：

1、更加可靠。使用了mysql原生的复制代码来执行binlog，比使用mysqlbinlog解析binlog并执行更可靠。

2、效率更高。如果开启了多线程复制，应用binlog的效率会更高。

3、更容易处理异常。如果应用binlog的过程中遇到各种错误，可以像处理一个普通的备库遇到的问题那样来处理。

正式的环境中，更推荐使用binlog实例来进行时间点恢复。

binlog恢复注意事项

使用备库产生的binlog恢复数据时，需要注意备库是否有延迟。如果备库本身存在延迟，那么备库上生成的binlog的时间戳和真实的业务时间可能存在偏差，依据这个时间来进行恢复，就可能达不到业务上的恢复目标。

所以进行时间点恢复时，优先选择使用主库的binlog来进行恢复。当然在实际业务场景中，主备库之间可能会存在角色切换，需要想办法确定待恢复的时间点上，哪一个实例是主库。

我们也需要监控好主备库的复制状态，如果存在复制中断、复制延迟、甚至是主备库数据不一致的情况，需要及时处理。不然，在对备库进行的备份可能实际上是无效的。

更多技术信息请查看云掣官网https://yunche.pro/?t=yrgw