MySQL日志系统:Redo Log、Undo Log、Binlog本文从三种日志的实现原理出发,深入剖析WAL机制、两

概述

MySQL日志系统是数据库可靠性与一致性的基石。Redo Log保证crash-safe崩溃恢复,Undo Log支持事务回滚和MVCC多版本并发控制,Binlog实现主从复制和数据归档。本文从三种日志的实现原理出发,深入剖析WAL机制、两阶段提交协议,结合误删数据恢复、Undo表空间膨胀等生产案例,全面讲解日志系统的底层机制、配置优化与故障排查,帮助读者彻底理解MySQL日志的协同工作原理。

一、理论知识与核心概念

1.1 为什么MySQL需要多种日志?

MySQL作为一个ACID事务型数据库,需要在保证数据一致性的同时,提供高性能的读写能力。单一日志无法同时满足以下需求:

崩溃恢复: 系统crash后能恢复已提交的事务 → Redo Log
事务回滚: 执行ROLLBACK时能撤销修改 → Undo Log
主从复制: 从库能同步主库的数据变更 → Binlog
数据归档: 保留完整的操作历史用于审计 → Binlog

三种日志各司其职,互相配合,共同构建了MySQL的高可靠性架构。

1.2 三种日志的作用概述

Redo Log (重做日志)

定义: InnoDB引擎特有的物理日志,记录数据页的修改
核心作用: 保证crash-safe,即使系统崩溃也能恢复已提交事务
实现机制: WAL (Write-Ahead Logging),先写日志再刷盘
存储位置: ib_logfile0、ib_logfile1 (默认2个文件,循环写)

Undo Log (回滚日志)

定义: InnoDB引擎特有的逻辑日志,记录修改前的数据
核心作用: 支持事务回滚 + MVCC一致性读
实现机制: 通过回滚指针DB_ROLL_PTR形成版本链
存储位置: Undo表空间 (MySQL 5.7+独立表空间,5.7之前在ibdata1)

Binlog (归档日志)

定义: Server层的逻辑日志,记录所有DDL和DML语句
核心作用: 主从复制、数据恢复、操作审计
日志格式: Statement (SQL语句) / Row (行变化) / Mixed (混合)
存储位置: mysql-bin.000001、mysql-bin.000002... (追加写,不覆盖)

1.3 日志的重要性

数据可靠性的三大支柱:

Redo Log: 保证已提交事务不丢失 (持久性D)
Undo Log: 保证未提交事务可回滚 (原子性A)
Binlog: 保证主从数据一致,支持数据恢复

性能优化的关键:

WAL机制: 将随机写转化为顺序写,大幅提升性能
异步刷盘: 先写日志,后台线程异步刷脏页
Group Commit: 批量提交多个事务的日志,减少fsync调用

1.4 本文目标

本文将深入剖析三种日志的底层实现原理,重点讲解:

Redo Log的循环写机制和crash-safe原理
Undo Log在MVCC中的作用和清理机制
Binlog的三种格式对比和数据恢复实战
两阶段提交协议如何保证Redo Log和Binlog一致性
生产环境的配置优化和故障排查技巧

二、Redo Log重做日志详解

2.1 Redo Log是什么?

定义: Redo Log是InnoDB存储引擎特有的物理日志,记录了"对数据页的物理修改",而非SQL语句本身。

为什么需要Redo Log?

考虑一个场景:执行UPDATE account SET balance = 1000 WHERE id = 1;

❌ 没有Redo Log的问题:

1. 修改Buffer Pool中的数据页 (内存操作,很快)
2. 直接刷脏页到磁盘 (随机I/O,很慢)
3. 如果每次UPDATE都立即刷盘,性能极差
4. 如果不立即刷盘,系统crash后数据丢失

✅ 有Redo Log的解决方案:

1. 修改Buffer Pool中的数据页 (内存操作)
2. 将修改写入Redo Log (顺序I/O,很快)
3. 事务提交时,只需保证Redo Log刷盘即可
4. 脏页可以延后异步刷到磁盘
5. 系统crash后,通过Redo Log恢复已提交事务

核心优势: WAL (Write-Ahead Logging) 机制

先写日志,再写磁盘: 保证持久性的同时,将随机I/O转化为顺序I/O
性能提升: 顺序写性能远高于随机写 (几十倍甚至上百倍)
可靠性: 即使脏页未刷盘,也能通过Redo Log恢复数据

Redo Log vs Binlog对比

维度	Redo Log	Binlog
所属层级	InnoDB引擎层	MySQL Server层
日志类型	物理日志 (数据页修改)	逻辑日志 (SQL语句/行变化)
写入方式	循环写 (固定大小,覆盖)	追加写 (不覆盖,文件切换)
作用	crash-safe崩溃恢复	主从复制、数据归档
适用引擎	仅InnoDB	所有引擎

2.2 Redo Log的物理结构

日志文件

默认配置: 2个文件 (ib_logfile0、ib_logfile1)
文件大小: 默认48MB,生产环境建议设置为1GB

-- 查看Redo Log配置
SHOW VARIABLES LIKE 'innodb_log_file%';

+---------------------------+----------+
| Variable_name             | Value    |
+---------------------------+----------+
| innodb_log_file_size      | 50331648 | -- 48MB (50331648字节)
| innodb_log_files_in_group | 2        | -- 2个文件
+---------------------------+----------+

配置参数

[mysqld]
# Redo Log文件数量 (默认2,最大100)
innodb_log_files_in_group = 2

# 单个文件大小 (默认48MB,生产环境建议1GB)
innodb_log_file_size = 1G

# 文件存储位置 (默认为数据目录)
innodb_log_group_home_dir = /var/lib/mysql/

# Redo Log Buffer大小 (默认16MB)
innodb_log_buffer_size = 16M

Redo Log Buffer

作用: 内存缓冲区,先写Buffer,再批量刷盘
大小: 默认16MB,可通过innodb_log_buffer_size配置
优势: 减少磁盘I/O次数,提升性能

write pos和checkpoint

如上图所示,Redo Log采用循环写机制:

write pos: 当前写入位置,一边写一边后移,写到最后一个文件末尾就回到第一个文件开头
checkpoint: 当前擦除位置,擦除前必须保证对应的脏页已刷盘
空闲空间: write pos到checkpoint之间,可写入新日志
已写入区域: checkpoint到write pos之间,已持久化但未刷盘的日志

⚠️ 重要: 如果write pos追上checkpoint,表示Redo Log写满,必须停下来等待脏页刷盘,推进checkpoint,这会严重影响性能。

