本篇博文介绍 MySQL 二进制日志（binlog） 的底层结构和解析机制。下面我会系统性地讲解：
1️⃣ binlog中到底记录了什么；
2️⃣ binlog文件的具体数据结构；
3️⃣ 如何手动或程序化地解析binlog并还原为原始SQL。

🧱 一、MySQL Binlog记录的内容

MySQL Binlog 主要用于：

  • 主从复制（replication）；
  • 增量备份与恢复；
  • 审计与数据追踪。

记录的内容不是原始 SQL 文本（除非使用 STATEMENT 模式），而是事务性的“事件”（event）序列。

Binlog三种格式

  1. STATEMENT：直接记录执行的 SQL 语句。
  2. ROW：记录每一行被修改的“前镜像”和“后镜像”（row image）。
  3. MIXED：混合模式，MySQL 自动选择以上两种。

生产中常用 ROW 模式，因为它更安全、可精确重放。

🧩 二、Binlog 文件的物理结构

Binlog 是一个顺序写入的二进制文件，文件名形如：

mysql-bin.000001

文件头 + 一系列事件（Event）组成：

(1) 文件头（File Header）

固定17字节：

偏移量

长度

含义

0x00

4

Magic Number 0xfe'bin

0x04

13

保留字段（空）

(2) 事件通用头（Event Header）

每个事件前都有一个固定格式的头，长度19字节：

字段

长度

含义

timestamp

4

事件发生时间（UNIX时间戳）

type_code

1

事件类型（如 QUERY_EVENT, WRITE_ROWS_EVENT）

server_id

4

产生事件的服务器ID

event_size

4

当前事件总字节数（头+体）

log_pos

4

下一个事件的文件偏移位置

flags

2

标志位（如是否压缩、是否事务结束）

(3) 事件体（Event Body）

根据事件类型不同而变化，常见事件：

事件类型

含义

FORMAT_DESCRIPTION_EVENT

描述binlog格式版本

QUERY_EVENT

记录SQL语句（DDL或非事务语句）

TABLE_MAP_EVENT

在ROW模式下，映射表ID与表名/列结构

WRITE_ROWS_EVENT / UPDATE_ROWS_EVENT / DELETE_ROWS_EVENT

行级别变更数据

XID_EVENT

标记事务提交

ROTATE_EVENT

指示binlog文件切换

🧮 三、Row-based事件结构（重点）

以 WRITE_ROWS_EVENT 为例（插入操作）：

+------------------------+
| Event Header (19B)     |
+------------------------+
| Table ID (6B)          |
| Flags (2B)             |
| Columns Count (lenenc) |
| Columns Bitmap (n bytes) |
| Rows Data (variable)   |
+------------------------+

Rows Data中会包含：

  • 每一行的列值；
  • NULL位图；
  • 变长字段的长度；
  • 数据类型对应的原始值（INT, VARCHAR, DATETIME等）；

⚙️ 四、binlog解析还原SQL的步骤

要想“还原”原始SQL，有两种思路：

🧩 方案一：使用MySQL自带工具 mysqlbinlog

最简单方式，无需手写解析器。

基本命令
mysqlbinlog --base64-output=DECODE-ROWS -vv /var/lib/mysql/mysql-bin.000001
说明
  • --base64-output=DECODE-ROWS：解码行事件。
  • -vv：打印详细信息。
  • 输出会包含：
### INSERT INTO `test`.`user`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Alice' /* VARCHAR(10) meta=10 nullable=1 is_null=0 */

即重构出的SQL内容。

可选：导出可执行SQL
mysqlbinlog --database=mydb --verbose --base64-output=DECODE-ROWS mysql-bin.000001 > binlog.sql

🧩 方案二：编写自定义解析程序

如果你需要更灵活的控制（如过滤特定表、生成审计日志），可使用解析库：

常用开源库

语言

库名

说明

Python

python-mysql-replication

能实时解析binlog流

Go

go-mysql

阿里维护，生产可用

Java

mysql-binlog-connector-java

用于Canal等项目

C++

MySQL官方 libbinlogevents

内部库

Python示例
from pymysqlreplication import BinLogStreamReader
from pymysqlreplication.row_event import WriteRowsEvent, UpdateRowsEvent, DeleteRowsEvent

stream = BinLogStreamReader(
    connection_settings={
        "host": "127.0.0.1",
        "port": 3306,
        "user": "repl",
        "passwd": "replpass"
    },
    server_id=100,
    blocking=True,
    resume_stream=True,
    only_events=[WriteRowsEvent, UpdateRowsEvent, DeleteRowsEvent]
)

for binlogevent in stream:
    for row in binlogevent.rows:
        if isinstance(binlogevent, WriteRowsEvent):
            print(f"INSERT INTO {binlogevent.table} VALUES {row['values']}")
        elif isinstance(binlogevent, UpdateRowsEvent):
            print(f"UPDATE {binlogevent.table} SET {row['after_values']} WHERE {row['before_values']}")
        elif isinstance(binlogevent, DeleteRowsEvent):
            print(f"DELETE FROM {binlogevent.table} WHERE {row['values']}")

🧰 五、还原SQL的过程总结

  1. 定位事件：从binlog文件中读取event头；
  2. 解析事件体：根据type_code读取对应格式；
  3. TABLE_MAP_EVENT：拿到表结构、字段类型；
  4. ROW_EVENT：提取具体列值；
  5. 拼接SQL：构造INSERT/UPDATE/DELETE语句；
  6. 输出或重放：用于审计或恢复。

✅ 总结

内容

说明

binlog存储

一系列事务事件（Event）

格式

STATEMENT / ROW / MIXED

数据结构

文件头 + 事件头 + 事件体

工具

mysqlbinlog 或第三方库解析

还原SQL

解码TABLE_MAP + ROW_EVENT即可