MySQL Binlog的样式

一、Binlog 的基本概念与记录内容

Binlog 是 MySQL 的二进制日志，以事件形式记录所有对数据库的修改操作，包括：

• DDL 操作：如 CREATE、ALTER、DROP 等表结构变更。
• DML 操作：如 INSERT、UPDATE、DELETE 等数据修改。
• 事务信息：事务的开始、提交、回滚状态及执行时间 。

不记录的内容：纯查询语句（如 SELECT、SHOW），因其不改变数据库状态 。

---

二、Binlog 的三种格式及其样式

1. STATEMENT 格式

• 记录方式：保存原始的 SQL 语句。

• 示例：

  UPDATE users SET name='Alice' WHERE id=1;
  

• 特点：

  • 优点：日志量小，性能高，适合批量操作 。
  • 缺点：依赖上下文（如时间函数 NOW()、UUID()），可能导致主从复制不一致 。
  • 适用场景：简单 SQL 操作且主从环境一致的情况 。

2. ROW 格式

• 记录方式：保存每行数据的具体变更（前镜像和后镜像）。

• 示例：

  ### 更新操作的前后数据
  UPDATE `test`.`users`
  SET@1=1 (id),@2='Bob' (原name),@3=25 (原age)
  WHERE@1=1 (id),@2='Alice' (新name),@3=25 (新age);
  

• 特点：

  • 优点：精确记录行变更，确保主从数据严格一致 。
  • 缺点：日志量大（如全表更新时），可能影响磁盘 I/O 。
  • 适用场景：需要强一致性的场景（如金融系统）或涉及非确定性函数的操作 。

3. MIXED 格式

• 记录方式：混合模式，根据 SQL 类型自动选择 STATEMENT 或 ROW。

  • 默认使用 STATEMENT，但遇到非确定性函数（如 RAND()、NOW()）时切换为 ROW 。

• 示例：

  -- 使用 NOW() 函数时，自动转为 ROW 格式
  INSERT INTO logs (message, created_at) VALUES ('test', NOW());
  

• 特点：

  • 优点：平衡性能与一致性，减少手动调整需求 。
  • 缺点：复杂场景下可能无法完全规避复制问题 。
  • 适用场景：通用场景，尤其适合动态 SQL 操作 。

---

三、Binlog 记录的具体示例

1. STATEMENT 格式的 Binlog 内容

# 时间戳和事件位置
# at 1234
#240312 10:00:00 server id 1  end_log_pos 1300
SET TIMESTAMP=1710223200;
UPDATE users SET balance=balance-100 WHERE id=1;

2. ROW 格式的 Binlog 内容

# 行变更详情（Base64 编码）
### UPDATE `test`.`users`
### WHERE
###   @1=1 (id)
###   @2=500 (原balance)
### SET
###   @2=400 (新balance)

3. MIXED 格式的 Binlog 内容

# 自动选择模式示例
# Statement 模式记录表结构变更
ALTER TABLE users ADD COLUMN email VARCHAR(255);# Row 模式记录含非确定性函数的操作
INSERT INTO orders (user_id, amount, created_at) VALUES (1, 100, NOW());

---

四、如何查看 Binlog 内容

1. 通过 MySQL 命令行工具

• 查看日志列表：

  SHOW BINARY LOGS;
  

• 查看指定日志事件：

  SHOW BINLOG EVENTS IN 'mysql-bin.000001' FROM 1234 LIMIT 10;
  

2. 使用 mysqlbinlog 工具

• 解析日志文件：

  mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000001
  

  • 参数说明：

• --base64-output=decode-rows：解码 ROW 格式的日志。

• -vv：显示详细变更信息 。

• 按时间过滤：

  mysqlbinlog --start-datetime="2025-03-12 10:00:00" mysql-bin.000001
  

---

五、格式选择建议

场景	推荐格式	理由
主从复制（强一致性需求）	ROW	避免函数或存储过程导致的主从不一致
批量数据操作	STATEMENT	减少日志量，提升性能
通用业务	MIXED	自动平衡性能与一致性，减少人工干预

---

六、关键总结

• Binlog 样式核心差异：
  • STATEMENT：记录 SQL 语句，简洁但依赖上下文。
  • ROW：记录行变更，精确但日志量大。
  • MIXED：动态选择，兼顾性能与一致性 。
• 查看工具：优先使用 mysqlbinlog 解码 ROW 格式，结合过滤参数提高可读性 。
• 实践建议：根据业务需求选择格式，ROW 适合强一致性场景，STATEMENT 适合批量操作，MIXED 作为折中方案 。