2.3 Redo Log的写入时机

innodb_flush_log_at_trx_commit参数

这是控制Redo Log刷盘策略的关键参数,有3个可选值:

参数值0:每秒刷盘

执行流程:
1. 事务提交时,只写Redo Log Buffer,不刷盘
2. 后台线程每秒调用一次fsync刷盘

优点: 性能最好 (不等待fsync)
缺点: MySQL宕机可能丢失1秒数据
风险: 高 (不推荐生产环境)

参数值1:每次提交刷盘 (默认值,推荐)

执行流程:
1. 事务提交时,写Redo Log Buffer
2. 调用write写入OS Page Cache
3. 立即调用fsync刷盘

优点: 数据最安全,绝对不会丢失已提交事务
缺点: 性能最差 (每次提交都fsync)
推荐: 生产环境强烈推荐

参数值2:每次提交写OS缓存,每秒刷盘

执行流程:
1. 事务提交时,写Redo Log Buffer
2. 调用write写入OS Page Cache
3. 后台线程每秒调用一次fsync刷盘

优点: 折中方案,性能较好
缺点: OS宕机可能丢失1秒数据
风险: 中 (数据库宕机不丢数据,OS宕机可能丢失)

三种设置对比

参数值	刷盘时机	MySQL宕机	OS宕机	性能	推荐度
0	每秒	丢失1秒	丢失1秒	最高	⭐⭐
1	每次提交	不丢失	不丢失	最低	⭐⭐⭐⭐⭐
2	每次提交(OS缓存),每秒刷盘	不丢失	丢失1秒	中等	⭐⭐⭐⭐

生产环境推荐配置

[mysqld]
# 推荐设置为1,保证数据绝对安全
innodb_flush_log_at_trx_commit = 1

# 配合sync_binlog=1,实现双1配置 (最高可靠性)
sync_binlog = 1

2.4 Redo Log如何保证crash-safe

数据修改完整流程

1. 执行器调用InnoDB接口: UPDATE account SET balance = 1000 WHERE id = 1;

2. InnoDB修改Buffer Pool中的数据页:
   - 找到id=1所在的数据页
   - 将balance从500修改为1000
   - 标记该数据页为脏页

3. 写入Undo Log:
   - 记录修改前的旧值: balance = 500
   - 用于事务回滚和MVCC

4. 写入Redo Log (prepare阶段):
   - 记录物理修改: 将X页Y偏移量的值改为1000
   - 状态标记为prepare
   - 写入Redo Log Buffer

5. 写入Binlog:
   - 记录逻辑SQL或行变化
   - 刷盘到Binlog文件

6. 修改Redo Log状态 (commit阶段):
   - 将Redo Log状态从prepare改为commit
   - 事务提交完成

7. 异步刷脏页:
   - 后台线程将脏页刷到磁盘
   - 更新checkpoint位置
   - Redo Log对应区域可以擦除重用

崩溃恢复流程

假设在上述流程的不同阶段发生crash:

情况1: Redo Log写入前crash

状态: Buffer Pool有脏页,Redo Log无记录
恢复: 无需恢复,事务未执行
结果: 数据保持原值 (balance = 500)

情况2: Redo Log写入后、Binlog写入前crash

状态: Redo Log有记录(prepare),Binlog无记录
恢复: 回滚该事务 (使用Undo Log)
结果: 数据恢复到原值 (balance = 500)
原因: Binlog无记录,从库不会执行,必须回滚保证主从一致

情况3: Binlog写入后、commit前crash

状态: Redo Log有记录(prepare),Binlog有记录
恢复: 自动提交该事务 (修改Redo Log状态为commit)
结果: 数据修改生效 (balance = 1000)
原因: Binlog有记录,从库会执行,必须提交保证主从一致

情况4: commit后crash

状态: Redo Log有记录(commit),Binlog有记录
恢复: 无需恢复,事务已成功提交
结果: 数据已持久化,即使脏页未刷盘,也可通过Redo Log恢复

为什么先写Redo Log再写数据页?

Redo Log: 顺序写,固定大小,性能高
数据页: 随机写,分散在磁盘各处,性能低

先写Redo Log:
1. 保证日志先持久化 (WAL机制)
2. 脏页可以延后刷盘,减少随机I/O
3. 系统crash后,通过Redo Log重放已提交事务
4. 性能提升巨大 (顺序写 vs 随机写)

三、Undo Log回滚日志详解

3.1 Undo Log是什么?

定义: Undo Log是InnoDB存储引擎特有的逻辑日志,记录了数据修改前的旧值,用于事务回滚和MVCC一致性读。

两大核心作用

1. 事务回滚

当执行ROLLBACK时,InnoDB通过Undo Log恢复数据到修改前的状态:

BEGIN;
UPDATE account SET balance = 1000 WHERE id = 1; -- 旧值500
-- 假设此时决定回滚
ROLLBACK;

回滚过程:

1. InnoDB读取Undo Log,找到id=1的旧值balance=500
2. 将数据恢复为旧值
3. 释放锁,撤销所有修改

2. MVCC一致性读

Undo Log通过版本链实现MVCC,保证事务的隔离性(详见第11篇):

-- 事务A (REPEATABLE READ隔离级别)
BEGIN;
SELECT balance FROM account WHERE id = 1; -- 读到1000

-- 事务B
UPDATE account SET balance = 500 WHERE id = 1;
COMMIT;

-- 事务A再次查询
SELECT balance FROM account WHERE id = 1; -- 仍然读到1000 (通过Undo Log版本链)

3.2 Undo Log的存储位置

MySQL 5.7之前

存储位置: 系统表空间 ibdata1
问题:
- ibdata1文件会不断膨胀
- 无法回收空间
- 影响性能

MySQL 5.7及以后 (推荐)

存储位置: 独立的Undo表空间
配置参数:

-- 查看Undo Log配置
SHOW VARIABLES LIKE 'innodb_undo%';

+-------------------------+----------------------+
| Variable_name           | Value                |
+-------------------------+----------------------+
| innodb_undo_directory   | ./                   | -- Undo文件存储路径
| innodb_undo_tablespaces | 3                    | -- Undo表空间数量
| innodb_undo_logs        | 128                  | -- 回滚段数量
| innodb_max_undo_log_size| 1073741824           | -- 最大1GB,超过自动truncate
+-------------------------+----------------------+

推荐配置

[mysqld]
# Undo表空间数量 (默认2,建议3,支持自动truncate轮转)
innodb_undo_tablespaces = 3

