数据库 MySQL Binlog 闪回与分析

文章目录

前言1. 修改 event 实现闪回1.1 binlog 结构1.2 闪回案例1.3 方法总结

2. 解析文本闪回2.1 mysqlbinlog2.2 闪回案例2.3 方法总结

3. 在线订阅闪回3.1 mysql-replication3.2 binlog2sql3.3 方法总结

4. Binlog 分析方法4.1 分析场景4.2 辅助定位事务4.3 方法总结

5. 平台化的解决方案5.1 数据追踪5.2 方法总结

总结

前言

由于误操作、代码 bug 或平台误点，我们在操作数据时难免会遇到数据丢失的情况，比如一条 delete 删除了预期之外的数据。早期想要恢复数据，只能通过全量备份 + 日志备份恢复数据或业务人员通过日志以及业务逻辑进行手动订正，这些恢复数据的方法影响其恢复速度的变量很多，全量备份如果数据量很大，上传和解压缩的耗时很久，还要考虑增量日志应用。手动订正数据量大业务逻辑复杂的话，是一件非常消耗人力的事情，且容易出错。直到出现 Binlog 闪回技术，大大的提升了 DML 造成数据丢失的恢复速度。

本篇文章，将带你了解 SQL 闪回的三种方法，以及 Binlog 分析方法等。

1. 修改 event 实现闪回

这是一种可以将 delete 操作转换为 insert 操作的一种方法。MySQL binlog 中使用 type_code 来标记 binlog 的事件，例如一个 delete 操作会使用 32 来标记，一个 insert 操作会使用 30 来标记。通过将 delete 事件 type_code 32 修改为 30 就可以将一个删除操作转换为插入操作，就可以回滚 delete 误操作的数据。

该方法需要对 Binlog 的结构有了解，下面介绍一些相关知识。

PS：本文所有的演示，要求的 Binlog 参数配置如下： binlog_format = ROW binlog_row_image = FULL

1.1 binlog 结构

binlog 日志为 MySQL 记录数据库变化的日志，主要应用于主从复制和配合其它工具实现数据恢复。binlog 文件分为两类，其中一类为 mysql-bin.00000n 里面记录的是 binlog 事件，另一类为 mysql-bin.index 为 MySQL 二进制日志的索引文件，负责跟踪服务器上所有的 binlog 文件以便必要时可以正确的创建新的 binlog 文件。

每一个 binlog 文件由若干个 binlog 事件组成，以 Format_description（格式描述事件）作为文件头，以 Rotate（日志轮换事件）作为文件尾。除了控制事件，binlog 中其它事件被分为组，在事务存储引擎中，每个事务就是一个组，但是对于非事物存储引擎每条 SQL 语句就是一个组。

从 MySQL 5+ 版本开始，Binlog 采用的是 v4 版本。事件的类型根据 MySQL 的内部文档，有下面 36 类：

enum Log_event_type {

UNKNOWN_EVENT= 0,

START_EVENT_V3= 1,

QUERY_EVENT= 2,

STOP_EVENT= 3,

ROTATE_EVENT= 4,

INTVAR_EVENT= 5,

LOAD_EVENT= 6,

SLAVE_EVENT= 7,

CREATE_FILE_EVENT= 8,

APPEND_BLOCK_EVENT= 9,

EXEC_LOAD_EVENT= 10,

DELETE_FILE_EVENT= 11,

NEW_LOAD_EVENT= 12,

RAND_EVENT= 13,

USER_VAR_EVENT= 14,

FORMAT_DESCRIPTION_EVENT= 15,

XID_EVENT= 16,

BEGIN_LOAD_QUERY_EVENT= 17,

EXECUTE_LOAD_QUERY_EVENT= 18,

TABLE_MAP_EVENT = 19,

PRE_GA_WRITE_ROWS_EVENT = 20,

PRE_GA_UPDATE_ROWS_EVENT = 21,

PRE_GA_DELETE_ROWS_EVENT = 22,

WRITE_ROWS_EVENT = 23,

UPDATE_ROWS_EVENT = 24,

DELETE_ROWS_EVENT = 25,

INCIDENT_EVENT= 26,

HEARTBEAT_LOG_EVENT= 27,

IGNORABLE_LOG_EVENT= 28,

ROWS_QUERY_LOG_EVENT= 29,

WRITE_ROWS_EVENT = 30,

UPDATE_ROWS_EVENT = 31,

DELETE_ROWS_EVENT = 32,

GTID_LOG_EVENT= 33,

ANONYMOUS_GTID_LOG_EVENT= 34,

PREVIOUS_GTIDS_LOG_EVENT= 35,

ENUM_END_EVENT

/* end marker */

};

