MySQLNotes

第 21 章：日志提交与两阶段提交

zjc 于 2026-01-21 发布

这是《MySQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 InnoDB Redo 和 Server 层 Binlog 是两份不同日志。如果一份成功、一份失败，主库和从库、恢复后的主库都可能不一致。内部两阶段提交就是为了解决这个问题。

21.1 为什么需要两阶段提交

假设事务提交时直接顺序执行，没有协调：

崩溃场景一

1. Redo 已持久化；
2. Binlog 未写入；
3. 实例崩溃；

恢复后主库有该事务，Binlog 和从库没有，主从不一致。

崩溃场景二

1. Binlog 已持久化；
2. InnoDB Redo 未持久化；
3. 实例崩溃；

恢复后主库没有该事务，从库重放了 Binlog，主从不一致。

两阶段提交用状态和恢复规则保证 Redo 与 Binlog 对同一事务的结论一致。

21.2 简化提交流程

一个开启 Binlog 的 InnoDB 事务提交可以概括为：

1. InnoDB Redo 写入 prepare 状态；
2. 持久化 prepare Redo；
3. 写 Binlog 并 fsync；
4. InnoDB 写 commit Redo 并持久化；
5. 清理事务状态和锁；

不同版本实现细节有差异，但恢复规则稳定：

prepare 事务：
  Binlog 中存在该事务 -> 提交；
  Binlog 中不存在该事务 -> 回滚；

这样崩溃后主库结论与 Binlog 保持一致，从库也能得到相同结果。

21.3 组提交 Group Commit

多个事务同时提交时，可以合并刷盘：

Leader 阶段：收集事务；
Flush 阶段：写 Binlog / Redo；
Sync 阶段：一次 fsync；
Commit 阶段：各事务完成提交；

收益：

  1. 多个事务共享一次 fsync；
  2. 提高写吞吐；
  3. 降低每次提交延迟；
  4. 延迟越高，合并效果越明显；
  5. 保持每个事务持久性语义。

相关参数：

SHOW VARIABLES LIKE 'binlog_group_commit_sync_delay';
SHOW VARIABLES LIKE 'binlog_group_commit_sync_no_delay_count';
SHOW VARIABLES LIKE 'sync_binlog';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

binlog_group_commit_sync_delay 会主动等待一小段时间换取更多合并，可能增加单事务延迟，必须压测。

21.4 双 1 配置

生产核心库常见配置：

sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

含义：

  1. 每次提交 fsync Binlog；
  2. 每次提交 fsync InnoDB Redo；
  3. MySQL 崩溃或操作系统崩溃后已提交事务不丢。

代价：

  1. 每次提交至少触发关键刷盘；
  2. 对磁盘 fsync 能力敏感；
  3. 低延迟磁盘上收益明显；
  4. 高并发下依赖组提交摊薄成本。

不建议直接关闭双 1 追求压测数字。若业务允许丢失少量事务，应明确 RPO，并只用于非核心实例。

21.5 Binlog 格式

查看：

SHOW VARIABLES LIKE 'binlog_format';

STATEMENT

记录原始 SQL：

UPDATE orders SET status='PAID' WHERE id=1;

优点：

  1. 日志小；
  2. 可读性较好；
  3. 写放大较低。

风险：

  1. NOW()、UUID() 等不确定性结果；
  2. LIMIT 无稳定排序时结果不确定；
  3. 依赖函数和触发器；
  4. 复制可能不一致。

ROW

记录行前后镜像：

before: id=1, status=CREATED
after:  id=1, status=PAID

优点：

  1. 复制一致性最好；
  2. 对 SQL 语义依赖少；
  3. 支持更安全的并发复制；
  4. 适合 CDC。

缺点：

  1. 大批量修改日志量大；
  2. 需要控制 binlog_row_image；
  3. 解析需要解析工具；
  4. 全镜像会放大存储和网络。

查看行镜像：

SHOW VARIABLES LIKE 'binlog_row_image';

推荐在线业务默认 ROW + FULL 或按审计需求评估 MINIMAL。

21.6 Binlog 事件

查看日志：

SHOW BINARY LOGS;
SHOW MASTER STATUS;
SHOW BINLOG EVENTS LIMIT 20;

较新的 MySQL 8.x 版本推荐使用更清晰的状态命令；是否可用以当前版本文档为准：

SHOW BINARY LOG STATUS;