# Undo表空间存储路径 (建议独立磁盘)
innodb_undo_directory = /data/mysql/undo/

# 单个Undo表空间最大大小 (默认1GB,超过自动truncate)
innodb_max_undo_log_size = 1G

# 回滚段数量 (默认128,支持128*1024=131072个并发事务)
innodb_undo_logs = 128

Undo Segment (回滚段)

定义: 每个事务分配一个Undo Segment,每个Segment可包含多个Undo Page
MySQL 5.5: 只有1个回滚段,最多支持1024个并发事务
MySQL 5.6+: 最多128个回滚段,支持128 × 1024 = 131,072个并发事务

3.3 Undo Log的类型

Insert Undo Log

记录内容: INSERT操作的主键ID
作用: 事务回滚时删除插入的记录
清理时机: 事务提交后立即删除
特点: 占用空间小,无需支持MVCC

INSERT INTO account (id, name, balance) VALUES (1, 'Alice', 1000);

Undo Log记录:

类型: Insert Undo Log
主键: id = 1
操作: 记录主键,回滚时DELETE

Update Undo Log

记录内容: UPDATE、DELETE操作修改前的完整行数据
作用: 事务回滚 + MVCC一致性读
清理时机: 等到没有事务依赖该版本时才能删除
特点: 占用空间大,可能导致Undo表空间膨胀

UPDATE account SET balance = 1000 WHERE id = 1;

Undo Log记录:

类型: Update Undo Log
主键: id = 1
旧值: balance = 500, name = 'Alice', ...
DB_TRX_ID: 100
DB_ROLL_PTR: 指向更早的版本

3.4 Undo Log在MVCC中的作用

版本链构建

每次UPDATE都会生成一个Undo Log,通过DB_ROLL_PTR指针形成版本链(详见第11篇MVCC原理图):

当前版本 (内存):
id=1, balance=1000, DB_TRX_ID=300, DB_ROLL_PTR → 版本2

版本2 (Undo Log):
id=1, balance=800, DB_TRX_ID=200, DB_ROLL_PTR → 版本1

版本1 (Undo Log):
id=1, balance=500, DB_TRX_ID=100, DB_ROLL_PTR = NULL

一致性读流程

事务A (trx_id=150, REPEATABLE READ):
1. 第一次SELECT时生成Read View:
   m_ids = [200, 300]  (活跃事务)
   min_trx_id = 200
   max_trx_id = 400
   creator_trx_id = 150

2. 读取id=1的数据:
   - 当前版本 DB_TRX_ID=300 → 在m_ids中,不可见
   - 沿着DB_ROLL_PTR找到版本2,DB_TRX_ID=200 → 在m_ids中,不可见
   - 继续找到版本1,DB_TRX_ID=100 < min_trx_id → 可见!
   - 返回balance=500

3. 即使其他事务修改了数据,事务A仍读到balance=500 (可重复读)

3.5 Undo Log的清理

Purge线程

作用: 后台线程,负责清理不再需要的Undo Log
清理条件: 所有Read View的min_trx_id都大于该Undo Log的DB_TRX_ID
清理对象: 主要是Update Undo Log (Insert Undo Log提交后立即删除)

Undo表空间膨胀问题

原因分析

1. 长事务占用Undo Log,Purge线程无法清理
2. 大量UPDATE/DELETE操作,生成大量Update Undo Log
3. Undo表空间不断增长,无法回收

排查方法

-- 1. 查看当前活跃事务
SELECT * FROM information_schema.innodb_trx\G

*************************** 1. row ***************************
                    trx_id: 12345
                 trx_state: RUNNING
               trx_started: 2024-01-01 10:00:00  -- 已运行24小时!
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 100
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 1
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 100
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ

-- 2. 查看Undo表空间大小
SELECT
  FILE_NAME,
  TABLESPACE_NAME,
  ROUND(DATA_LENGTH/1024/1024/1024, 2) AS SIZE_GB
FROM information_schema.FILES
WHERE FILE_NAME LIKE '%undo%';

+-------------------+----------------+---------+
| FILE_NAME         | TABLESPACE_NAME| SIZE_GB |
+-------------------+----------------+---------+
| ./undo_001        | innodb_undo_001| 50.00   | -- 50GB,异常膨胀!
| ./undo_002        | innodb_undo_002| 48.00   |
+-------------------+----------------+---------+

-- 3. 查看Purge线程进度
SHOW ENGINE INNODB STATUS\G

-- 找到以下部分:
---TRANSACTION 12345, ACTIVE 86400 sec
-- 长事务运行了86400秒(24小时)!

解决方案

临时方案: KILL长事务

-- 找到长事务对应的线程ID
SELECT trx_mysql_thread_id FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 3600;

-- KILL该线程
KILL 100;

-- 等待Purge线程清理
-- 可以通过SHOW ENGINE INNODB STATUS查看Purge进度

永久方案: 配置自动truncate

[mysqld]
# 单个Undo表空间超过1GB自动truncate
innodb_max_undo_log_size = 1G

# 开启Undo表空间自动truncate (MySQL 5.7+默认开启)
innodb_undo_log_truncate = ON

# 设置多个Undo表空间,支持轮转truncate
innodb_undo_tablespaces = 3

监控指标

-- 监控Undo表空间大小
SELECT
  TABLESPACE_NAME,
  ROUND(SUM(DATA_LENGTH)/1024/1024/1024, 2) AS SIZE_GB
FROM information_schema.FILES
WHERE TABLESPACE_NAME LIKE 'innodb_undo%'
GROUP BY TABLESPACE_NAME;

-- 监控长事务
SELECT
  trx_id,
  trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS running_seconds,
  trx_mysql_thread_id,
  trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 10;

告警阈值

Undo表空间 > 10GB: 警告
Undo表空间 > 50GB: 严重告警
长事务 (>10秒): 警告
长事务 (>60秒): 严重告警

四、Binlog归档日志详解

4.1 Binlog是什么?

定义: Binlog (Binary Log) 是MySQL Server层的逻辑日志,记录了所有修改数据的DDL和DML语句。

三大核心作用

1. 主从复制

主库 (Master):
1. 执行SQL,修改数据
2. 写入Binlog

从库 (Slave):
1. IO线程拉取Master的Binlog
2. SQL线程重放Binlog中的SQL
3. 实现数据同步

2. 数据恢复

场景: 运维误删除了1000条订单数据
恢复步骤:
1. 恢复最近的全量备份 (如昨天凌晨的备份)
2. 使用mysqlbinlog工具恢复备份后到误删前的Binlog
3. 跳过误删操作
4. 继续恢复误删后的Binlog
5. 数据完整恢复