Binlog 日志由多个 event 组成，一个 event 分为 header 和 data 两部分，通过解析 header 事件就可以知道该事件的类型和长度。

+=====================================+

| event | timestamp 0 : 4 |

| header +----------------------------+

| | type_code 4 : 1 |

| +----------------------------+

| | server_id 5 : 4 |

| +----------------------------+

| | event_length 9 : 4 |

| +----------------------------+

| | next_position 13 : 4 |

| +----------------------------+

| | flags 17 : 2 |

| +----------------------------+

| | extra_headers 19 : x-19 |

+=====================================+

| event | fixed part x : y |

| data +----------------------------+

| | variable part |

+=====================================+

下面为我们使用 mysqlbinlog --hexdump 转换为十六进制的 binlog 可以看到 event_type 为 1e 转换为十进制为 1e(十六进制) = 30(十进制) 表明该事件为写入事件。

# at 355

#201209 17:01:52 server id 33061 end_log_pos 410 CRC32 0x31c97555

# Position Timestamp Type Master ID Size Master Pos Flags

# 163 80 92 d0 5f 1e 25 81 00 00 37 00 00 00 9a 01 00 00 00 00

# 176 ee 00 00 00 00 00 01 00 02 00 04 ff f0 02 30 33 |..............03|

# 186 06 e5 ad 99 e9 a3 8e 99 46 a8 00 00 03 e7 94 b7 |........F.......|

# 196 55 75 c9 31 |Uu.1|

# Write_rows: table id 238 flags: STMT_END_F

所以，通过修改 event_type 实现数据恢复的原理，就是定位到数据的 event_type 位置，将 DELETE_ROWS_EVENT 替换为 WRITE_ROWS_EVENT 实现回滚 SQL。

1.2 闪回案例

执行一个 delete 删除操作。

delete from student where SId = 04

准备一个简单的 Python 脚本，修改 Event_type。

import sys

if len(sys.argv) != 3:

sys.exit()

inputType = open(sys.argv[1], "rb")

changedType = open(sys.argv[2], "wb")

changedType.write(inputType.read(359))

changedType.write(chr(30).encode())

inputType.seek(1, 1)

while True:

line = inputType.readline()

if not line:

break

changedType.write(line)

inputType.close()

changedType.close()

执行脚本程序，执行完成之后会生成一个 bak 文件，是替换过 event code 的 binlog 文件。

python chtype.py mysql-bin.000001 mysql-bin.000001-bak

使用 mysqlbinlog 对比两个 binlog 文件内容，下方是原文件的内容。

BEGIN

/*!*/;

# at 293

#201201 11:13:06 server id 33061 end_log_pos 355 CRC32 0xfc95f44d Table_map: `school`.`student` mapped to number 238

# at 355

#201201 11:13:06 server id 33061 end_log_pos 410 CRC32 0x6912246f Delete_rows: table id 238 flags: STMT_END_F

### DELETE FROM `school`.`student`

### WHERE

### @1='04'

### @2='李云'

### @3='1990-12-06 00:00:00'

### @4='男'

# at 410

#201201 11:13:06 server id 33061 end_log_pos 441 CRC32 0x24c5d71c Xid = 4363

COMMIT/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

下方是经过篡改过 event code 后的 binlog 文件。

BEGIN

/*!*/;

# at 293

#201201 11:13:06 server id 33061 end_log_pos 355 CRC32 0xfc95f44d Table_map: `school`.`student` mapped to number 238

# at 355

#201201 11:13:06 server id 33061 end_log_pos 410 CRC32 0x6912246f Write_rows: table id 238 flags: STMT_END_F

### INSERT INTO `school`.`student`

### SET

### @1='04'

### @2='李云'

### @3='1990-12-06 00:00:00'

### @4='男'

# at 410

#201201 11:13:06 server id 33061 end_log_pos 441 CRC32 0x24c5d71c Xid = 4363

COMMIT/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

现在拿着替换后的 binlog 到数据库中执行一下，这条记录就可以恢复。

1.3 方法总结

