MySQL 闪回技术总结
一、闪回技术汇总
1. 第一类为以 patch 形式集成到官方工具 mysqlbinlog 中，阿里彭立勋 2012 年曾提交过相关工具；
2. 第二类是独立工具，通过伪装 Slave 拉取 Binlog 来进行处理，以美团的 binlog2sql 为代表；
○ 优点：兼容性好、安装部署简单、源码难度不大可以进行改造；
○ 缺点：需要连接数据库；
3. 第三类简单脚本，使用 mysqlbinlog 生成文本然后根据回滚原理及正则进行匹配和替换；
○ 优点：支持离线解析、脚本写起来简单；
○ 缺点：通用性及兼容性不好；
二、binlog 初探
● binlog 介绍：二进制日志为 MySQL 记录数据库变化的日志，主要应用于主从复制和配合其它工具实现数据恢复。binlog 文件分为两类，其中一类为 mysql-bin.00000n 里面记录的是 binlog 事件，另一类为 mysql-bin.index 为 MySQL 二进制日志的索引文件，负责跟踪服务器上所有的 binlog 文件以便必要时可以正确的创建新的 binlog 文件。
● binlog 组成单元：每一个 binlog 文件由若干个 binlog 事件组成，以 Format_description（格式描述事件）作为文件头，以 Rotate（日志轮换事件）作为文件尾。除了控制事件，binlog 中其它事件被分为组，在事务存储引擎中，每个事务就是一个组，但是对于非事物存储引擎每条 SQL 语句就是一个组。
● binlog 事件的组成单元：
a. 通用头（Common Header）：记录事件的基本信息，事件类型和大小等；
b. 提交头（Post Header）：提交头与特定的事件类型有关，不同的事件类型，该字段存储的信息不同；
c. 事件体（Event Body）：存储事件的主要信息；
d. 校验和（Checksum）：从 5.6 版本开始新增校验参数，如果服务器设置产生校验的话，事件末位会产生校验字段，类型为 32 位整数，用于检查事件写入后是否有损坏。
● binlog 写入：由于 binlog 属于公共资源，大部分会话线程都需要写二进制日志，所以需要避免两个线程同时更新二进制日志，所以在写入二进制日志之前都会获得一个互斥锁 LOCK_log 写完事件后释放。由于服务器所有的会话线程都对应的是一个 binlog 所以 LOCK_log 常常会堵塞某些会话线程。
三、“篡改” binlog 实现闪回
● 原理：MySQL binlog 中使用二进制数字来标记 binlog 的事件，例如一条 DELETE 语句会使用 30 来标记写入事件，使用 32 来标记为删除事件，我们只需要找到对应的 position 然后修改其事件类型即可完成闪回；
● binlog 事件引入：首先为们可以看到 Log_name 为 binlog 事件写入的物理文件名，Pos 为事件开始的位置 End_log_pos 为事件结束的位置，使用 End_log_pos 减去 Pos 就可以知道这个事件占有的字节大小 Event_type 为事件类型 Server_id 为该事件的服务器 MySQL Server ID Info 该事件的描述信息。
Log_name Pos Event_type Server_id End_log_pos Info
mysql-bin.000001 4 Format_desc 33061 123 Server ver: 5.7.32-log, Binlog ver: 4
mysql-bin.000001 123 Previous_gtids 33061 154
mysql-bin.000001 154 Gtid 33061 219 SET @@SESSION.GTID_NEXT= '7d8ce50b-2e3c-11eb-b893-000c29adbb20:1'
mysql-bin.000001 219 Query 33061 293 BEGIN
mysql-bin.000001 293 Table_map 33061 355 table_id: 238 (school.student)
mysql-bin.000001 355 Delete_rows 33061 410 table_id: 238 flags: STMT_END_F
mysql-bin.000001 410 Xid 33061 441 COMMIT /* xid=4363 */
●
● binlog 常见事件类型（详细请见 MySQL Event Meanings）：
事件名 含义
Format_desc 格式描述事件，属于系统控制事件
Query 语句编写
Table_map 记录即将要修改表的结构
Xid 事务 commit 时候写入的事物 ID
Delete_rows 表示为一个删除事件
●
● MySQL 内部如何标记 binlog 事件类型（详细请见 MySQL Event Classes and Types）下面的 C++ 代码片段为 MySQL 官方文档中给出的事件类型代码。我们可以看到 WRITE_ROWS_EVENT 事件在源码中使用 30 来标记，也就是说在 binlog 事件的 Event_type 中使用二进制 30 来标记事件为一个写入事件，同理使用 32 来标记一个 DELETE_ROWS_EVENT 写入事件。
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 */
};
● 下面为我们使用 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
BINLOG '
gJLQXxMlgQAAPgAAAGMBAAAAAO4AAAAAAAEABnNjaG9vbAAHc3R1ZGVudAAEDw8SDwcoACgAACgA
DxKVlvY=
gJLQXx4lgQAANwAAAJoBAAAAAO4AAAAAAAEAAgAE//ACMDMG5a2Z6aOOmUaoAAAD55S3VXXJMQ==
'/*!*/;
● MySQL 内部如何来存储事件（详细请见 MySQL Event Header Fields）下面是 MySQL V4 版本的 binlog 事件头的文件偏移量，从中我们可以了解到在一个 binlog 事件中 type_code 存储在第 5 个字节，也就是说上面 type_event=30 存储在该事件里的第 5 个字节
Event_type Position
timestamp 0 : 4
type_code 4 : 1
server_id 5 : 4
event_length 9 : 4
next_position 13 : 4
flags 17 : 2
extra_headers 19 : x-19
●
● 我们已经了解 binlog 事件类型及存储结构，也就理解了如何去通过 ‘篡改’ binlog 实现闪回，接下来请看一个 闪回案例。
三、闪回失败案例
1. 收到需要进行一次 DELETE 语句的闪回 Position 位置点相关信息都有，安装流程进行闪回：
--start-position=551445 --stop-position=1298494
2. 下载对应的 binlog 文件：
python chtype.py mysql-bin.002759 mysql-bin.002759-bak
3. 查询之前的 binlog 事件我们发现属于 DELETE 事件，此时我们已经将 bak 修改为写入事件：
# at 551445
#201208 10:27:20 server id 388932253 end_log_pos 551514 CRC32 0xeeee42a2 Table_map: `eolinker_os`.`eo_api_cache` mapped to number 940
# at 551514
#201208 10:27:20 server id 388932253 end_log_pos 1298494 CRC32 0x66cb2d37 Delete_rows: table id 940 flags: STMT_END_F
### DELETE FROM `eolinker_os`.`eo_api_cache`
### WHERE
### @1=4272
### @2=25
### @3=922
### @4=4275
### @5='{"baseInfo":{"apiFailureMock"}
### @6=0
### @7=170
# at 1298494
# at 551445
#201208 10:27:20 server id 388932253 end_log_pos 551514 CRC32 0xeeee42a2 Table_map: `eolinker_os`.`eo_api_cache` mapped to number 940
# at 551514
#201208 10:27:20 server id 388932253 end_log_pos 1298494 CRC32 0x66cb2d37 Write_rows: table id 940 flags: STMT_END_F
### INSERT INTO `eolinker_os`.`eo_api_cache`
### SET
### @3=1549556828
### @4=1549556828
### @5=NULL
### @7=NULL
### INSERT INTO `eolinker_os`.`eo_api_cache`
### SET
### @3=1549556828
### @4=1549556828
### @5=NULL
### @7=NULL
...........................
4. 我们发现闪回生成的格式是有问题的，并没有达到我们的预期，我们看到了一句报错信息：
### INSERT INTO `eolinker_os`.`eo_api_cache`
### SET
### @3=1549556828
### @4=NULL
### @5=***Corrupted replication event was detected. Not printing the value***
# at 1298494
检查到事件信息损坏，不打印值，我们了解到如果改错了位置的话，那么整个 binlog 文件会损坏 mysqlbinlog 也无法解析出相关的事件信息，那么究竟是什么问题导致出现这个错误呢？
5. 经过一阵折腾我们从 MySQL 5.7 版本中的 binlogevent 源码中找到了答案（准确来说是源码上的注释😄）
num Log_event_type
{
/** The V1 event numbers are used from 5.1.16 until mysql-trunk-xx **/
WRITE_ROWS_EVENT_V1 = 23,
UPDATE_ROWS_EVENT_V1 = 24,
DELETE_ROWS_EVENT_V1 = 25,
/** Version 2 of the Row events **/
WRITE_ROWS_EVENT = 30,
UPDATE_ROWS_EVENT = 31,
DELETE_ROWS_EVENT = 32,
}
MySQL 二进制日志的版本不同，有使用 V1 与 V2 我们使用 V2 的 event 编码，导致闪回出现异常。
6. 总结：修改 binlog 实现闪回修改确认事件的 Positon 编号，然后通过 Position 推算出 Event_type 的位置，然后根据需求修改对应的 binlogevent 编码即可完成修改。
○ 优点：绝对准确、支持离线解析、对云上数据库也很友好；
○ 缺点：不支持 UPDATA 闪回；