3. 数据归档与审计

- 保留完整的操作历史
- 审计谁在什么时间做了什么操作
- 离线数据分析 (如用户行为分析)

Binlog vs Redo Log对比

维度	Binlog	Redo Log
所属层级	Server层,所有引擎通用	InnoDB引擎层
日志类型	逻辑日志 (SQL/行变化)	物理日志 (数据页修改)
写入方式	追加写,不覆盖	循环写,覆盖旧数据
文件大小	可配置,默认1GB/文件	固定大小,默认2×48MB
主要作用	主从复制、数据恢复	crash-safe崩溃恢复
是否可关闭	可关闭 (不推荐)	不可关闭

4.2 Binlog的三种格式

Binlog格式对比示意图

Statement格式 (不推荐)

记录内容: SQL语句本身

-- 原始SQL
UPDATE users SET update_time = NOW() WHERE name = 'Alice';

-- Binlog内容 (Statement格式)
SET TIMESTAMP=1674833544;
UPDATE users SET update_time = NOW() WHERE name = 'Alice';

优点:

✅ 日志量小,节约磁盘和网络带宽
✅ 适合批量操作 (一条UPDATE影响1万行,只记录1条SQL)

缺点:

❌ 不确定函数导致主从不一致: NOW(), UUID(), RAND()
❌ 触发器、存储过程可能复制错误
❌ 无法准确恢复数据 (时间戳不同,结果不同)

Row格式 (推荐) ⭐

记录内容: 每一行数据的实际变化

-- 原始SQL
UPDATE users SET update_time = NOW() WHERE name = 'Alice';

-- Binlog内容 (Row格式,假设匹配3行)
### UPDATE `test`.`users`
### WHERE
###   @1=1001 /* id */
###   @2='Alice' /* name */
###   @3='2024-01-01 10:00:00' /* update_time (旧值) */
### SET
###   @3='2024-01-01 11:30:25' /* update_time (新值) */

### UPDATE `test`.`users`
### WHERE
###   @1=1002 /* id */
###   @2='Alice' /* name */
###   @3='2024-01-01 09:00:00' /* update_time (旧值) */
### SET
###   @3='2024-01-01 11:30:25' /* update_time (新值) */

### UPDATE `test`.`users`
### WHERE
###   @1=1003 /* id */
###   @2='Alice' /* name */
###   @3='2024-01-01 08:00:00' /* update_time (旧值) */
### SET
###   @3='2024-01-01 11:30:25' /* update_time (新值) */

优点:

✅ 保证主从数据一致性 (记录实际值,不受函数影响)
✅ 可以准确恢复数据
✅ 支持所有场景 (触发器、存储过程、函数)

缺点:

❌ 日志量大 (1条UPDATE影响1万行,记录1万条)
❌ 占用更多磁盘空间和网络带宽

Mixed格式 (混合模式)

记录内容: 根据SQL类型自动选择Statement或Row

选择规则:
- 一般情况: 使用Statement格式 (节省空间)
- 特殊情况: 使用Row格式 (保证一致性)
  - 包含不确定函数: NOW(), UUID(), RAND(), SYSDATE()
  - 使用触发器、存储过程、自定义函数
  - 使用AUTO_INCREMENT、LOAD DATA等语句

优点: 兼顾性能与一致性,自动选择最优格式

三种格式对比

维度	Statement	Row (推荐)	Mixed
日志大小	小 ✅	大 ❌	中等
主从一致性	可能不一致 ❌	完全一致 ✅	一致 ✅
数据恢复	不准确 ❌	准确 ✅	准确 ✅
推荐度	⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐⭐

配置方式

-- 查看当前Binlog格式
SHOW VARIABLES LIKE 'binlog_format';

-- 设置为Row格式 (推荐)
SET GLOBAL binlog_format = 'ROW';

[mysqld]
# 推荐配置
binlog_format = ROW

4.3 Binlog的写入机制

Binlog Cache

执行流程:
1. 事务执行过程中,日志写入Binlog Cache (线程私有内存)
2. 事务提交时,Binlog Cache刷入Binlog文件
3. 配置参数: binlog_cache_size (默认32KB)

sync_binlog参数

控制Binlog刷盘策略,与innodb_flush_log_at_trx_commit配合使用:

参数值0: 不主动刷盘

执行流程:
1. 事务提交时,写入OS Page Cache
2. 由操作系统决定何时fsync刷盘

优点: 性能最好
缺点: OS宕机可能丢失Binlog
风险: 高 (不推荐)

参数值1: 每次提交刷盘 (推荐)

执行流程:
1. 事务提交时,写入OS Page Cache
2. 立即调用fsync刷盘

优点: 数据最安全,绝对不会丢失Binlog
缺点: 性能最差
推荐: 生产环境强烈推荐

参数值N: 每N次提交刷盘

执行流程:
1. 事务提交时,写入OS Page Cache
2. 累积N次提交后,调用fsync刷盘

优点: 折中方案
缺点: 宕机可能丢失N个事务的Binlog

双1配置 (最高可靠性)

[mysqld]
# Redo Log每次提交刷盘
innodb_flush_log_at_trx_commit = 1

# Binlog每次提交刷盘
sync_binlog = 1

# 两者结合,保证已提交事务绝对不丢失

性能与安全性权衡

配置	innodb_flush_log_at_trx_commit	sync_binlog	数据安全性	性能
最安全	1	1	最高 ✅	最低
推荐	1	1	最高 ✅	可接受
折中	2	0	中等	高
最快	0	0	最低 ❌	最高

4.4 Binlog文件管理

文件命名

mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
...
mysql-bin.index  (索引文件,记录所有Binlog文件)

文件切换

Binlog文件在以下情况下会切换到新文件:

1. 文件大小达到max_binlog_size (默认1GB)
2. 执行FLUSH LOGS命令
3. MySQL重启

配置参数

-- 查看Binlog配置
SHOW VARIABLES LIKE '%binlog%';

-- 关键参数
log_bin = ON                        -- 是否开启Binlog
binlog_format = ROW                 -- 日志格式
max_binlog_size = 1G                -- 单个文件最大大小
binlog_expire_logs_seconds = 604800 -- 过期时间7天 (MySQL 8.0+)
expire_logs_days = 7                -- 过期时间7天 (MySQL 5.7)

文件过期与清理

自动过期 (推荐)

