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

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。

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

开始标识位号是上一个事件的结束标识位号，如果我们要截取多个事件产生的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语句不用管。

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将数据库还原成功。

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都会被查询出来。