MySQL三大日志的爱恨情仇 📝

一、开篇故事：三个记账本的故事 💰

想象你开了一家餐厅，有三个不同的账本：

账本1：Undo Log（后悔药）💊

作用：记录"修改前"的数据，可以撤销

场景：
小明："我要买单，刚才那桌消费了500元。"
你："好的，记账：账户余额 1000 → 500"
小明："等等，算错了，其实是300元！"
你："没关系，我有'修改前'的记录，可以回滚！"
  → undo log: 余额从1000改成500
  → 撤销：余额恢复1000
  → 重新扣款：余额1000 → 700 ✅

功能：
  - 记录旧值（1000）
  - 支持回滚
  - 支持MVCC（多版本并发控制）

账本2：Redo Log（安全保险）🛡️

作用：记录"修改后"的数据，防止数据丢失

场景：
小红："我消费了200元。"
你："好的，先在草稿本（redo log）记一下：余额500 → 300"
  → 还没写到正式账本（磁盘）
突然停电！💡❌
来电后：
你："让我看看草稿本...哦，小红消费了200元，补记到正式账本。"
  → 从redo log恢复数据 ✅

功能：
  - 记录新值（300）
  - 先写日志，后写磁盘（WAL）
  - 崩溃恢复

账本3：Binlog（总账本）📚

作用：记录所有操作，可以复制、恢复

场景：
主店："今天的营业记录：
  - 10:00 小明消费500元
  - 11:00 小红消费200元
  - 12:00 小刚消费300元"
  
分店："让我抄一份！"
  → binlog同步
  → 主从复制 ✅

DBA："昨天的数据被误删了！"
  → 从binlog恢复昨天的数据 ✅

功能：
  - 记录所有修改操作
  - 主从复制
  - 数据恢复

---

二、Undo Log：后悔药 💊

2.1 什么是Undo Log？

Undo Log（回滚日志） 记录数据修改前的值，用于事务回滚和MVCC。

2.2 作用

作用1：事务回滚
  UPDATE users SET age = 26 WHERE id = 1;
  undo log: age从25改成26
  ROLLBACK → 从undo log读取旧值25，恢复 ✅

作用2：MVCC（多版本并发控制）
  事务A: SELECT * FROM users WHERE id = 1; -- 读到age=25
  事务B: UPDATE users SET age = 26 WHERE id = 1; COMMIT;
  事务A: SELECT * FROM users WHERE id = 1; -- 还是25!
  → 从undo log读取旧版本 ✅

2.3 存储位置

MySQL 5.6之前：存储在系统表空间（ibdata1）
MySQL 5.7+：独立undo表空间（undo_001, undo_002）

2.4 Undo Log链（版本链）

-- 初始数据
id=1, age=25, trx_id=100

-- 事务200修改
UPDATE users SET age = 26 WHERE id = 1;
-- 新数据：id=1, age=26, trx_id=200
-- undo log: age=25, trx_id=100

-- 事务300修改
UPDATE users SET age = 27 WHERE id = 1;
-- 新数据：id=1, age=27, trx_id=300
-- undo log: age=26, trx_id=200

版本链：
  最新版本: age=27, trx_id=300
      ↓ (undo指针)
  旧版本1: age=26, trx_id=200
      ↓
  旧版本2: age=25, trx_id=100

2.5 Undo Log清理

Purge线程负责清理不再需要的undo log：

条件：
  - 事务已提交
  - 没有其他事务需要这个版本（MVCC）

长事务的危害：
  - 长时间不提交
  → undo log无法清理
  → 占用大量空间 💀

---

三、Redo Log：安全保险 🛡️

3.1 什么是Redo Log？

Redo Log（重做日志） 记录数据修改后的值，用于崩溃恢复。

3.2 为什么需要Redo Log？

问题：写磁盘太慢！

直接写磁盘：
  1. 找到数据页位置（随机IO）
  2. 读取整个页（16KB）
  3. 修改页中的数据
  4. 写回磁盘（随机IO）
  耗时：10ms ❌

写Redo Log：
  1. 顺序追加到日志文件
  2. 写一小段日志（几十字节）
  耗时：0.1ms ✅

性能提升：100倍！

3.3 WAL（Write-Ahead Logging）

核心思想：先写日志，后写磁盘

步骤：
1. 执行SQL：UPDATE users SET age = 26 WHERE id = 1;
2. 修改Buffer Pool中的数据页（内存）
3. 记录redo log：id=1, age=26（磁盘，顺序写）✅
4. 返回成功给客户端
5. 后台线程慢慢把脏页刷到磁盘（异步）

优点：
  - 快速响应（只写日志）
  - 崩溃恢复（从redo log重放）

3.4 Redo Log结构

Redo Log Buffer（内存）
  ↓ flush
Redo Log File（磁盘）
  ├─ ib_logfile0（固定大小，如512MB）
  └─ ib_logfile1（固定大小，如512MB）
  循环使用！

