MySQL binlog的row格式记录数据变更的具体内容

MySQL 的二进制日志（binlog）记录了数据库的所有变更。当 binlog 格式设置为 ROW 模式时，它不再记录执行的 SQL 语句文本，而是直接记录每一行数据的实际变化。这意味着日志会详细存储数据被修改前后的影像。

1. 准备测试环境

为了直观查看 binlog 记录的内容，需要先搭建一个简单的测试环境并配置相关参数。

打开 MySQL 配置文件（通常是 my.cnf 或 my.ini），确认或添加以下配置：

[mysqld]
log_bin=mysql-bin
binlog_format=ROW
binlog_row_image=FULL
server_id=1

重启 MySQL 服务使配置生效。binlog_row_image=FULL 是关键配置，它指示 MySQL 在记录 UPDATE 操作时，同时记录修改前的所有列和修改后的所有列。

登录 MySQL 数据库：

mysql -u root -p

创建一个测试数据库和表：

CREATE DATABASE test_binlog;
USE test_binlog;

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    age INT,
    balance DECIMAL(10, 2)
);

2. 执行数据变更操作

在这个阶段，执行典型的增删改操作，以便观察 binlog 如何记录这些变化。

插入一条新数据：

INSERT INTO users (username, age, balance) VALUES ('Alice', 25, 1000.00);

更新这条数据：

UPDATE users SET age = 26, balance = 1200.00 WHERE username = 'Alice';

删除这条数据：

DELETE FROM users WHERE username = 'Alice';

3. 解析 binlog 日志文件

直接查看 binlog 原始文件看到的是乱码，必须使用官方提供的 mysqlbinlog 工具进行解析。

查找当前的 binlog 文件名，在 MySQL 命令行中执行：

SHOW MASTER STATUS;

假设文件名为 mysql-bin.000001。退出 MySQL 命令行，打开系统终端，运行以下命令：

mysqlbinlog -vvv --base64-output=DECODE-ROWS /var/lib/mysql/mysql-bin.000001

• -vvv：表示 verbose 模式，用于展示详细的行数据变更。
• --base64-output=DECODE-ROWS：表示将 base64 编码的行事件解码为可读的 SQL 形式。

4. 分析不同操作的记录内容

通过查看解析后的日志，可以看到 ROW 格式下不同操作类型的具体记录结构。

4.1 INSERT 操作记录

INSERT 操作只记录变更后的数据，即新插入的行。

日志中会出现 ### INSERT INTO 字段，随后是 ### SET 部分，列出了该行所有列的值：

### INSERT INTO `test_binlog`.`users`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Alice' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
###   @3=25 /* INT meta=0 nullable=1 is_null=0 */
###   @4=1000.00 /* DECIMAL(10,2) meta=2050 nullable=1 is_null=0 */

@1, @2 代表列的索引位置（对应表结构中的顺序）。

4.2 DELETE 操作记录

DELETE 操作只记录变更前的数据，即被删除的行原本的内容。

日志中会出现 ### DELETE FROM 字段，随后是 ### WHERE 部分，标明了被删除行的具体值：

### DELETE FROM `test_binlog`.`users`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Alice' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
###   @3=26 /* INT meta=0 nullable=1 is_null=0 */
###   @4=1200.00 /* DECIMAL(10,2) meta=2050 nullable=1 is_null=0 */

4.3 UPDATE 操作记录

UPDATE 操作最复杂，它会同时记录变更前和变更后的完整数据。

日志中首先出现 ### UPDATE，随后分为两个部分：

1. ### WHERE：记录修改前的旧值（用于定位唯一行）。
2. ### SET：记录修改后的新值。

### UPDATE `test_binlog`.`users`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Alice' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
###   @3=25 /* INT meta=0 nullable=1 is_null=0 */
###   @4=1000.00 /* DECIMAL(10,2) meta=2050 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Alice' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
###   @3=26 /* INT meta=0 nullable=1 is_null=0 */
###   @4=1200.00 /* DECIMAL(10,2) meta=2050 nullable=1 is_null=0 */

5. 对比总结

为了更清晰地理解 ROW 格式的记录逻辑，下表总结了三种操作的记录差异：

通过这种记录方式，ROW 格式确保了数据恢复和同步的精确性，避免了 STATEMENT 模式下可能存在的不确定性问题（如 NOW() 函数或自定义函数在不同时间执行结果不同）。