[mysqld]
# MySQL 8.0+
binlog_expire_logs_seconds = 604800  # 7天 (7*24*3600秒)

# MySQL 5.7
expire_logs_days = 7

手动删除

-- 删除指定文件之前的所有Binlog
PURGE BINARY LOGS TO 'mysql-bin.000010';

-- 删除指定日期之前的所有Binlog
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';

-- 删除所有Binlog (危险!谨慎使用)
RESET MASTER;

查看Binlog文件

-- 查看所有Binlog文件
SHOW BINARY LOGS;

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177       |
| mysql-bin.000002 | 1073741886|
| mysql-bin.000003 | 5432109   |
+------------------+-----------+

-- 查看当前正在写入的Binlog文件
SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 5432109  |              |                  |
+------------------+----------+--------------+------------------+

4.5 Binlog解析与数据恢复

mysqlbinlog工具

# 查看Binlog内容
mysqlbinlog /var/lib/mysql/mysql-bin.000001

# 查看Row格式Binlog (需要解码)
mysqlbinlog --no-defaults -v --base64-output=decode-rows /var/lib/mysql/mysql-bin.000001

# 按时间范围查看
mysqlbinlog --start-datetime="2024-01-01 10:00:00" \
            --stop-datetime="2024-01-01 12:00:00" \
            /var/lib/mysql/mysql-bin.000001

# 按位置范围查看
mysqlbinlog --start-position=1000 \
            --stop-position=2000 \
            /var/lib/mysql/mysql-bin.000001

完整数据恢复案例

场景: 运维误删除了1000条订单数据

-- 1. 误操作时间: 2024-01-15 14:30:00
-- 2. 最近一次全量备份: 2024-01-15 02:00:00 (每天凌晨2点备份)
-- 3. 目标: 恢复到误删除前的状态

恢复步骤

步骤1: 停止应用,防止新数据写入

# 停止应用服务器
systemctl stop app-service

步骤2: 恢复全量备份

# 恢复凌晨2点的全量备份
mysql -u root -p < /backup/full_backup_2024-01-15_02-00-00.sql

步骤3: 找到误删操作的Binlog位置

# 查看2024-01-15 14:30前后的Binlog
mysqlbinlog --no-defaults -v --base64-output=decode-rows \
  --start-datetime="2024-01-15 14:25:00" \
  --stop-datetime="2024-01-15 14:35:00" \
  /var/lib/mysql/mysql-bin.000105 | less

# 找到以下内容:
# at 5432109
#240115 14:30:15 server id 1  end_log_pos 5432200
DELETE FROM orders WHERE create_time < '2024-01-01';  # 误删操作!

# 记录位置:
# 误删前位置: 5432109
# 误删后位置: 5432200

步骤4: 恢复备份后到误删前的数据

# 恢复2024-01-15 02:00:00 到 14:30:15之间的数据
mysqlbinlog --no-defaults \
  --start-datetime="2024-01-15 02:00:00" \
  --stop-position=5432109 \
  /var/lib/mysql/mysql-bin.000105 | mysql -u root -p

步骤5: 跳过误删操作,恢复误删后的数据

# 恢复14:30:15之后的数据 (跳过误删操作)
mysqlbinlog --no-defaults \
  --start-position=5432200 \
  /var/lib/mysql/mysql-bin.000105 \
  /var/lib/mysql/mysql-bin.000106 | mysql -u root -p

步骤6: 验证数据完整性

-- 检查订单总数
SELECT COUNT(*) FROM orders;

-- 检查最新订单时间
SELECT MAX(create_time) FROM orders;

-- 抽查部分订单数据
SELECT * FROM orders WHERE id IN (100, 200, 300);

步骤7: 恢复应用服务

systemctl start app-service

恢复成功! 数据完整恢复,业务恢复正常。

预防措施

1. ✅ 开启Binlog (log_bin=ON)
2. ✅ 使用Row格式 (binlog_format=ROW)
3. ✅ 定期全量备份 (每天凌晨)
4. ✅ Binlog保留足够长时间 (至少7天)
5. ✅ 敏感操作前先备份
6. ✅ 使用BEGIN/ROLLBACK测试DELETE语句
7. ✅ 生产环境禁用DELETE权限,改用逻辑删除

五、两阶段提交协议

5.1 为什么需要两阶段提交?

问题背景

MySQL有两套独立的日志系统:

Redo Log: InnoDB引擎层,物理日志,用于crash-safe
Binlog: Server层,逻辑日志,用于主从复制和数据恢复

如果两者不同步,会导致严重问题:

❌ 问题1: 先写Redo Log,再写Binlog,中间crash

执行: UPDATE account SET balance = 1000 WHERE id = 1;

时间线:
1. InnoDB写入Redo Log,标记状态为commit
2. 【系统crash!】
3. Binlog未写入

恢复后:
- 主库: Redo Log有记录,恢复后balance=1000 ✅
- 从库: Binlog无记录,balance=500 ❌

问题: 主从数据不一致!

❌ 问题2: 先写Binlog,再写Redo Log,中间crash

时间线:
1. Server层写入Binlog
2. 【系统crash!】
3. InnoDB未写入Redo Log

恢复后:
- 主库: Redo Log无记录,balance=500 ❌
- 从库: Binlog有记录,重放后balance=1000 ✅

问题: 主从数据不一致!

✅ 解决方案: 两阶段提交 (Two-Phase Commit, 2PC)

通过将事务提交分为两个阶段,保证Redo Log和Binlog的一致性。

5.2 两阶段提交流程

完整流程

阶段1: InnoDB Prepare
1. 修改Buffer Pool中的数据页
2. 写入Undo Log (保存旧值)
3. 写入Redo Log,状态标记为prepare
4. 事务暂不提交

阶段2: 写Binlog
1. 将事务日志写入Binlog Cache
2. Binlog Cache刷盘到Binlog文件
3. Binlog持久化完成

阶段3: InnoDB Commit
1. 修改Redo Log状态: prepare → commit
2. 事务提交完成,释放锁

示例SQL执行流程

UPDATE account SET balance = 1000 WHERE id = 1;

T1: 执行器调用InnoDB接口
T2: InnoDB修改Buffer Pool: balance 500 → 1000
T3: 写入Undo Log: 记录旧值balance=500
T4: 写入Redo Log: 状态=prepare ← 【阶段1完成】
T5: 写入Binlog Cache
T6: Binlog Cache刷盘 ← 【阶段2完成】
T7: 修改Redo Log状态: prepare → commit ← 【阶段3完成】
T8: 事务提交,释放锁

