第78讲:截取MySQL Binlog二进制日志中特定部分内容的技巧

2023-12-15 10:42:32

1.为什么要截取Binlog日志中的部分内容

我们通过Binlog二进制日志恢复数据时,一般都会先用备份恢复全库的数据,然后再使用Binlog恢复备份中不存在的数据,因此再使用Binlog进行数据恢复时,并不是直接恢复整个Binlog日志中的数据,只是恢复Binlog中的部分数据。

根据特定的情况以及需求去恢复Binlog日志中的数据时,需要通过截取的方式,截取特定时间段产生的Binlog日志,然后进行数据恢复,或者截取指定的开始标识位到结束标识位之间的Binlog日志。

针对截取Binlog日志中的部分数据,有两种方法:

  • 通过标识位偏移量截取
    • 通过标识位偏移量截取,主要是根据事件的Position号标识位去截取的,首先要找到要恢复的数据在Binlog的那些事件中的,然后找到这些事件的开始标识位和结束标识位,拿到这两个标识位号之后,就可以截取其中的数据。
    • 使用最为广泛。
  • 通过时间范围截取
    • 根据时间范围,例如截取早上10点到12点之间产生的Binlog日志。

2.针对标识位截取Binlog日志中的部分数据

2.1.以标识位号截取Binlog日志的方法

使用标识位Position号来截取Binlog日志中部分的数据内容时,最关键的部分就在于如何确定要截取数据的标识位号范围,也就是确定标识位的起点和终点部分,我们可以查看Binlog的事件信息,找到要恢复的数据属于哪一个事件,然后获取事件的开始标识位和结束标识位,此时也就能确定要恢复的数据在Binlog日志中的位置。

当确定好开始标识位号和结束标识位号之后,就可以通过mysqlbinlog命令去截取指定的数据内容了。

通过标识位截取部分Binlog日志的语法格式:

mysqlbinlog --start-position=开始标识位号 --stop-position=结束标识位号 Binlog日志路径

获取截取之后的Binlog日志后,可以将其写入到SQL文件里,虽然ROW格式记录的Binlog我们看不懂,但是MySQL能看得懂,我们将截取的内容输入到SQL文件之后,就可以使用source命令对这个截取后的Binlog日志在数据库中进行数据恢复。

2.2.截取Binlog日志中的部分数据

在Binlog中有一条是关于创建db_2数据库的,我们将这个数据从Binlog日志中截取出来。

1)首先获取创建数据库的语句位于哪个事件中

查看Binlog日志中的Event事件信息,创建数据库属于DDL语句,因此可以直接看到具体的SQL在哪个事件中。

show binlog events in 'mysql-bin.000002';

2)获取要截取的数据标识位范围

如下图所示,我们已经知道创建db_2数据库的操作在哪个事件中了,想要截取这一部分的Binlog日志,就需要确定这个事件的标识位号,开始标识位号是219,结束标识位号是313。

如果不知道该怎么确定开始标识位和结束标识位,那么就去看一下要截取的内容是那个事件产生的,在事件信息中就包含了这个事件的开始标识位和结束标识位,内容是由这个事件产生的,那么通过这个事件的开始标识位和结束标识位就能拿到想要的数据。

image-20220701110217115

开始标识位号是上一个事件的结束标识位号,如果我们要截取多个事件产生的Binlog时,只需要记录第一个事件的开始标识位号,和最后一个事件的结束标识位号即可,如上图的开始标识位313和结束标识位729,在这个标识位范围内就存在了多个事件信息。

3)截取出创建db_2数据库的Binlog日志

目前我们已经拿到要截取的数据的开始标识位以及结束标识位号了,下面就可以通过mysqlbinlog目录来截取这两个标识位范围内产生的Binlog日志,然后将截取的内容输出到SQL文件里。

[root@mysql backup]# mysqlbinlog --start-position=219 --stop-position=313 /data/mysql/mysql-bin.000002 > mysql-binlog-20220701.sql

如下图所示,截取的内容中只包含了创建db_2数据库的Binlog日志,Binlog截取的没问题,SET语句不用管。

image-20220701113200910

2.3.模拟简单基于标识位的Binlog数据恢复

我们在前面根据需求,截取除了创建db_2数据库的Binlog日志,下面我们将db_2数据库删除,然后通过Binlog进行简单的数据恢复。

1.模拟误删除
mysql> drop database db_2;

2.1新开启一个MySQL会话禁止记录binlog(退出会话后配置失效)
#禁止将后续的操作记录到binlog中
mysql> set sql_log_bin=0;

2.从Binlog日志中进行数据恢复
mysql> source /root/backup/mysql-binlog-20220701.sql

在使用Binlog数据恢复前,需要先将要恢复的数据从Binlog日志中截取出来,然后输出到一个SQL文件里,最后使用source命令通过这个SQL文件还原误删除的数据。在数据还原之前可以先禁止将操作记录到binlog中,因为还原的数据本来就是从Binlog中截取的,没有必要再往Binlog中写一份,减少磁盘的占用。这里使用source进行还原而不是使用mysql进行还原,是因为source还原的性能要高,能减少IO的开销。

我们刚刚五删除了db_2数据库,然后通过Binlog将数据库还原成功。

image-20220701114856672

3.针对事件范围截取Binlog日志中的部分数据

使用时间范围截取的Binlog不如标识位截取的精准,因此在企业中应用的较少。

使用时间范围截取的Binlog语法格式:

mysqlbinlog --start-datetime="开始时间" --stop-datetime="结束时间" /data/mysql/mysql-bin.000002

时间格式要以"年-月-日 时:分:秒"的格式来定义

以时间范围截取Binlog的原理其实和Binlog中的注释信息有关,在每个注释里面都有一个时间,mysqlbinlog是根据这个时间来截取的。

案例:截取22:30到23:00之间产生的Binlog日志。

[root@mysql backup]# mysqlbinlog --start-datetime="2022-06-30 22:30:00" --stop-datetime="2022-06-30 23:00:00" /data/mysql/mysql-bin.000002

顺便提一个小的知识点:无论如何截取,标识位4-154之间的Binlog都会被查询出来。

image-20220701132235516

文章来源:https://blog.csdn.net/weixin_44953658/article/details/135009005
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。