知序
返回
保存草稿
发布
H1
H2
H3
1
2
3
Excalidraw 图
AI 写作
AI 优化
目录
输入内容后显示目录
Markdown
# 04 - MySQL 日志体系 ## 三种核心日志 ``` redo log(重做日志):InnoDB 引擎层,保证事务持久性,用于崩溃恢复 undo log(回滚日志):InnoDB 引擎层,保证事务原子性,用于回滚和 MVCC binlog(二进制日志):MySQL Server 层,用于主从复制和数据恢复 ``` ## redo log(重做日志) ### 为什么需要 redo log? ``` 问题:事务提交后,数据写到 Buffer Pool(内存),还没刷到磁盘, 此时宕机,数据就丢了。 解决:Write-Ahead Logging(WAL,预写日志) 事务提交时,先把修改操作写到 redo log(顺序写,很快) Buffer Pool 的脏页可以延迟刷盘 宕机重启后,用 redo log 重放操作,恢复数据 redo log 是顺序写(追加),比随机写数据页快得多 ``` ### redo log 的结构  *图:redo log 刷盘参数与循环写 checkpoint* ``` redo log 由两部分组成: redo log buffer(内存):事务执行过程中,先写到这里 redo log file(磁盘):ib_logfile0, ib_logfile1(循环写) 写入时机(innodb_flush_log_at_trx_commit 参数): 0:每秒刷一次(性能最好,可能丢 1 秒数据) 1:每次事务提交都刷盘(默认,最安全) 2:每次提交写到 OS 缓存,每秒刷盘(折中) 循环写: ┌──────────────────────────────────────────┐ │ ib_logfile0 │ ib_logfile1 │ │ ←── write pos │ │ checkpoint ──→ │ │ write pos 追上 checkpoint 时,需要先刷脏页│ └──────────────────────────────────────────┘ redo log 循环写入与 checkpoint 推进: ┌──────────────── redo log 文件组(环形缓冲区)──────────────┐ │ │ │ ib_logfile0 ib_logfile1 │ │ ┌─────────────────────┬─────────────────────────────────┐ │ │ │ [已刷盘,可覆盖] │ [未刷盘的 redo record] │ │ │ │ │ │ │ │ └────┬────────────────┴┬────────────────────────────────┘ │ │ ↑ ↑ │ │ checkpoint write pos (追加新日志的位置) │ │ (脏页已刷, 每次事务提交 write pos 前进 │ │ 至此为止的 write pos 不能超过 checkpoint │ │ redo log 可以覆盖) 如果追上 → 必须停下来刷脏页 │ └────────────────────────────────────────────────────────────┘ 关键关系: write pos:当前写入 redo log 的位置(持续前进) checkpoint:已经刷到磁盘数据文件的位置(由刷脏页推进) write pos 到 checkpoint 之间:待刷盘的 redo log(不能覆盖) checkpoint 到 write pos 之间:空闲区域(可以写入新日志) checkpoint 推进 → 腾出空间 → write pos 继续前进 如果 write pos 追上 checkpoint → 没有空间写新日志 → 必须停下来刷脏页 → 推进 checkpoint → 这就是性能抖动的原因! ```  [编辑 Excalidraw 源图](/images/excalidraw/04-logs-redo-circular-write-diagram.excalidraw)  ### 事务执行时 redo log 的完整流程  *图:UPDATE 提交七步与 redo log 刷盘时机* ``` 以 UPDATE users SET name='李四' WHERE id=1 为例: 第 1 步:事务开始 InnoDB 为该事务分配一个事务 ID(trx_id) 第 2 步:读取数据页到 Buffer Pool 检查 id=1 的数据页是否在 Buffer Pool 中 不在 → 从磁盘读取到 Buffer Pool 在 → 直接使用 第 3 步:记录 undo log(修改前) 把 id=1 的旧值(name='张三')写入 undo log 用于回滚和 MVCC 第 4 步:修改 Buffer Pool 中的数据页 在内存中把 name 改为 '李四' 该页被标记为脏页(dirty page) 第 5 步:写 redo log buffer(内存) 把"对 id=1 的数据页做了什么修改"写入 redo log buffer 此时还在内存,没有落盘 第 6 步:事务提交 根据 innodb_flush_log_at_trx_commit 参数决定刷盘时机: =1(默认):立即将 redo log buffer 刷到 redo log 文件(磁盘) =0:不立即刷,等后台线程每秒刷一次 =2:写到 OS 的 page cache,等后台线程每秒刷一次 第 7 步:后台线程异步刷脏页 Buffer Pool 中的脏页由后台线程(Page Cleaner)定期刷到磁盘 这一步是异步的,不影响事务提交的响应时间 时间线: ┌──────────────────────────────────────────────────────────────┐ │ 事务开始 │ │ ↓ │ │ 读数据页到 Buffer Pool │ │ ↓ │ │ 写 undo log(记录旧值,用于回滚) │ │ ↓ │ │ 修改 Buffer Pool 中的数据页(内存)← 脏页 │ │ ↓ │ │ 写 redo log buffer(内存) │ │ ↓ │ │ 事务提交 → redo log buffer 刷盘 ← 这一步保证了持久性 │ │ ↓ │ │ 返回成功给客户端 │ │ ↓(异步) │ │ 后台线程将脏页刷到磁盘数据文件 │ └──────────────────────────────────────────────────────────────┘ 关键点: 事务提交时,只要 redo log 落盘就算成功 数据页不需要立即落盘(延迟刷盘提高性能) 宕机时,用 redo log 重放,把未落盘的数据页恢复出来 ```  [编辑 Excalidraw 源图](/images/excalidraw/04-logs-redo-transaction-flow-diagram.excalidraw) ### crash-safe(崩溃恢复) ``` redo log 让 InnoDB 具备 crash-safe 能力: 事务提交 → redo log 写盘 → 返回成功 宕机重启 → 读取 redo log → 重放未刷盘的操作 → 数据恢复 ``` ### 两阶段提交(Two-Phase Commit)  *图:redo log 与 binlog 两阶段提交及宕机恢复* ``` 为什么需要两阶段提交? 事务提交时同时涉及 redo log(InnoDB 层)和 binlog(Server 层) 必须保证两者一致:要么都写,要么都不写 否则主从数据不一致! 两阶段提交流程: 事务开始 ↓ ┌──────────────────────┐ │ 第 1 阶段:prepare │ │ 1. 写 undo log │ │ 2. 修改 Buffer Pool │ │ 3. 写 redo log buffer │ │ 4. redo log prepare │ ← 标记 prepare 状态 └──────────┬───────────┘ ↓ ┌──────────────────────┐ │ 第 2 阶段:commit │ │ 5. 写 binlog │ ← 先写 binlog! │ 6. redo log commit │ ← 再 commit redo log └──────────┬───────────┘ ↓ 事务提交完成,返回客户端 关键设计:为什么先写 binlog 再 commit redo log? 场景 1:redo log prepare 后,写 binlog 前宕机 恢复时:redo log 是 prepare 状态,binlog 为空 → 回滚事务(binlog 没写,从库没有这个事务) 场景 2:binlog 写完,redo log commit 前宕机 恢复时:redo log 是 prepare 状态,binlog 有记录 → 继续提交事务(binlog 已写,从库会同步这个事务) → 主库 redo log commit,保持主从一致 核心原则: binlog 写成功 = 事务一定提交(否则主从不一致) binlog 没写 = 事务一定回滚 以 binlog 为判断依据,保证主从数据一致 ```  [编辑 Excalidraw 源图](/images/excalidraw/04-logs-two-phase-commit-diagram.excalidraw)  ### xa recover 命令 ## undo log(回滚日志) ### 两个作用 ``` 1. 事务回滚: 事务执行过程中,把修改前的数据记录到 undo log 如果事务回滚,用 undo log 恢复原始数据 2. MVCC: undo log 保存了数据的历史版本,形成版本链 MVCC 通过版本链实现快照读(见第 03 篇) ``` ### undo log 的内容 ``` INSERT 操作:记录主键,回滚时删除该行 DELETE 操作:记录整行数据,回滚时重新插入 UPDATE 操作:记录修改前的值,回滚时恢复旧值 ``` ## binlog(二进制日志) ### binlog 的作用 ``` 1. 主从复制: 主库把 binlog 发给从库,从库重放 binlog,保持数据同步 2. 数据恢复: 全量备份 + binlog,可以恢复到任意时间点 误删数据后,用 binlog 回放到删除前的状态 ``` ### binlog 的三种格式 ``` STATEMENT(语句模式): 记录 SQL 语句本身 优点:日志量小 缺点:某些函数(NOW()、UUID())在主从执行结果不同,可能导致数据不一致 ROW(行模式,推荐): 记录每行数据的变化(修改前和修改后的值) 优点:精确,不会有数据不一致问题 缺点:日志量大(批量更新时每行都记录) MIXED(混合模式): 默认用 STATEMENT,遇到可能不一致的情况自动切换为 ROW ``` ### ROW 格式 binlog 实际内容示例 ```sql -- 执行以下 SQL: UPDATE orders SET status = 1 WHERE id = 5; -- 查看 binlog 内容(使用 mysqlbinlog 工具解析): mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000001 ``` ``` # 解析后的 ROW 格式 binlog 内容(伪代码形式): === 事件头 === # at 1234 # 240115 10:30:00 server id 1 end_log_pos 1456 # 事件类型: UPDATE_ROWS_EVENT # 表: mydb.orders === 修改前的行数据(Before Image)=== ### UPDATE `mydb`.`orders` ### WHERE ### @1=5 -- id=5(主键) ### @2=1002 -- user_id=1002 ### @3=0 -- status=0(修改前) ### @4=599.00 -- amount=599.00 ### @5='2024-01-02 11:00:00' -- create_time === 修改后的行数据(After Image)=== ### SET ### @1=5 -- id=5(不变) ### @2=1002 -- user_id=1002(不变) ### @3=1 -- status=1(修改后!) ### @4=599.00 -- amount=599.00(不变) ### @5='2024-01-02 11:00:00' -- create_time(不变) -- ROW 格式记录了每一列修改前后的完整值 -- 从库收到这条 binlog 后,精确地将 id=5 的 status 从 0 改为 1 -- 不依赖 SQL 语句,不受 NOW()、UUID() 等函数影响 ``` ```sql -- 对比 STATEMENT 格式的 binlog: -- 只记录 SQL 语句本身: -- UPDATE orders SET status = 1 WHERE id = 5; -- 从库直接重放这条 SQL -- STATEMENT 格式的问题示例: UPDATE orders SET create_time = NOW() WHERE id = 5; -- 主库执行时:NOW() = '2024-01-15 10:30:00' -- 从库延迟 5 秒后重放:NOW() = '2024-01-15 10:30:05' -- 主从数据不一致! -- ROW 格式不会有这个问题: -- 记录的是 create_time='2024-01-15 10:30:00'(具体值) -- 从库直接用这个值,不重新计算 NOW() ``` ```sql -- 查看 binlog 格式 SHOW VARIABLES LIKE 'binlog_format'; -- 查看 binlog 列表 SHOW BINARY LOGS; -- 查看 binlog 内容 SHOW BINLOG EVENTS IN 'mysql-bin.000001'; -- 用 mysqlbinlog 工具解析 mysqlbinlog mysql-bin.000001 | grep -A 5 'DELETE' ``` ## redo log vs binlog 的区别(面试高频)  *图:redo log 与 binlog 的五个维度对比* ``` 维度 redo log binlog ───────────────────────────────────────────────────────────── 所属层 InnoDB 引擎层 MySQL Server 层 内容 物理日志(数据页的修改) 逻辑日志(SQL 或行变化) 写入方式 循环写(固定大小,会覆盖) 追加写(不会覆盖) 用途 崩溃恢复(crash-safe) 主从复制、数据恢复 事务提交 必须写盘才算提交 可以配置 ``` ## 两阶段提交(2PC)— 面试高频 ### 为什么需要两阶段提交? ``` 问题:redo log 和 binlog 是两个独立的日志,如果只写了一个就宕机, 会导致两个日志不一致,进而导致主从数据不一致。 场景 1:写完 redo log,宕机,binlog 没写 → 用 redo log 恢复了数据,但 binlog 没有这条记录 → 从库没有这条数据,主从不一致 场景 2:写完 binlog,宕机,redo log 没写 → 主库没有这条数据,但 binlog 有 → 从库有这条数据,主从不一致 ``` ### 两阶段提交的流程 ``` 事务提交过程: 阶段 1(prepare): ① 写 redo log,状态标记为 prepare ② 写 binlog 阶段 2(commit): ③ 写 redo log,状态标记为 commit 崩溃恢复时的判断: redo log 是 commit 状态 → 直接提交 redo log 是 prepare 状态: binlog 完整 → 提交(因为 binlog 已写,从库会有这条数据) binlog 不完整 → 回滚 ``` ``` 时间线: 事务执行 ↓ 写 redo log(prepare) ↓ 写 binlog ↓ 写 redo log(commit)← 如果这里宕机,binlog 完整,恢复时提交 ↓ 返回成功 ``` > 💬 **Q:阶段二的写 redo log 和阶段一有什么区别?** > > 区别在于写入的**状态标记不同**,数据内容完全一样,改变的只是这个状态标志位。 > > - 阶段 1:写 redo log,状态 = `prepare`,表示"我准备好了,但还没最终确认"。此时 binlog 还没写,如果宕机 → 回滚。 > - 阶段 2:写 redo log,状态 = `commit`,表示"我已经最终确认提交了"。此时 binlog 已写完,如果宕机 → 直接提交。 > > 这个状态标志就是崩溃恢复的判断依据——看到 `commit` 直接提交,看到 `prepare` 就去检查 binlog 是否完整再决定提交还是回滚。 ### 两阶段提交的具体故障场景分析 ``` 场景 A:在写 redo log(prepare)之后、写 binlog 之前宕机 重启后: redo log 是 prepare 状态 binlog 不完整(没有这条记录) → 判断:回滚这个事务 → 主库没有这条数据,从库也没有 → 一致 ✓ 场景 B:在写 binlog 之后、写 redo log(commit)之前宕机 重启后: redo log 是 prepare 状态 binlog 完整(有这条记录) → 判断:提交这个事务(因为从库已经有了这条数据) → 主库有这条数据,从库也有 → 一致 ✓ 场景 C:在写 redo log(commit)之后宕机 重启后: redo log 是 commit 状态 → 直接提交 → 主库有这条数据,从库也有 → 一致 ✓ 结论:两阶段提交保证了无论在哪个时间点宕机, redo log 和 binlog 最终都是一致的 ``` ## binlog 实现时间点恢复(PITR) ### 场景:误删数据,需要恢复 ``` 背景: 周一 00:00 做了全量备份(mysqldump) 周三 14:30 有人误执行了 DELETE FROM orders WHERE status = 3; 需要恢复到周三 14:29 的状态 恢复步骤: 第 1 步:找到全量备份文件 备份文件:backup_20240115_000000.sql 备份时记录的 binlog 位置:mysql-bin.000010, pos=4 第 2 步:恢复全量备份 mysql -u root -p mydb < backup_20240115_000000.sql 此时数据库恢复到周一 00:00 的状态 第 3 步:找到误删操作在 binlog 中的位置 mysqlbinlog mysql-bin.000010 mysql-bin.000011 mysql-bin.000012 \ | grep -n "DELETE FROM orders" 假设找到:mysql-bin.000012, pos=8920(误删操作的位置) 第 4 步:重放从备份点到误删前的 binlog mysqlbinlog \ --start-position=4 \ --stop-position=8920 \ mysql-bin.000010 mysql-bin.000011 mysql-bin.000012 \ | mysql -u root -p mydb 这会重放从周一 00:00 到周三 14:29 的所有操作 但不包括 14:30 的误删操作 第 5 步:验证数据 SELECT COUNT(*) FROM orders WHERE status = 3; -- 应该能看到被误删的数据已经恢复 注意事项: 1. binlog 必须开启(log_bin = ON) 2. binlog 格式推荐 ROW,恢复更精确 3. 生产环境建议定期备份 binlog 到远程存储 4. 恢复操作在从库或测试库上进行,验证后再同步到主库 ``` ```sql -- 开启 binlog(在 my.cnf 中配置) [mysqld] log_bin = /var/log/mysql/mysql-bin binlog_format = ROW expire_logs_days = 7 -- binlog 保留 7 天 -- 查看 binlog 是否开启 SHOW VARIABLES LIKE 'log_bin'; -- 查看当前 binlog 文件和位置 SHOW MASTER STATUS; -- +------------------+----------+ -- | File | Position | -- +------------------+----------+ -- | mysql-bin.000012 | 9234 | -- +------------------+----------+ ``` ## redo log 与 binlog 的刷盘时机 ### redo log 的刷盘时机 ``` 由 innodb_flush_log_at_trx_commit 控制: 0:每次提交只写到 redo log buffer,由后台线程定时刷盘 1:每次提交都 write + fsync 到磁盘 2:每次提交 write 到文件,但 fsync 由后台线程定时执行 ``` ``` 面试里可以直接记: 1 最安全,0 性能最好,2 折中。 ``` ### binlog 的刷盘时机 ``` 由 sync_binlog 控制: 0:每次提交只写文件系统缓存,由操作系统决定何时刷盘 1:每次提交都 fsync N:每 N 次提交 fsync 一次 ``` ## 组提交(Group Commit) ``` 组提交就是把多个事务的 binlog / redo log 刷盘动作合并, 避免每个事务都单独 fsync 一次。 好处: 1. 降低磁盘刷盘次数 2. 提升事务提交吞吐量 3. 也为并行复制提供了更好的分组基础 ``` ``` 所以很多时候两阶段提交并不等于“每个事务都要单独刷盘”, 而是“多个事务按批次一起提交、一起刷盘”。 ``` ## undo log 会一直存在吗?什么时候删除 ``` 不会一直存在。 undo log 的生命周期和两个因素有关: 1. 事务是否已经提交 2. 是否还有活跃事务需要通过 MVCC 读取旧版本 ``` ``` 即使事务已经提交, 如果还有其他事务的快照读可能访问这些旧版本, undo log 也不能立刻删。 等到这些旧版本对所有活跃事务都不可见后, 后台 purge 线程才会逐步清理。 ``` ## MySQL 能保证数据 100% 不丢吗 ``` 严格来说,不能轻易承诺“100% 不丢”。 因为是否丢数据取决于: 1. redo log 刷盘策略 2. binlog 刷盘策略 3. 存储设备是否可靠 4. 宕机发生在什么时间点 5. 主从复制是不是半同步甚至全同步 ``` ``` 如果要尽量逼近“不丢”: 1. innodb_flush_log_at_trx_commit = 1 2. sync_binlog = 1 3. 关键链路用更可靠的存储 4. 主从高可用场景配合半同步复制 但即便如此,也更适合说“把丢数据概率降到极低”, 而不是轻易说绝对 100% 不丢。 ``` ## 面试常见问题 **Q:redo log 和 binlog 的区别?** A:① 层次不同:redo log 是 InnoDB 引擎层,binlog 是 Server 层;② 内容不同:redo log 是物理日志(记录数据页的修改),binlog 是逻辑日志(记录 SQL 或行变化);③ 写入方式不同:redo log 循环写,binlog 追加写;④ 用途不同:redo log 用于崩溃恢复,binlog 用于主从复制和数据恢复。 **Q:为什么需要两阶段提交?** A:为了保证 redo log 和 binlog 的一致性。如果不用两阶段提交,可能出现一个写了另一个没写的情况,导致主从数据不一致。两阶段提交通过 prepare 和 commit 两个状态,确保两个日志要么都有,要么都没有。 **Q:redo log 为什么能提高写性能?** A:redo log 是顺序写(追加到文件末尾),而直接写数据页是随机写(需要找到对应的数据页位置)。顺序写的速度远快于随机写,所以先写 redo log 再异步刷数据页,能显著提高写性能。 **Q:binlog 的 ROW 格式和 STATEMENT 格式有什么区别?** A:STATEMENT 格式记录 SQL 语句本身,日志量小,但遇到 NOW()、UUID() 等函数时主从执行结果可能不同,导致数据不一致。ROW 格式记录每行数据修改前后的完整值,精确可靠,不受函数影响,但日志量大(批量更新时每行都记录)。生产环境推荐 ROW 格式。 **Q:如何用 binlog 恢复误删的数据?** A:① 先恢复最近的全量备份;② 用 mysqlbinlog 找到误删操作在 binlog 中的位置;③ 重放从备份点到误删操作之前的所有 binlog;④ 验证数据恢复正确。前提是 binlog 必须开启,且 binlog 文件没有被清理。 --- 上一篇:[03 - 事务与 MVCC](./03-transaction-mvcc.md) | 下一篇:[05 - 锁机制](./05-locks.md)
预览
发布文章
添加摘要让读者更快了解文章内容
取消
确认发布
AI 写作助手
输入一个主题,AI 将为你生成一篇 Markdown 博客文章
AI 正在写作中...
取消
开始生成
AI 优化助手
描述你的修改需求,AI 将在原文基础上进行补充或微调
AI 正在优化中...
取消
开始优化
画布
从已有图里选
✕
图名
保存后生成 /images/excalidraw/<图名>.svg(只能用字母、数字、短横线、下划线、点)
取消
保存并插入