摘要：从一次"断电后数据丢失"和"数据重复写入"的诡异故障出发，深度剖析MySQL三种核心日志的作用与区别。通过图解WAL机制、redo log循环写入、undo log版本链、binlog的三种格式，揭秘两阶段提交如何保证redo log和binlog的一致性。用"转账"的完整案例贯穿始终，详解崩溃恢复的全流程，配合刷盘策略、参数调优、真实故障排查，让你彻底搞懂"为什么需要两份日志"、"断电后数据会怎样"等核心问题。
💥 翻车现场
周六凌晨3点，机房突然断电。
5分钟后，电力恢复，哈吉米紧急登录数据库检查数据。
-- 查询转账记录
SELECT * FROM account WHERE user_id IN (10086, 10087);
user_id | balance | update_time
--------|---------|-------------
10086 | 900 | 2024-10-07 02:58:32 -- 应该是800（转出200）
10087 | 1200 | 2024-10-07 02:58:32 -- 应该是1200（转入200）✅
-- 卧槽，user_id=10086的余额不对！
-- 明明转出了200，怎么还是900？
哈吉米："断电前user_id=10086应该从1000扣减了100，变成900，这个对了。但后来又转出200，应该是700啊，怎么还是900？"
紧急查看binlog：
-- 查看binlog记录
SHOW BINLOG EVENTS IN 'mysql-bin.000123';
pos | event_type | info
-----|------------|------
1000 | Query | BEGIN
1050 | Update | UPDATE account SET balance=900 WHERE user_id=10086 -- 第一次转账
1100 | Update | UPDATE account SET balance=1100 WHERE user_id=10087
1150 | Xid | COMMIT
1200 | Query | BEGIN
1250 | Update | UPDATE account SET balance=700 WHERE user_id=10086 -- 第二次转账
1300 | Update | UPDATE account SET balance=1300 WHERE user_id=10087
-- 断电了，没有COMMIT！
哈吉米："binlog记录了第二次转账，但没有COMMIT，所以第二次转账丢了？"
早上8点，南北绿豆和阿西噶阿西赶来了。
南北绿豆："典型的日志不一致问题！你对redo log、undo log、binlog的理解不够深。"
阿西噶阿西："而且我猜你的刷盘策略配置不对，断电时redo log还没来得及写磁盘。"
哈吉米："redo log？undo log？我只知道binlog啊！"
南北绿豆："来，今天给你讲透彻MySQL的三种日志！"
🤔 三种日志的作用对比
南北绿豆在白板上画了一个表格。
核心对比
| 特性 | redo log | undo log | binlog |
|---|---|---|---|
| 层级 | InnoDB引擎层 | InnoDB引擎层 | MySQL Server层 |
| 作用 | 崩溃恢复（保证持久性D） | 事务回滚（保证原子性A） MVCC（保证隔离性I） | 主从复制 数据恢复 |
| 记录内容 | 物理日志（数据页的变化） | 逻辑日志（相反操作） | 逻辑日志（SQL或行变化） |
| 写入时机 | 事务执行中 | 事务执行中 | 事务提交时 |
| 存储方式 | 循环写入（固定大小） | 随机写入（按需分配） | 顺序追加（无限增长） |
| 是否可删除 | 可以（循环覆盖） | 可以（事务提交后） | 需要手动清理 |
哈吉米："所以三种日志各管各的？"
阿西噶阿西："对！redo log保证断电不丢数据，undo log保证事务能回滚，binlog保证主从一致。"
南北绿豆："我们逐个讲解。"
🔥 redo log：崩溃恢复的保障
为什么需要redo log？
哈吉米："为什么不直接把数据写磁盘，还要写redo log？"
南北绿豆："因为随机IO太慢！"
问题场景：
用户执行：UPDATE account SET balance = 900 WHERE user_id = 10086;
如果直接写磁盘：
1. 找到user_id=10086所在的数据页（可能在磁盘的任意位置）
2. 修改这一页（16KB）
3. 写回磁盘（随机IO，5-10ms）
问题：
- 每次UPDATE都要随机IO，性能极差
- 磁盘IO是性能瓶颈（1000个并发UPDATE → 1000次随机IO）
解决方案：WAL（Write-Ahead Logging）
WAL机制：
1. 修改数据时，先写内存（buffer pool）
2. 同时写redo log（顺序IO，0.1ms）
3. 返回"提交成功"
4. 后台异步把内存数据刷到磁盘（批量、顺序IO）
优点：
- 顺序IO比随机IO快100倍
- 批量刷盘，减少IO次数
阿西噶阿西："所以redo log的核心是：用顺序IO代替随机IO，大幅提升性能。"
redo log的结构
南北绿豆："redo log是循环写入的，像个环形缓冲区。"
结构图：
redo log文件（固定大小，比如4个文件，每个1GB）
ib_logfile0 (1GB) → ib_logfile1 (1GB) → ib_logfile2 (1GB) → ib_logfile3 (1GB)
↑ ↓
└─────────────────────────────────────────────────────────────────┘
循环写入
关键指针：
- write pos：当前写入位置
- checkpoint：已刷盘的位置
可用空间 = write pos 到 checkpoint 之间的空间
写入流程：
1. 事务提交，写redo log（写到write pos位置）
2. write pos往前移动
3. 后台线程定期刷盘（把内存数据写到磁盘）
4. 刷盘完成，checkpoint往前移动
5. 如果write pos追上checkpoint，必须等待刷盘（阻塞写入）
图解：
初始状态：
├────────────────────────────────┤
↑ ↑
checkpoint write pos
（已刷盘） （当前写入位置）
写入事务后：
├────────────────────────────────┤
↑ ↑
checkpoint write pos
（写入了新数据）
后台刷盘后：
├────────────────────────────────┤
↑ ↑
checkpoint write pos
（刷盘完成，checkpoint前移）
如果write pos追上checkpoint：
├────────────────────────────────┤
↑
checkpoint = write pos
（redo log满了，阻塞写入）
哈吉米："所以redo log满了会阻塞写入？"
南北绿豆："对！这时候必须等后台刷盘，释放空间。"
redo log的内容
阿西噶阿西："redo log记录的是物理日志，也就是数据页的变化。"
示例：
UPDATE account SET balance = 900 WHERE user_id = 10086;
redo log记录（简化表示）：
页号: 123
偏移量: 456
旧值: balance = 1000
新值: balance = 900
意思是：在数据页123的偏移量456处，把balance从1000改成900
物理日志 vs 逻辑日志：
| 类型 | 记录内容 | 示例 |
|---|---|---|
| 物理日志 | 数据页的具体变化 | 页123偏移456，把1000改成900 |
| 逻辑日志 | SQL语句或操作 | UPDATE account SET balance=900 WHERE user_id=10086 |
南北绿豆："物理日志的好处是：恢复速度快，直接把数据写回去就行，不用重新执行SQL。"
redo log的刷盘策略
关键参数：innodb_flush_log_at_trx_commit
阿西噶阿西："这个参数决定了什么时候把redo log刷到磁盘，直接影响性能和数据安全。"
三种策略：
-- 查看当前设置
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
-- 设置刷盘策略
SET GLOBAL innodb_flush_log_at_trx_commit = 1;
策略0：延迟写入（性能最好，最不安全）
流程：
1. 事务提交，redo log写到内存缓冲区（log buffer）
2. 返回"提交成功"
3. 后台线程每秒把log buffer刷到磁盘
风险：
- 如果1秒内MySQL崩溃，这1秒的事务全丢
- 如果1秒内OS崩溃，这1秒的事务全丢
图解：
事务提交
↓
log buffer（内存）
↓（每秒一次）
OS缓存（内存）
↓
磁盘
适用场景：对数据安全要求不高，追求极致性能
策略1：每次提交都刷盘（默认，最安全）
流程：
1. 事务提交，redo log写到内存缓冲区（log buffer）
2. 调用fsync()，强制刷到磁盘
3. 返回"提交成功"
风险：
- MySQL崩溃：数据不丢（redo log在磁盘）
- OS崩溃：数据不丢（redo log在磁盘）
- 断电：数据不丢（redo log在磁盘）
代价：
- 每次事务都要磁盘IO，性能下降
图解：
事务提交
↓
log buffer（内存）
↓（立即fsync）
OS缓存（内存）
↓（立即fsync）
磁盘 ✅
适用场景：⭐⭐⭐⭐⭐ 金融、支付等核心业务（推荐）
策略2：每次提交写OS缓存（折中）
流程：
1. 事务提交，redo log写到OS缓存
2. 返回"提交成功"
3. OS自己决定什么时候刷盘（通常1秒）
风险：
- MySQL崩溃：数据不丢（redo log在OS缓存）
- OS崩溃：这1秒的事务丢失
- 断电：这1秒的事务丢失
性能：比策略1好，比策略0差
图解：
事务提交
↓
log buffer（内存）
↓（立即写入）
OS缓存（内存）✅
↓（OS决定）
磁盘
适用场景：折中方案，平衡性能和安全
三种策略对比
| 策略 | 性能 | MySQL崩溃 | OS崩溃 | 断电 | 适用场景 |
|---|---|---|---|---|---|
| 0 | ⭐⭐⭐⭐⭐ | ❌ 可能丢1秒 | ❌ 可能丢1秒 | ❌ 可能丢1秒 | 日志、临时表 |
| 1 | ⭐⭐ | ✅ 不丢 | ✅ 不丢 | ✅ 不丢 | ⭐⭐⭐⭐⭐ 核心业务 |
| 2 | ⭐⭐⭐⭐ | ✅ 不丢 | ❌ 可能丢1秒 | ❌ 可能丢1秒 | 非核心业务 |
南北绿豆："生产环境推荐用策略1，虽然性能差点，但数据绝对安全。"
哈吉米："我明白了！我之前用的是策略0，所以断电丢了数据！"
🔄 undo log：事务回滚与MVCC的基石
undo log的作用
阿西噶阿西："undo log有两个核心作用。"
作用1️⃣：事务回滚
START TRANSACTION;
UPDATE account SET balance = 900 WHERE user_id = 10086; -- 原来是1000
UPDATE account SET balance = 1100 WHERE user_id = 10087; -- 原来是1000
-- 如果这时候出错了，要回滚
ROLLBACK;
-- MySQL如何恢复？
-- 靠undo log记录的反向操作！
undo log记录：
操作1：UPDATE account SET balance = 900 WHERE user_id = 10086
undo log：UPDATE account SET balance = 1000 WHERE user_id = 10086 ← 反向操作
操作2：UPDATE account SET balance = 1100 WHERE user_id = 10087
undo log：UPDATE account SET balance = 1000 WHERE user_id = 10087 ← 反向操作
回滚流程：
ROLLBACK时：
1. 读取undo log
2. 执行反向操作
3. 恢复到事务开始前的状态
结果：
user_id=10086: balance = 1000（恢复了）
user_id=10087: balance = 1000（恢复了）
作用2️⃣：MVCC多版本并发控制
南北绿豆："还记得我们讲MVCC时的版本链吗？那就是undo log构建的！"
回顾版本链：
最新版本（在数据页中）
┌─────────────────────────────────┐
│ user_id: 10086 │
│ balance: 800 │
│ DB_TRX_ID: 200 │ ← 事务200修改的
│ DB_ROLL_PTR: ──────────────┐ │
└─────────────────────────────│───┘
↓
undo log版本2
┌─────────────────────────────────┐
│ user_id: 10086 │
│ balance: 900 │
│ DB_TRX_ID: 100 │ ← 事务100修改的
│ DB_ROLL_PTR: ──────────────┐ │
└─────────────────────────────│───┘
↓
undo log版本1
┌─────────────────────────────────┐
│ user_id: 10086 │
│ balance: 1000 │
│ DB_TRX_ID: 80 │ ← 最初的版本
│ DB_ROLL_PTR: NULL │
└─────────────────────────────────┘
南北绿豆："通过undo log，不同事务可以看到不同版本的数据，这就是MVCC的核心！"
undo log的内容
undo log记录的是逻辑日志（相反操作）：
| 操作 | undo log |
|---|---|
INSERT | DELETE（记录主键） |
DELETE | INSERT（记录完整数据） |
UPDATE | UPDATE（记录旧值） |
示例：
-- 操作
INSERT INTO account (user_id, balance) VALUES (10088, 1000);
-- undo log
DELETE FROM account WHERE user_id = 10088;
-- 操作
DELETE FROM account WHERE user_id = 10088;
-- undo log（记录完整数据，以便恢复）
INSERT INTO account (user_id, balance, create_time, ...)
VALUES (10088, 1000, '2024-10-07 10:00:00', ...);
-- 操作
UPDATE account SET balance = 900 WHERE user_id = 10086;
-- undo log
UPDATE account SET balance = 1000 WHERE user_id = 10086;
undo log什么时候被清理？
哈吉米："undo log会不会越来越多，占满磁盘？"
阿西噶阿西："会清理，但有条件。"
清理时机：
条件1：事务已提交
条件2：没有其他事务需要这个版本（MVCC）
满足两个条件后，后台线程（purge线程）会清理undo log
问题场景：
如果有个长事务一直不提交：
START TRANSACTION;
SELECT * FROM account; -- 生成Read View
-- 一直不COMMIT，持续1小时
结果：
- 这1小时内的所有undo log都不能清理（因为这个事务可能需要）
- undo log越积越多，占用大量空间
- 可能导致"undo log爆满"错误
查看undo log数量：
-- 查看当前活跃的事务
SELECT * FROM information_schema.INNODB_TRX;
-- 查看undo log使用情况
SHOW ENGINE INNODB STATUS\G
History list length 12345 ← undo log条数，越大越危险
南北绿豆："所以要避免长事务，及时提交或回滚！"
📝 binlog：主从复制与数据恢复
binlog的作用
南北绿豆："binlog是Server层的日志，有两个核心作用。"
作用1️⃣：主从复制
主库写入binlog
↓
发送给从库
↓
从库执行binlog
↓
主从数据一致
详见上一篇文章：《主从复制与读写分离》
作用2️⃣：数据恢复
场景：
今天中午12点，运维小王误删了整个订单表：
DROP TABLE order_info;
怎么恢复？
1. 恢复昨晚的全量备份（恢复到昨晚12点的状态）
2. 从昨晚12点到今天12点的binlog，重放一遍
3. 数据恢复到误删前的状态
恢复命令：
# 1. 恢复全量备份
mysql < backup_2024-10-06.sql
# 2. 从binlog恢复增量数据
mysqlbinlog --start-datetime="2024-10-06 00:00:00" \
--stop-datetime="2024-10-07 11:59:59" \
mysql-bin.000123 | mysql
# 3. 数据恢复完成
binlog的三种格式
回顾：
| 格式 | 记录内容 | 优点 | 缺点 |
|---|---|---|---|
| STATEMENT | 原始SQL | binlog小 | 某些函数会导致主从不一致 |
| ROW | 每行数据的变化 | 一致性强 | binlog大 |
| MIXED | 自动选择 | 平衡 | 判断可能不准 |
推荐ROW格式（MySQL 8.0默认）
binlog的写入时机
关键参数：sync_binlog
-- 查看当前设置
SHOW VARIABLES LIKE 'sync_binlog';
-- 设置
SET GLOBAL sync_binlog = 1;
三种策略：
策略0：OS自己决定（不安全）
流程：
1. 事务提交，binlog写到OS缓存
2. 返回"提交成功"
3. OS自己决定什么时候刷盘
风险：
- MySQL崩溃：不丢（OS缓存还在）
- OS崩溃：可能丢数据
- 断电：可能丢数据
策略1：每次提交都刷盘（最安全，推荐）
流程：
1. 事务提交，binlog写到OS缓存
2. 调用fsync()，强制刷到磁盘
3. 返回"提交成功"
风险：
- 任何情况都不丢数据 ✅
策略N：每N个事务刷一次
SET GLOBAL sync_binlog = 10;
流程：
1. 事务提交，binlog写到OS缓存
2. 每10个事务，调用fsync()刷盘
风险：
- 崩溃时可能丢失最多10个事务
sync_binlog对比
| 策略 | 性能 | 数据安全 | 适用场景 |
|---|---|---|---|
| 0 | ⭐⭐⭐⭐⭐ | ❌ 可能丢 | 不推荐 |
| 1 | ⭐⭐ | ✅ 不丢 | ⭐⭐⭐⭐⭐ 核心业务（推荐） |
| N | ⭐⭐⭐⭐ | ⚠️ 可能丢N个事务 | 折中方案 |
南北绿豆："生产环境推荐 sync_binlog=1，配合 innodb_flush_log_at_trx_commit=1，数据绝对安全！"
🔗 两阶段提交（2PC）：redo log + binlog的协同
哈吉米："为什么需要redo log和binlog两份日志？一份不行吗？"
阿西噶阿西："因为历史原因和职责分离。"
为什么有两份日志？
历史原因：
MySQL最初没有InnoDB：
- 只有MyISAM引擎
- MyISAM不支持事务
- 所以只有binlog（用于主从复制）
后来引入InnoDB：
- InnoDB支持事务
- 需要redo log保证崩溃恢复
- 但binlog已经存在了，不能删（主从复制还要用）
结果：两份日志并存
职责分离：
| 日志 | 层级 | 职责 |
|---|---|---|
| redo log | InnoDB引擎层 | 崩溃恢复（保证事务持久性） |
| binlog | Server层 | 主从复制、数据恢复 |
南北绿豆："虽然有两份日志，但必须保证两份日志的一致性，否则主从数据会不一致！"
两阶段提交的流程
问题场景：
如果不协同，会出现什么问题？
场景1：先写redo log，后写binlog
1. 写redo log成功
2. 崩溃（binlog还没写）
3. 重启后，根据redo log恢复数据（主库有数据）
4. 从库没有binlog，无法同步（主库有，从库没有）❌
场景2：先写binlog，后写redo log
1. 写binlog成功
2. 崩溃（redo log还没写）
3. 重启后，没有redo log，数据没恢复（主库没数据）
4. 从库有binlog，同步了数据（主库没有，从库有）❌
解决方案：两阶段提交（2PC）
流程图：
事务执行
↓
1. 【Prepare阶段】写redo log（标记为prepare状态）
↓
2. 写binlog
↓
3. 【Commit阶段】redo log改为commit状态
↓
事务提交完成
详细步骤：
步骤1：执行SQL（写内存 + undo log）
UPDATE account SET balance = 900 WHERE user_id = 10086;
步骤2：Prepare阶段（写redo log）
- 写redo log
- 标记状态为"prepare"（未最终提交）
步骤3：写binlog
- 生成binlog event
- 写入binlog文件
步骤4：Commit阶段（修改redo log状态）
- redo log状态从"prepare"改为"commit"
步骤5：返回"提交成功"
图解：
时间轴：
T1: 写redo log（状态=prepare）
T2: 写binlog
T3: redo log状态改为commit
T4: 返回"提交成功"
崩溃恢复的判断逻辑
阿西噶阿西："两阶段提交的精妙之处在于：无论在哪个阶段崩溃，都能正确恢复！"
场景1：Prepare之前崩溃
崩溃时间：T0.5（还没写redo log）
恢复逻辑：
1. redo log没有记录 → 事务未执行
2. binlog也没有记录
3. 结论：事务丢失（正常，因为还没提交）
场景2：Prepare之后、写binlog之前崩溃
崩溃时间：T1.5（redo log是prepare状态，binlog还没写）
恢复逻辑：
1. redo log有记录，状态=prepare
2. binlog没有记录
3. 结论：事务回滚（因为binlog没写，从库不会执行，主库也不能提交）
场景3：写binlog之后、Commit之前崩溃
崩溃时间：T2.5（binlog已写，redo log还是prepare状态）
恢复逻辑：
1. redo log有记录，状态=prepare
2. binlog有记录 ✅
3. 结论：事务提交（因为binlog已经写了，从库会执行，主库也要提交保持一致）
场景4：Commit之后崩溃
崩溃时间：T3.5（redo log状态=commit，binlog已写）
恢复逻辑：
1. redo log有记录，状态=commit
2. binlog有记录
3. 结论：事务已提交（正常）
崩溃恢复的判断流程
重启后：
↓
读取redo log
↓
检查状态
↓
┌──────────────────────────────┐
│ 状态 = commit？ │
└──────┬───────────────────┬───┘
YES NO
↓ ↓
事务已提交 状态 = prepare？
↓
┌───────┴───────┐
YES NO
↓ ↓
检查binlog是否存在 事务未开始
↓
┌───────┴───────┐
YES NO
↓ ↓
提交事务 回滚事务
南北绿豆："这就是两阶段提交的精妙之处：以binlog为准，保证主从一致！"
哈吉米："卧槽，恍然大悟！所以redo log和binlog必须保持一致！"
🎯 完整案例：转账过程中断电
阿西噶阿西："来个完整案例，梳理三种日志的协作。"
转账SQL
START TRANSACTION;
-- 转出方扣款
UPDATE account SET balance = balance - 200 WHERE user_id = 10086;
-- 原值：1000 → 新值：800
-- 转入方入账
UPDATE account SET balance = balance + 200 WHERE user_id = 10087;
-- 原值：1000 → 新值：1200
COMMIT;
完整执行流程
【事务开始】
T1: 执行第1条UPDATE
- 写内存（buffer pool）：user_id=10086, balance=800
- 写undo log：UPDATE account SET balance=1000 WHERE user_id=10086
- 写redo log（prepare）：页X偏移Y，1000→800
T2: 执行第2条UPDATE
- 写内存（buffer pool）：user_id=10087, balance=1200
- 写undo log：UPDATE account SET balance=1000 WHERE user_id=10087
- 写redo log（prepare）：页X偏移Z，1000→1200
【COMMIT】
T3: Prepare阶段
- redo log标记为prepare
T4: 写binlog
- 生成binlog event
- 写入binlog文件
T5: Commit阶段
- redo log状态改为commit
- 返回"提交成功"
T6: 后台异步刷盘
- 把内存中的数据（800, 1200）写入磁盘
断电场景分析
场景1：T1.5断电（第1条UPDATE后）
已完成：
- 内存有balance=800
- undo log有反向操作
- redo log（prepare）有记录
未完成：
- 第2条UPDATE还没执行
- 没有COMMIT
恢复逻辑：
1. 读取redo log，状态=prepare
2. binlog没有完整事务记录
3. 回滚事务（根据undo log恢复）
4. 结果：user_id=10086 balance=1000（恢复了）
场景2：T4.5断电（binlog写完后）
已完成：
- 两条UPDATE都执行了
- redo log（prepare）都写了
- binlog写完了
未完成：
- redo log还没改成commit状态
恢复逻辑：
1. 读取redo log，状态=prepare
2. 检查binlog，发现有完整事务记录 ✅
3. 提交事务（把redo log改成commit）
4. 根据redo log恢复数据
5. 结果：user_id=10086 balance=800，user_id=10087 balance=1200（正确）
场景3：T6断电（后台刷盘时）
已完成：
- 事务已提交（redo log=commit）
- binlog已写入
- 但内存数据还没刷到磁盘
恢复逻辑：
1. 读取redo log，状态=commit
2. 根据redo log重做（把800, 1200写入磁盘）
3. 结果：数据正确
三种日志的协作总结
| 日志 | 在转账中的作用 |
|---|---|
| undo log | 记录旧值（1000, 1000），用于回滚 |
| redo log | 记录新值（800, 1200），用于崩溃恢复 |
| binlog | 记录完整事务，用于主从复制 |
核心原则：
- 以binlog为准：binlog有记录，redo log就要提交（保证主从一致）
- 两阶段提交：prepare → binlog → commit（保证两份日志一致）
- 崩溃恢复：根据redo log + binlog的状态，决定提交还是回滚
📊 参数配置最佳实践
南北绿豆："最后总结一下，生产环境该怎么配置。"
核心参数
-- 查看当前配置
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'sync_binlog';
-- 推荐配置（最安全）
SET GLOBAL innodb_flush_log_at_trx_commit = 1; -- 每次提交都刷redo log
SET GLOBAL sync_binlog = 1; -- 每次提交都刷binlog
-- 折中配置（高并发场景）
SET GLOBAL innodb_flush_log_at_trx_commit = 2; -- 写OS缓存
SET GLOBAL sync_binlog = 10; -- 每10个事务刷一次binlog
配置对比
| 配置 | 性能 | 数据安全 | 适用场景 |
|---|---|---|---|
| innodb=1, binlog=1 | ⭐⭐ | ✅ 绝对安全 | ⭐⭐⭐⭐⭐ 核心业务（推荐） |
| innodb=2, binlog=10 | ⭐⭐⭐⭐ | ⚠️ 可能丢少量数据 | 高并发、非核心业务 |
| innodb=0, binlog=0 | ⭐⭐⭐⭐⭐ | ❌ 可能丢很多数据 | 临时表、日志表 |
其他重要参数
-- redo log大小（影响checkpoint频率）
SHOW VARIABLES LIKE 'innodb_log_file_size';
SET GLOBAL innodb_log_file_size = 1073741824; -- 1GB
-- redo log文件数量
SHOW VARIABLES LIKE 'innodb_log_files_in_group';
-- 默认2个，可以设置为4个
-- binlog保留天数
SHOW VARIABLES LIKE 'expire_logs_days';
SET GLOBAL expire_logs_days = 7; -- 保留7天
🎓 面试高频题
题目1：redo log和binlog的区别是什么？
答案：
| 特性 | redo log | binlog |
|---|---|---|
| 层级 | InnoDB引擎层 | MySQL Server层 |
| 作用 | 崩溃恢复（保证持久性） | 主从复制、数据恢复 |
| 记录内容 | 物理日志（数据页变化） | 逻辑日志（SQL或行变化） |
| 写入时机 | 事务执行中 | 事务提交时 |
| 存储方式 | 循环写入（固定大小） | 顺序追加（无限增长） |
题目2：为什么需要两阶段提交？
答案：
保证redo log和binlog的一致性，防止主从数据不一致。
流程：
- Prepare：写redo log（状态=prepare）
- 写binlog
- Commit：redo log状态改为commit
崩溃恢复逻辑：
- 如果binlog有记录 → 提交事务（保证主从一致）
- 如果binlog没记录 → 回滚事务
题目3：innodb_flush_log_at_trx_commit设置为多少最安全？
答案：
设置为 1 最安全（每次提交都刷盘）。
| 值 | 行为 | 数据安全 |
|---|---|---|
| 0 | 写log buffer，每秒刷盘 | ❌ 可能丢1秒数据 |
| 1 | 立即刷盘 | ✅ 绝对安全（推荐） |
| 2 | 写OS缓存，OS决定刷盘 | ⚠️ OS崩溃可能丢1秒 |
生产环境推荐：innodb_flush_log_at_trx_commit=1 + sync_binlog=1
题目4：断电后，数据会丢吗？
答案：
取决于配置：
| 配置 | 断电是否丢数据 |
|---|---|
innodb_flush_log_at_trx_commit=1 | ✅ 不丢（redo log在磁盘） |
innodb_flush_log_at_trx_commit=0 | ❌ 可能丢1秒 |
sync_binlog=1 | ✅ 不丢（binlog在磁盘） |
sync_binlog=0 | ❌ 可能丢数据 |
最安全配置：两个参数都设置为1
题目5：undo log什么时候被清理？
答案：
清理条件：
- 事务已提交
- 没有其他事务需要这个版本（MVCC）
问题场景：
- 长事务不提交 → undo log无法清理 → 占用大量空间
- 可能导致"undo log爆满"错误
解决方案：
- 避免长事务
- 及时提交或回滚
- 监控
History list length
🎉 结束语
晚上11点，三人终于把三种日志讲透了。
哈吉米："原来redo log保证崩溃恢复，undo log保证事务回滚和MVCC，binlog保证主从复制！"
南北绿豆："对！而且要记住两阶段提交，保证redo log和binlog的一致性。"
阿西噶阿西："生产环境一定要配置 innodb_flush_log_at_trx_commit=1 和 sync_binlog=1，虽然性能差点，但数据绝对安全！"
哈吉米："我明天就改配置！下周咱们聊聊什么？"
南北绿豆："死锁？我昨天又把测试库锁死了……"
哈吉米："卧槽，又是你！"
三种日志记忆口诀：
redo log保崩溃，WAL机制快如飞
undo log保回滚，MVCC版本靠它建
binlog保复制，主从一致不能少
两阶段提交妙，prepare先行binlog跟
commit最后到，崩溃恢复有依据
希望这篇文章能帮你彻底搞懂MySQL的三种日志！记住：理解日志的原理，才能理解MySQL的持久性和一致性保证！
收藏+点赞，面试不慌张！💪