循环写入：

ib_logfile0: [已刷盘|未刷盘|空闲]
              ↑write pos  ↑checkpoint
              
write pos：当前写入位置
checkpoint：已刷盘的位置

空闲空间 = write pos - checkpoint

如果空间不足：
  → 停止写入
  → 强制刷脏页到磁盘
  → 移动checkpoint
  → 释放空间 ✅

3.5 Redo Log刷盘策略

-- innodb_flush_log_at_trx_commit
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

值：
0: 每秒刷盘一次（性能最好，可能丢1秒数据）❌
1: 每次事务提交都刷盘（最安全，性能一般）✅
2: 每次提交写OS缓存，每秒刷盘（折中方案）⚠️

性能对比：

配置0: 10000 TPS（最快，但不安全）
配置1: 1000 TPS（安全，推荐）✅
配置2: 5000 TPS（折中）

---

四、Binlog：总账本 📚

4.1 什么是Binlog？

Binlog（二进制日志） 记录所有DDL和DML语句，用于主从复制和数据恢复。

4.2 作用

作用1：主从复制
  主库：记录所有修改 → binlog
  从库：拉取binlog → 重放 → 数据同步 ✅

作用2：数据恢复
  全量备份（昨天晚上）
  + binlog（今天的所有操作）
  = 完整恢复到当前 ✅

作用3：审计
  谁在什么时候做了什么操作？
  → 查binlog ✅

4.3 Binlog格式

格式1：STATEMENT

记录： SQL语句

-- 执行SQL
UPDATE users SET age = age + 1 WHERE city = '北京';

-- binlog记录
UPDATE users SET age = age + 1 WHERE city = '北京';

优点：

✅ binlog小（只记录SQL）
✅ 可读性好

缺点：

❌ 有些函数不确定（如NOW(), RAND()）
  主库执行：UPDATE users SET create_time = NOW();
  → 主库：2024-01-15 10:00:00
  从库重放：2024-01-15 10:00:05 ← 不一致！❌

格式2：ROW（推荐）

记录： 每一行的变化

-- 执行SQL
UPDATE users SET age = age + 1 WHERE city = '北京';

-- binlog记录（伪代码）
UPDATE users SET age = 26 WHERE id = 1; -- 旧值25，新值26
UPDATE users SET age = 31 WHERE id = 2; -- 旧值30，新值31
UPDATE users SET age = 21 WHERE id = 5; -- 旧值20，新值21
...

优点：

✅ 数据一致性好（记录每行的实际变化）
✅ 适合主从复制
✅ 可以做数据闪回（根据旧值恢复）

缺点：

❌ binlog大（大量修改时很大）
  UPDATE users SET status = 1; -- 100万行
  → binlog记录100万条 💀

格式3：MIXED

记录： 自动选择STATEMENT或ROW

一般情况：STATEMENT（省空间）
特殊函数（NOW等）：ROW（保证一致性）

4.4 Binlog刷盘策略

-- sync_binlog
SET GLOBAL sync_binlog = 1;

值：
0: 由OS决定何时刷盘（性能好，不安全）❌
1: 每次提交都刷盘（最安全，推荐）✅
N: 每N个事务刷盘（折中）

---

五、三大日志对比 📊

5.1 对比表

特性	Undo Log	Redo Log	Binlog
层级	InnoDB引擎	InnoDB引擎	MySQL Server
作用	事务回滚、MVCC	崩溃恢复	主从复制、数据恢复
记录内容	修改前的值	修改后的值	SQL语句或行变化
格式	逻辑日志	物理日志	逻辑日志
写入时机	修改数据前	修改数据后	事务提交时
存储方式	undo表空间	ib_logfile	binlog文件
循环写	❌ 否	✅ 是（循环覆盖）	❌ 否（追加）
清理	Purge线程清理	checkpoint后覆盖	过期自动删除

5.2 工作流程图

事务执行流程：

1. 开始事务
   ↓
2. 修改前记录undo log（旧值）
   ↓
3. 修改Buffer Pool中的数据
   ↓
4. 记录redo log（新值）
   ↓
5. 准备提交
   ↓
6. 记录binlog（SQL或行变化）
   ↓
7. 提交事务（两阶段提交）
   ↓
8. 刷redo log到磁盘
   ↓
9. 刷binlog到磁盘
   ↓
10. 事务完成 ✅

---

六、两阶段提交（2PC）🔄

6.1 为什么需要两阶段提交？

问题： 保证redo log和binlog一致

场景1：先写redo log，后写binlog
  1. 写redo log成功 ✅
  2. 崩溃 💥（binlog没写）
  恢复后：
    - 主库有数据（从redo log恢复）
    - 从库没数据（binlog没记录）
    → 主从不一致！❌

场景2：先写binlog，后写redo log
  1. 写binlog成功 ✅
  2. 崩溃 💥（redo log没写）
  恢复后：
    - 主库没数据（redo log没记录）
    - 从库有数据（从binlog复制）
    → 主从不一致！❌