常见事件：

事件 说明
Format_desc 文件格式描述
Query STATEMENT SQL 或事务元信息
Table_map ROW 格式表映射
Write_rows 插入行
Update_rows 更新行
Delete_rows 删除行
Xid 事务提交
GTID GTID 事务标识

使用 mysqlbinlog：

mysqlbinlog --no-defaults \
  --base64-output=decode-rows \
  -vv mysql-bin.000001 \
  > decoded.sql

21.7 GTID

开启 GTID 后，每个事务拥有全局唯一标识：

3E11FA47-71CA-11E1-9E33-C80AA9429562:1-100

优点：

  1. 事务全局识别；
  2. 自动定位复制位点；
  3. 主从切换更简单；
  4. 防止重复重放同一事务；
  5. 故障排查更清晰。

查看：

SHOW VARIABLES LIKE 'gtid_mode';
SHOW VARIABLES LIKE 'enforce_gtid_consistency';
SELECT @@GLOBAL.gtid_executed;

要求：

  1. 主从都正确配置；
  2. 禁止不安全事务；
  3. 升级需分步；
  4. 从库写入要严格控制；
  5. 运维工具要兼容 GTID。

21.8 半同步与日志可靠性

异步复制：

主库提交后不等待从库；

风险：主库故障时未接收 Binlog 的事务丢失。

半同步复制：

主库提交事务后，至少等待一个从库确认收到 Binlog；

查看：

SHOW VARIABLES LIKE 'rpl_semi_sync_master_enabled';
SHOW STATUS LIKE 'Rpl_semi_sync_master_status';
SHOW STATUS LIKE 'Rpl_semi_sync_master_no_tx';

半同步保证“收到”，不等于“已经应用并对外可读”。它降低丢失窗口，但不是零 RPO 的完整方案。

21.9 查看提交相关状态

查看日志状态：

SHOW GLOBAL STATUS LIKE 'Binlog%';
SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';
SHOW GLOBAL STATUS LIKE 'Innodb_log_write_requests';

重点指标：

指标 含义
Binlog_cache_disk_use Binlog 缓存落盘次数
Binlog_cache_use 事务使用 Binlog 缓存次数
Innodb_log_waits Redo 空间或刷盘等待
Innodb_os_log_written Redo 写入量

如果 Binlog_cache_disk_use 高，说明大事务过多：

SHOW VARIABLES LIKE 'binlog_cache_size';
SHOW VARIABLES LIKE 'max_binlog_cache_size';

优先拆分事务，而不是盲目加大缓存。

21.10 大事务的问题

大事务会放大：

  1. Binlog 事件大小；
  2. Binlog 缓存落盘；
  3. Redo 写入；
  4. 锁持有时间；
  5. Undo 版本；
  6. 主从延迟；
  7. 回滚时间。

查看大事务：

SELECT
  trx_id,
  trx_started,
  trx_rows_modified,
  trx_rows_locked
FROM information_schema.INNODB_TRX
ORDER BY trx_rows_modified DESC;

治理：

  1. 按主键分批；
  2. 每批单独事务；
  3. 控制批大小；
  4. 避免一条 SQL 修改百万行；
  5. 监控单事务 Binlog 大小；
  6. 后台任务限速。

21.11 故障恢复一致性案例

崩溃时事务处于 prepare：

恢复流程：
1. 扫描 Redo；
2. 找到 prepare 事务；
3. 检查 Binlog 是否包含该 XID / GTID；
4. 包含 -> 提交；
5. 不包含 -> 回滚；

因此，不要手工删除 Binlog 来“省空间”而不保留完整恢复链路。清理策略必须与备份、恢复和主从切换方案一致。

本章小结

内部两阶段提交让 InnoDB Redo 和 Binlog 在崩溃后保持一致。核心主库应默认使用双 1 配置，通过组提交摊薄 fsync 成本。Binlog 推荐 ROW 格式，GTID 简化复制和切换。大事务会放大日志、锁、Undo 和主从延迟，应始终拆分和限速。

思考题

  1. 如果只有 Redo 成功而 Binlog 未写，会带来什么问题？
  2. prepare 事务在崩溃恢复时如何决定提交或回滚？
  3. 双 1 配置的代价和收益是什么？
  4. STATEMENT 和 ROW 格式各适合什么场景？
  5. 为什么大事务会显著增加 Binlog cache disk use？