5.3 两阶段提交如何保证一致性

崩溃恢复分析

场景1: Prepare前crash

状态: Buffer Pool有修改,Redo Log无记录,Binlog无记录
恢复策略: 无需恢复
结果: balance=500 (原值)
一致性: ✅ Redo Log无,Binlog无,主从一致

场景2: Prepare后、写Binlog前crash

状态: Redo Log有记录(prepare),Binlog无记录
恢复策略: 回滚该事务 (使用Undo Log)
结果: balance=500 (回滚到原值)
原因: Binlog无记录 → 从库不会执行 → 必须回滚保证主从一致
一致性: ✅ 主库回滚,从库无修改,主从一致

场景3: 写Binlog后、Commit前crash ⭐

状态: Redo Log有记录(prepare),Binlog有记录
恢复策略: 自动提交该事务 (修改Redo Log状态为commit)
结果: balance=1000 (提交生效)
原因: Binlog有记录 → 从库会执行 → 必须提交保证主从一致
一致性: ✅ 主库提交,从库执行,主从一致

场景4: Commit后crash

状态: Redo Log有记录(commit),Binlog有记录
恢复策略: 无需恢复,事务已成功提交
结果: balance=1000
一致性: ✅ 主库已提交,从库已执行,主从一致

关键判断逻辑

MySQL恢复时的判断流程:

1. 扫描Redo Log,找到状态为prepare的事务
2. 检查Binlog中是否有对应的事务记录:
   - Binlog有记录 → 自动提交 (修改Redo Log状态为commit)
   - Binlog无记录 → 回滚 (使用Undo Log)
3. 保证主从数据一致性

核心原理

Binlog是提交的判断依据:
- Binlog有记录 = 从库会执行 → 主库必须提交
- Binlog无记录 = 从库不会执行 → 主库必须回滚

Redo Log的prepare状态:
- 作为事务提交的"临时标记"
- 在写Binlog前不会真正提交
- 为两阶段提交提供回滚能力

5.4 两阶段提交的性能优化

Group Commit (组提交)

问题: 每次事务提交都调用fsync,性能差

解决: 将多个事务的Binlog一起刷盘,减少fsync调用次数

原理

事务A: 写Binlog Cache,等待fsync
事务B: 写Binlog Cache,等待fsync
事务C: 写Binlog Cache,等待fsync

Group Commit:
1. 收集A、B、C的Binlog Cache
2. 一次性fsync刷盘
3. 同时完成A、B、C的提交

效果: 3次fsync → 1次fsync,性能提升3倍

配置参数

-- 查看Group Commit配置
SHOW VARIABLES LIKE 'binlog_group_commit%';

-- 关键参数
binlog_group_commit_sync_delay = 0       -- 延迟时间(微秒),0表示不延迟
binlog_group_commit_sync_no_delay_count = 0  -- 最大事务数,0表示不限制

优化配置

[mysqld]
# 延迟10毫秒,或累积100个事务
binlog_group_commit_sync_delay = 10000           # 10毫秒 (10000微秒)
binlog_group_commit_sync_no_delay_count = 100    # 最多100个事务

性能对比

配置	QPS	延迟	说明
无Group Commit	1000	1ms	每次提交都fsync
Group Commit (delay=0)	3000	1ms	自动攒批,无延迟
Group Commit (delay=10ms)	5000	10ms	强制延迟10ms,攒更多事务

推荐配置

对延迟不敏感的场景:
- binlog_group_commit_sync_delay = 10000 (10ms)
- binlog_group_commit_sync_no_delay_count = 100

对延迟敏感的场景:
- binlog_group_commit_sync_delay = 0
- binlog_group_commit_sync_no_delay_count = 0

六、实战场景应用

6.1 场景1: 通过Binlog恢复误删数据

业务背景: 运维误执行DELETE,删除了1000条订单数据

完整恢复流程 (详见4.5节)

6.2 场景2: 主从复制架构

基于Binlog的主从复制原理

主库 (Master):
1. 执行SQL,修改数据
2. 写入Binlog
3. Dump线程读取Binlog,发送给从库

从库 (Slave):
1. IO线程接收Master的Binlog,写入Relay Log
2. SQL线程读取Relay Log,重放SQL
3. 完成数据同步

主从延迟监控

-- 从库执行,查看主从延迟
SHOW SLAVE STATUS\G

Seconds_Behind_Master: 0  -- 主从延迟秒数,0表示无延迟

主从延迟优化

1. 使用Row格式Binlog (减少SQL复杂度)
2. 从库使用SSD (加快重放速度)
3. 并行复制 (MySQL 5.7+支持)
4. 减少主库大事务 (拆分为小事务)

详见第13篇《MySQL主从复制与高可用架构》

七、生产案例与故障排查

7.1 案例1: Undo表空间膨胀导致磁盘满

故障现象

2024-01-15 10:30:00 ERROR MySQL: Disk is full
应用无法写入数据,系统告警

排查过程

步骤1: 检查磁盘使用率

df -h

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1       200G  199G  1.0G  99%  /data  # 磁盘满!

步骤2: 定位大文件