6.2 两阶段提交流程

阶段1：Prepare
  1. 写redo log（标记为prepare状态）✅
  2. 写binlog ✅

阶段2：Commit
  3. 写redo log（标记为commit状态）✅
  
事务完成！

崩溃恢复时：
  - redo log=prepare，binlog有：提交事务 ✅
  - redo log=prepare，binlog无：回滚事务 ✅
  → 保证一致性！

图解：

      写redo log(prepare)
            ↓
         写binlog
            ↓
      写redo log(commit)
            ↓
        事务提交
        
如果在任何一步崩溃：
  - binlog没写完 → 回滚（undo log）
  - binlog写完了 → 提交（redo log）

---

七、实战案例：误删数据恢复 💾

案例背景

-- 10:00 全量备份
mysqldump > backup.sql

-- 11:00 正常业务...
-- 12:00 正常业务...

-- 13:00 误操作！
DELETE FROM orders; -- 删除了所有订单！💀

-- 13:05 发现问题！需要恢复！

恢复步骤

步骤1：停止业务（防止继续写入）

SET GLOBAL read_only = 1;

步骤2：恢复全量备份

mysql < backup.sql
# 恢复到10:00的状态

步骤3：从binlog恢复增量数据

# 查看binlog文件
SHOW BINARY LOGS;

# 导出binlog为SQL
mysqlbinlog --start-datetime="2024-01-15 10:00:00" \
            --stop-datetime="2024-01-15 13:00:00" \
            mysql-bin.000001 > binlog.sql

# 去掉误删的DELETE语句
vi binlog.sql  # 删除 DELETE FROM orders;

# 执行binlog
mysql < binlog.sql

步骤4：验证数据

SELECT COUNT(*) FROM orders;
-- 检查数据是否完整

步骤5：恢复业务

SET GLOBAL read_only = 0;

---

八、日志相关参数调优 ⚙️

8.1 Undo Log参数

-- undo表空间数量
innodb_undo_tablespaces = 2

-- undo日志截断（自动回收空间）
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 1GB  -- 超过1GB自动截断

8.2 Redo Log参数

-- redo log文件大小
innodb_log_file_size = 512M

-- redo log文件数量
innodb_log_files_in_group = 2

-- 刷盘策略（推荐1）
innodb_flush_log_at_trx_commit = 1

-- redo log buffer大小
innodb_log_buffer_size = 16M

8.3 Binlog参数

-- binlog格式（推荐ROW）
binlog_format = ROW

-- 刷盘策略（推荐1）
sync_binlog = 1

-- binlog过期时间（天）
expire_logs_days = 7

-- binlog大小（512MB一个文件）
max_binlog_size = 512M

---

九、面试高频问题 🎤

Q1: redo log和binlog的区别？

答：

1. 层级：redo log是InnoDB引擎层，binlog是MySQL Server层
2. 作用：redo log用于崩溃恢复，binlog用于主从复制和数据恢复
3. 内容：redo log记录物理变化（页的修改），binlog记录逻辑变化（SQL或行）
4. 存储：redo log循环写（覆盖），binlog追加写（不覆盖）

Q2: 为什么需要两阶段提交？

答： 保证redo log和binlog的一致性。如果不用两阶段提交，可能出现主从数据不一致（一个有数据，另一个没有）。

Q3: undo log的作用是什么？

答：

1. 事务回滚：记录修改前的值，ROLLBACK时恢复
2. MVCC：提供历史版本，实现快照读

Q4: binlog的三种格式区别？

答：

• STATEMENT：记录SQL，binlog小但可能不一致（NOW等函数）
• ROW：记录行变化，一致性好但binlog大（推荐）
• MIXED：自动选择，一般用STATEMENT，特殊情况用ROW

Q5: 如何用binlog恢复数据？

答：

1. 恢复全量备份
2. 用mysqlbinlog导出增量SQL
3. 去掉误操作的SQL
4. 执行binlog恢复数据

---

十、总结口诀 📝

MySQL三大日志，
各有各的用。
undo记旧值，
回滚和MVCC。

redo记新值，
崩溃能恢复。
先写日志后写盘，
WAL性能高。

binlog记操作，
主从复制靠它。
数据恢复也需要，
备份加binlog。

两阶段提交妙，
redo和binlog一致好。
prepare写redo，
binlog后commit。

日志参数要调好，
刷盘策略很重要。
安全选1最靠谱，
性能一致两兼顾！

---

参考资料 📚

• MySQL官方文档 - InnoDB Redo Log
• MySQL官方文档 - Binary Log
• MySQL技术内幕：InnoDB存储引擎

---

下期预告： 145-分库分表中间件Sharding-JDBC和MyCat的实现原理 🔧

---

编写时间：2025年
作者：技术文档小助手 ✍️
版本：v1.0

愿你的数据永远安全！ 💾✨