这种直接修改 Binlog Event 的方法虽然可以将 delete 操作转换为 insert 操作，但较难实现 update 闪回（能实现但是更复杂）且计算 type_cdoe 需要对 Binlog 的结构非常熟悉。经过我们的统计，生产环境中 delete 误删事件只约占 10%，因为数据一般不会真正删除，而是表中有一个字段，用来标记删除，所以 update 导致的数据 “丢失” 占比较高有 80%，剩下的就是 drop 或者 truncate DDL 这种 binlog 不可逆的 DDL 操作，只能通过全量备份恢复。

由于操作复杂，且应对场景有限，这种恢复数据的方式，就没有继续探索下去了，不过是一个学习 binlog 非常好的案例。

PS：美团数据库团队，开源了一款 SQL 闪回工具，MyFlash 基于修改 Binlog Event 结构的方式回滚事务。其中 delete/insert 回滚的方法，与本小节介绍的方法相同。

2. 解析文本闪回

通过 mysqlbinlog 可以将 binlog 文件解析成文本，可以通过对内容进行文本处理正则匹配和替换，从而实现闪回。

2.1 mysqlbinlog

下方是使用 mysqlbinlog 将 binlog 文件解析成文本的命令，还可以添加时间范围 position 位点等过滤条件。

mysqlbinlog -vv --base64-output=decode-rows ./mysqlbin.003626 > all.sql

mysqlbinlog 解析后的内容，多余内容已删除。

### UPDATE `test`.`test_semi`

### WHERE

### @1=10 /* INT meta=0 nullable=0 is_null=0 */

### @2=1 /* INT meta=0 nullable=1 is_null=0 */

### @3=10 /* INT meta=0 nullable=1 is_null=0 */

### SET

### @1=10 /* INT meta=0 nullable=0 is_null=0 */

### @2=1 /* INT meta=0 nullable=1 is_null=0 */

### @3=111 /* INT meta=0 nullable=1 is_null=0 */

这是一个 update 事件，通过 mysqlbinlog 就可以将 binlog 解析成文本，分为两部分 WHERE 表示修改前的内容，既 before_values，SET 表示修改后的内容，既 after_values。此时还不能直接使用，需要将 update 中的 before_values 与 after_values 进行互换，或者直接根据 before_values 转换为 replace into 操作。

delete 事件原理也相同，通过正则匹配进行文本操作，转换为 insert 语句，再放到数据库中执行。

2.2 闪回案例

接下来，用一个生产环境遇到的案例，带大家了解整个数据恢复的过程。

记得当时是一个晚上八点左右时间，正当我准备探索提瓦特大陆的时候，收到研发经理的电话，其实看到这个来电人，以及这个来电的时间，我就心头一紧，准没好事…

他告诉我刚才有研发执行了一个 SQL 脚本，由于执行前没检查，谁知 SQL 文件中，有一条 update 没有带 where 条件，导致整张表被更新了，相当于更新了预期外的数据，产生了数据错乱，需要尽快修复。

让研发经理拉群，找那名研发确认操作时间，误操作的 SQL 语句，以及大致的影响行数。得到如下信息：

误操作时间：19:30 分左右，具体时间不详。误操作的 SQL 语句：-- SQL 是脱敏后的，当时也是更新全表

update test_semi set c = 111;

影响行数：约 1.7w 行。

第一步，定位事务的 position 位点：最原始方法是通过 mysqlbinlog 命令，指定时间范围，导出文本，如果打开文本文件，搜索关键字，匹配误操作的记录。

mysqlbinlog -vv --base64-output=decode-rows --start_datetime='2024-04-18 19:25:00' --stop_datetime='2024-04-18 19:45:00' --database='op_service_db_bak' ./mysql-bin.000014 > all.sql

通过不断缩小时间范围，搜索事务的 position 位点，下图为事务开始的位点。

PS：以下演示使用的是模拟数据，所以日志中的行数与案例描述不一致。

下图为事务结束的位点。

目前已经在 Binlog 中定位到误操作 position 位置，接下来需要使用脚本解析 update 语句将 WHERE 数据部分转换为 replace into 操作。

mysqlbinlog --base64-output=decode-rows -vvv --start-position=291 --stop-position=240425 ./mysql-bin.000002 | perl parse_binlog_preimage.pl > recover.sql

输出：

--table: `test`.`test_semi`