du -sh /data/mysql/* | sort -rh | head -10

50G    /data/mysql/undo_001  # Undo表空间异常膨胀!
48G    /data/mysql/undo_002
10G    /data/mysql/ibdata1
5G     /data/mysql/mysql-bin.*

步骤3: 查找长事务

SELECT
  trx_id,
  trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS running_seconds,
  trx_mysql_thread_id,
  trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;

+--------+---------------------+-----------------+---------------------+-----------+
| trx_id | trx_started         | running_seconds | trx_mysql_thread_id | trx_query |
+--------+---------------------+-----------------+---------------------+-----------+
| 123456 | 2024-01-14 10:30:00 | 86400           | 100                 | NULL      |
+--------+---------------------+-----------------+---------------------+-----------+

-- 发现一个事务运行了86400秒 (24小时)!

问题分析

根因: 开发人员在测试环境执行BEGIN,做了大量UPDATE操作,忘记COMMIT
影响:
1. 长事务占用大量Undo Log,Purge线程无法清理
2. Undo表空间持续膨胀,从10GB增长到50GB
3. 磁盘空间耗尽,MySQL无法写入

解决方案

临时方案: KILL长事务

-- KILL掉长事务
KILL 100;

Query OK, 0 rows affected

-- 等待Purge线程清理Undo Log
-- 观察Undo表空间大小变化
SELECT
  FILE_NAME,
  ROUND(DATA_LENGTH/1024/1024/1024, 2) AS SIZE_GB
FROM information_schema.FILES
WHERE FILE_NAME LIKE '%undo%';

-- 10分钟后,undo_001从50GB降到12GB

永久方案: 配置自动truncate

[mysqld]
# 单个Undo表空间超过1GB自动truncate
innodb_max_undo_log_size = 1G

# 开启Undo表空间自动truncate
innodb_undo_log_truncate = ON

# 设置3个Undo表空间,支持轮转truncate
innodb_undo_tablespaces = 3

预防措施

1. ✅ 监控长事务,超过10秒告警
2. ✅ 监控Undo表空间大小,超过10GB告警
3. ✅ 配置事务超时自动回滚
4. ✅ 定期巡检,检查长事务
5. ✅ 开发规范: 禁止手动BEGIN不COMMIT

效果: Undo表空间从50GB降到10GB,磁盘使用率从99%降到60%,问题解决

7.2 案例2: Binlog磁盘满导致主从同步中断

故障现象

从库Seconds_Behind_Master持续增长
主从延迟从0秒增长到3600秒 (1小时)
从库数据严重落后

排查过程

步骤1: 检查从库状态

SHOW SLAVE STATUS\G

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 3600  -- 延迟1小时!
Last_IO_Error:
Last_SQL_Error:

步骤2: 检查主库磁盘

df -h

Filesystem      Size  Used Avail Use% Mounted on
/dev/sdb1       500G  499G  1.0G 100%  /data/binlog  # Binlog磁盘满!

步骤3: 检查Binlog文件

ls -lh /data/binlog/ | tail -20

-rw-r----- 1 mysql mysql 1.0G Jan 15 10:00 mysql-bin.000100
-rw-r----- 1 mysql mysql 1.0G Jan 15 11:00 mysql-bin.000101
...
-rw-r----- 1 mysql mysql 1.0G Jan 15 23:00 mysql-bin.000113
-rw-r----- 1 mysql mysql 500M Jan 16 00:00 mysql-bin.000114

-- 发现有114个Binlog文件,每个1GB,总计114GB

问题分析

根因: binlog_expire_logs_seconds配置失效,Binlog未自动过期
原因: 配置文件写错 (binlog_expire_logs_days拼写错误)
影响:
1. Binlog文件持续增长,磁盘满
2. 主库无法写入新Binlog,主从同步中断
3. 从库延迟持续增长

解决方案

临时方案: 手动清理Binlog

-- 查看从库同步位置
SHOW SLAVE STATUS\G
Master_Log_File: mysql-bin.000100
Read_Master_Log_Pos: 1234567

-- 主库删除mysql-bin.000100之前的Binlog
PURGE BINARY LOGS TO 'mysql-bin.000100';

Query OK, 0 rows affected
-- 删除了000001~000099,释放99GB空间

步骤2: 检查磁盘

df -h

Filesystem      Size  Used Avail Use% Mounted on
/dev/sdb1       500G  400G  100G  80%  /data/binlog  # 恢复正常

步骤3: 修复配置

[mysqld]
# 修复拼写错误
binlog_expire_logs_seconds = 604800  # 7天 (MySQL 8.0+)

# 或者 (MySQL 5.7)
expire_logs_days = 7

步骤4: 重启MySQL,验证自动清理

systemctl restart mysqld

# 等待7天后,检查Binlog是否自动清理
ls -lh /data/binlog/ | wc -l
8  # 只保留7天内的Binlog

预防措施

1. ✅ 监控Binlog磁盘使用率,超过80%告警
2. ✅ 配置binlog_expire_logs_seconds自动清理
3. ✅ 定期检查Binlog清理是否正常
4. ✅ 监控主从延迟,超过60秒告警
5. ✅ Binlog独立磁盘,避免影响数据盘

效果: Binlog磁盘使用率从100%降到80%,主从延迟从3600秒降到0秒,问题解决

八、常见问题与避坑指南

8.1 Redo Log和Binlog有什么区别?

维度	Redo Log	Binlog
所属层级	InnoDB引擎层	MySQL Server层
日志类型	物理日志 (数据页修改)	逻辑日志 (SQL/行变化)
写入方式	循环写 (覆盖)	追加写 (不覆盖)
主要作用	crash-safe崩溃恢复	主从复制、数据恢复
适用引擎	仅InnoDB	所有引擎

8.2 为什么需要两阶段提交?

核心原因: 保证Redo Log和Binlog的一致性,防止主从数据不一致

单阶段提交问题:
- 先写Redo Log,crash后Binlog无记录 → 主从不一致
- 先写Binlog,crash后Redo Log无记录 → 主从不一致

两阶段提交解决:
1. Prepare阶段: 写Redo Log (状态prepare)
2. 写Binlog
3. Commit阶段: 修改Redo Log状态为commit
4. 任何时刻crash都能保证一致性

8.3 innodb_flush_log_at_trx_commit和sync_binlog如何配置?

推荐配置 (生产环境)

[mysqld]
# 双1配置,最高可靠性
innodb_flush_log_at_trx_commit = 1  # 每次提交刷Redo Log
sync_binlog = 1                     # 每次提交刷Binlog

# 性能损失约30%,但数据绝对安全

其他配置

# 折中方案 (适合对延迟敏感的场景)
innodb_flush_log_at_trx_commit = 2  # 写OS缓存,每秒刷盘
sync_binlog = 0                     # 由OS决定刷盘时机

# 性能最优 (不推荐生产环境)
innodb_flush_log_at_trx_commit = 0
sync_binlog = 0

8.4 什么是Group Commit?如何优化?

定义: 将多个事务的日志一起刷盘,减少fsync调用次数

优化配置

[mysqld]
# 延迟10毫秒,或累积100个事务
binlog_group_commit_sync_delay = 10000
binlog_group_commit_sync_no_delay_count = 100

性能提升: QPS从1000提升到5000 (提升5倍)

8.5 如何通过Binlog恢复数据?

详见4.5节《Binlog解析与数据恢复》

8.6 长事务为什么会导致Undo表空间膨胀?

原因

1. 长事务占用Undo Log,Purge线程无法清理
2. 新事务不断产生Undo Log,但旧的无法删除
3. Undo表空间持续增长,可能从10GB增长到100GB+

危害

1. 磁盘空间耗尽
2. 备份变慢 (文件太大)
3. 影响MVCC性能 (版本链太长)

解决

1. KILL长事务
2. 配置innodb_max_undo_log_size自动truncate
3. 监控长事务,及时告警

8.7 如何监控和排查长事务?

-- 查找运行超过10秒的事务
SELECT
  trx_id,
  trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS running_seconds,
  trx_mysql_thread_id,
  trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 10;

-- 查看Undo表空间大小
SELECT
  FILE_NAME,
  ROUND(DATA_LENGTH/1024/1024/1024, 2) AS SIZE_GB
FROM information_schema.FILES
WHERE FILE_NAME LIKE '%undo%';

8.8 Binlog三种格式如何选择?

推荐: Row格式

[mysqld]
binlog_format = ROW

原因:

✅ 保证主从数据一致性
✅ 可以准确恢复数据
✅ 支持所有场景 (触发器、存储过程、函数)
❌ 唯一缺点是日志量大,但磁盘便宜,可以接受

8.9 为什么推荐使用Row格式?

Statement格式问题:
1. NOW()函数导致主从时间不一致
2. UUID()函数导致主从生成不同ID
3. RAND()函数导致主从结果不同
4. 触发器、存储过程可能执行错误

Row格式优势:
1. 记录实际值,不受函数影响
2. 主从数据绝对一致
3. 可以准确恢复数据

8.10 如何查看当前Binlog文件和位置?

-- 查看当前正在写入的Binlog文件
SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 5432109  |              |                  |
+------------------+----------+--------------+------------------+

-- 查看所有Binlog文件
SHOW BINARY LOGS;

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177       |
| mysql-bin.000002 | 1073741886|
| mysql-bin.000003 | 5432109   |
+------------------+-----------+

九、最佳实践与总结

9.1 生产环境配置推荐

[mysqld]
# ============ Redo Log配置 ============
# 每次提交刷盘,保证持久性
innodb_flush_log_at_trx_commit = 1

# 单个Redo Log文件大小 (建议1GB,默认48MB太小)
innodb_log_file_size = 1G

# Redo Log文件数量 (默认2)
innodb_log_files_in_group = 2

# Redo Log Buffer大小 (默认16MB)
innodb_log_buffer_size = 16M

# ============ Binlog配置 ============
# 开启Binlog
log_bin = ON

# 使用Row格式 (推荐)
binlog_format = ROW

# 每次提交刷盘,保证持久性
sync_binlog = 1

# Binlog文件过期时间 (7天)
binlog_expire_logs_seconds = 604800  # MySQL 8.0+
# expire_logs_days = 7               # MySQL 5.7

# 单个Binlog文件最大大小 (默认1GB)
max_binlog_size = 1G

# Group Commit优化 (可选)
binlog_group_commit_sync_delay = 0
binlog_group_commit_sync_no_delay_count = 0

# ============ Undo Log配置 ============
# Undo表空间数量 (建议3,支持自动truncate轮转)
innodb_undo_tablespaces = 3

# Undo表空间存储路径 (建议独立磁盘)
innodb_undo_directory = /data/mysql/undo/

# 单个Undo表空间最大大小 (超过自动truncate)
innodb_max_undo_log_size = 1G

# 开启Undo表空间自动truncate
innodb_undo_log_truncate = ON

# 回滚段数量 (默认128)
innodb_undo_logs = 128

9.2 监控指标

Redo Log监控

-- Redo Log使用率
SHOW ENGINE INNODB STATUS\G
-- 查看 Log sequence number 和 Last checkpoint at 的差值

-- 告警阈值: 差值超过Redo Log总大小的80%

Undo Log监控

-- Undo表空间大小
SELECT
  TABLESPACE_NAME,
  ROUND(SUM(DATA_LENGTH)/1024/1024/1024, 2) AS SIZE_GB
FROM information_schema.FILES
WHERE TABLESPACE_NAME LIKE 'innodb_undo%'
GROUP BY TABLESPACE_NAME;

-- 告警阈值: 单个Undo表空间 > 10GB

-- 长事务监控
SELECT COUNT(*) FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 10;

-- 告警阈值: 长事务数量 > 5

Binlog监控

-- Binlog磁盘使用率
df -h /data/binlog/

-- 告警阈值: 使用率 > 80%

-- Binlog文件数量
ls /data/binlog/ | wc -l

-- 告警阈值: 超过预期数量 (如7天应该7个文件)

9.3 日志管理Checklist

配置检查

✅ innodb_flush_log_at_trx_commit = 1
✅ sync_binlog = 1
✅ binlog_format = ROW
✅ binlog_expire_logs_seconds = 604800 (7天)
✅ innodb_max_undo_log_size = 1G
✅ innodb_undo_tablespaces = 3

监控检查

✅ Redo Log使用率 < 80%
✅ Undo表空间大小 < 10GB
✅ Binlog磁盘使用率 < 80%
✅ 无长事务 (>10秒)
✅ 主从延迟 < 1秒

备份检查

✅ 每天全量备份
✅ Binlog保留至少7天
✅ 备份文件可恢复验证
✅ 恢复演练定期进行

9.4 核心要点总结

三种日志的作用

Redo Log: 保证crash-safe,WAL机制提升性能
Undo Log: 支持事务回滚和MVCC一致性读
Binlog: 主从复制、数据恢复、操作审计

两阶段提交

核心原理: Prepare → 写Binlog → Commit
保证一致性: 任何时刻crash都能保证Redo Log和Binlog一致
判断依据: Binlog有记录 = 提交,Binlog无记录 = 回滚

性能优化

WAL机制: 将随机写转化为顺序写
Group Commit: 批量刷盘,减少fsync调用
异步刷脏页: 延后刷盘,减少随机I/O

故障排查

Undo膨胀: KILL长事务,配置自动truncate
Binlog磁盘满: 手动清理,配置自动过期
主从延迟: 优化Binlog格式,使用并行复制

最佳实践

双1配置: innodb_flush_log_at_trx_commit=1 + sync_binlog=1
Row格式: binlog_format=ROW,保证主从一致
监控告警: Redo Log、Undo Log、Binlog、长事务全方位监控
定期备份: 全量备份 + Binlog,保证数据可恢复

掌握MySQL日志系统的底层原理,是数据库高可用架构的基础。通过深入理解Redo Log、Undo Log、Binlog的协同工作机制,配合两阶段提交协议,能够在生产环境中构建高可靠、高性能的MySQL数据库系统。

参考资料

《高性能MySQL》(第4版) - Baron Schwartz等
《MySQL技术内幕: InnoDB存储引擎》(第2版) - 姜承尧
MySQL官方文档: dev.mysql.com/doc/refman/…
《深入理解MySQL日志系统》- 阿里云数据库团队