03.5 阶段 4:日志、持久化与备份恢复
所属:全栈开发面试题库 / 学习路径 / 03 MySQL 与 PostgreSQL
本页目录
一、阶段定位
阶段 3 讲了事务在内存里怎么并发。本阶段解决"断电/宕机/误删表后怎么办":数据库靠什么保证已提交事务不丢、备份怎么做、误删后怎么恢复到前一分钟。这是运维面试与生产事故复盘的核心。
判断自己是否卡在本阶段:
- 知道有 redo log、undo log、binlog 三个名字,但说不清它们各记什么、写入顺序、刷盘参数——这是日志机制没建立。
- 听说过"两阶段提交",但答不出它协调的是哪两个日志、解决什么一致性问题——这是崩溃恢复原理不过关。
- 只会用 mysqldump 导数据,不知道它在大库上会锁表、有多慢——这是备份工具理解不深。
- 备份脚本写了一年,从没做过恢复演练,真出事时不知道怎么恢复——这是本阶段最该警惕的盲区。
衔接关系:阶段 3 的 undo log 是 MVCC 版本链与回滚的基础,本阶段展开它在崩溃恢复中的角色;阶段 5 的主从复制依赖 binlog(MySQL)与 WAL(PG)传输。不理解 redo/undo/binlog 的写入顺序,就解释不了 innodb_flush_log_at_trx_commit=1 为什么是安全默认值、=0/2 为什么会丢数据。
二、阶段目标与投入
| 能力目标(可验证的行为) | 验证方式 |
|---|---|
| 能说清 redo、undo、binlog 各自的作用与写入顺序 | 白板画出"修改内存页 → 写 redo → 写 binlog → 刷盘"流程 |
| 能解释两阶段提交与组提交 | 说明 prepare/commit 两阶段解决 redo 与 binlog 一致性 |
| 能对比 statement/row/mixed 三种 binlog 格式 | 说出为什么主从复制用 row |
能配置 innodb_flush_log_at_trx_commit 与 sync_binlog 的安全组合 |
列出 1/1、1/0、0/0 各丢多少 |
| 能做一次 mysqldump 与一次 pg_dump 逻辑备份 | 备份文件能恢复到测试库 |
| 能基于 binlog/WAL 做一次 PITR 恢复 | 误删表后恢复到删除前一秒 |
| 能制定一份全量+增量+日志归档的备份策略 | 包含 RPO/RTO 目标与演练频率 |
建议学时:
- 全职:1.5–2 周,约 40 小时。三种日志 10 小时、两阶段提交 6 小时、崩溃恢复 6 小时、逻辑/物理备份 8 小时、PITR 8 小时、策略与演练 4 小时。
- 在职:3–4 周,约 45 小时。PITR 必须在测试环境亲手做一次,不要只看文档。
学时分配:redo/undo/binlog 30%、两阶段提交与刷盘参数 20%、PG WAL/checkpoint 15%、备份工具 15%、PITR 15%、备份策略 5%。
三、先修自检
| 先修项 | 合格标准 | 不合格时回到 |
|---|---|---|
| 事务与 MVCC | 知道 undo 支持回滚与版本链 | 03.4 阶段 3 |
| 索引 | 知道数据页 16KB | 03.3 阶段 2 |
| 容器环境 | MySQL 8 与 PG 16 容器在跑 | 03.1 阶段 0 |
| 文件系统 | 知道什么是 fsync、操作系统页缓存 | 01.1 操作系统 |
| SQL 基础 | 能 CREATE/DROP/INSERT | 03.2 阶段 1 |
经验判断:日志与备份是"看起来枯燥但事故时救命"的板块。亲手做一次"删表 → PITR 恢复",比读十遍文档都记得牢。
四、知识地图
| 模块 | 知识点 | 掌握程度 | 对应题库章节 |
|---|---|---|---|
| redo log | WAL、循环写、crash-safe、刷盘参数 | 能讲清原理 | 05.2 MySQL |
| undo log | 回滚、MVCC 版本链、purge | 能讲清原理 | 05.2 MySQL |
| binlog | statement/row/mixed、位点、GTID | 能讲清原理 | 05.2 MySQL |
| 两阶段提交 | prepare/commit、组提交 | 能讲清原理 | 05.2 MySQL |
| PG WAL | WAL 记录、checkpoint、full_page_writes | 能讲清原理 | 05.3 PostgreSQL |
| 逻辑备份 | mysqldump、pg_dump、一致性快照 | 会用 | 05.2 MySQL |
| 物理备份 | XtraBackup、pg_basebackup | 了解 | 05.2 MySQL |
| PITR | 基于 binlog/WAL 恢复到时间点 | 会用 | 05.3 PostgreSQL |
进度管理建议:每个参数的默认值与丢数据边界要记牢,这是面试高频。术语见 03.12 术语表。
五、分模块精讲
5.1 redo log:InnoDB 的 crash-safe 日志
为什么需要它:InnoDB 修改数据时先改内存缓冲池的页(脏页),不是立刻写磁盘。如果此时宕机,内存脏页丢失。redo log 记录"对某页做了什么物理修改",崩溃后重放,把已提交事务的修改重做出来。
核心机制:
- redo log 是 WAL(Write-Ahead Logging):先写日志,再写数据页。日志顺序写磁盘,比随机写数据页快。
- redo log 文件固定大小、循环写(如
ib_logfile0/1,MySQL 8 为#innodb_redo),写满回覆盖。 innodb_flush_log_at_trx_commit(默认 1):- 1:每次事务提交都把 redo log 刷到磁盘(fsync),最安全,宕机不丢已提交事务。
- 2:每次提交写到 OS 页缓存,每秒 fsync;宕机丢 1 秒。
- 0:每秒刷一次;事务提交不触发;宕机丢 1 秒。
- 写入流程:修改缓冲池页 → 生成 redo log 写入 log buffer → 提交时按参数刷盘 → 后台线程异步把脏页刷到数据文件。
- 组提交:多个事务的 redo log 合并一次 fsync,提升吞吐。
最小可运行示例:
-- 查看参数
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
-- 默认 1
SHOW VARIABLES LIKE 'innodb_log_file_size%';
-- 崩溃恢复实验:
-- 1. 启动容器,建表插入数据
-- 2. COMMIT 后立即 docker kill(模拟断电,不是 docker stop)
-- 3. 重启容器:redo log 自动重放,已提交数据在
-- 4. 把 innodb_flush_log_at_trx_commit=2 后重复,会发现可能丢 1 秒内事务
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
设 =2 或 =0 后宕机丢数据 |
提交只到 OS 缓存 | 生产保持 1 |
| redo log 写满、应用卡住 | innodb_log_file_size 太小 |
调大到 1–4GB(8.0 支持在线调) |
| 磁盘 IO 高 | 每次提交 fsync | 业务批量提交;组提交自动优化 |
面试考法:"redo log 作用""innodb_flush_log_at_trx_commit 各值含义"。要点:WAL、崩溃重放、=1 安全、=0/2 丢 1 秒。
5.2 undo log 与版本链回收
为什么需要它:undo log 有两个作用:事务回滚时反向操作;MVCC 旧版本(阶段 3)。它不能无限增长——长事务会让 undo 无法清理,最终表空间暴涨。
核心机制:
- 每条 UPDATE/DELETE 在 undo log 记录旧行,形成版本链(阶段 3)。
- 事务 ROLLBACK 时用 undo 反向恢复。
- 已提交且没有任何 Read View 需要的 undo 由 purge 线程清理。
- 长事务持有 Read View,阻碍 purge,undo 表空间持续增长。
- MySQL 8 引入 undo 表空间独立、可在线扩展。
最小可运行示例:
-- 查看长事务
SELECT trx_id, trx_started, trx_query,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS dur
FROM information_schema.INNODB_TRX
ORDER BY dur DESC;
-- undo 表空间
SHOW VARIABLES LIKE 'innodb_undo%';
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| undo 表空间涨到几十 GB | 长事务阻碍 purge | 杀长事务;拆小事务 |
| 大事务回滚慢 | undo 要反向重做 | 避免大事务;拆分 |
| 复制延迟与 undo 有关 | 从库长 SQL | 见阶段 5 |
面试考法:"undo log 作用""长事务为什么危险"。要点:回滚 + MVCC 版本链;长事务阻碍回收。
5.3 binlog:三种格式与刷盘参数
为什么需要它:binlog 是 Server 层的逻辑日志(不是 InnoDB 独有),主要用于主从复制与 PITR。它记录"做了什么逻辑修改",而不是物理页修改。格式选错会导致主从数据不一致。
核心机制:
- binlog 在 Server 层,所有存储引擎共用。
- 三种格式:
- statement(SBR):记录原始 SQL,日志小,但
NOW()、UUID()、LIMIT更新在从库可能不一致。 - row(RBR):记录每行的修改前后值,日志大但准确,MySQL 8 默认。主从复制推荐。
- mixed:混合,默认用 statement,不确定时自动切 row。
- statement(SBR):记录原始 SQL,日志小,但
sync_binlog(默认 1,MySQL 8):- 0:依赖 OS 刷,宕机可能丢。
- 1:每次事务提交 fsync binlog,最安全。
- N:每 N 次提交刷一次。
- 安全组合:
innodb_flush_log_at_trx_commit=1+sync_binlog=1(双 1),崩溃不丢已提交事务。 - binlog 位点(file + pos)或 GTID(全局事务 ID)用于定位与恢复。
最小可运行示例:
-- 查看
SHOW VARIABLES LIKE 'binlog_format'; -- ROW
SHOW VARIABLES LIKE 'sync_binlog'; -- 1
SHOW VARIABLES LIKE 'log_bin'; -- ON
-- 查看当前 binlog 位点
SHOW MASTER STATUS;
-- 列出所有 binlog
SHOW BINARY LOGS;
-- 解析 binlog
-- mysqlbinlog --start-datetime="2026-01-01 10:00:00" \
-- --stop-datetime="2026-01-01 10:05:00" \
-- /var/lib/mysql/bin.000001 | mysql -uroot shop
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| statement 格式主从数据不一致 | NOW()/UUID() 在从库重放结果不同 |
改 row 格式 |
| row 格式 binlog 文件巨大 | 每行修改都记录 | 合理;大事务拆分 |
| 设 sync_binlog=0 宕机丢事件 | 依赖 OS 刷 | 生产保持 1(双 1) |
| 误删后找不到恢复位点 | 没记录 MASTER STATUS | 备份时记录位点 |
面试考法:"三种 binlog 格式区别""双 1 是哪两个参数"。要点:statement 小但不确定、row 准确、mixed;双 1 = innodb_flush_log_at_trx_commit=1 + sync_binlog=1。
5.4 两阶段提交与组提交
为什么需要它:InnoDB 有 redo log,Server 层有 binlog。如果事务在写 redo 后、写 binlog 前宕机,主库事务回滚但从库可能收到 binlog——主从不一致。两阶段提交(2PC)解决这个原子性问题。
核心机制:
- 阶段一(prepare):InnoDB 写 redo log,事务标记为 prepare 状态,此时还没提交。
- 阶段二(commit):Server 写 binlog,然后 InnoDB 把事务改为 commit 状态。
- 崩溃恢复时:
- 如果 redo 里有 prepare 且 binlog 里有该事务 → 提交(commit)。
- 如果 redo 有 prepare 但 binlog 里没有 → 回滚。
- 组提交:多个事务的 binlog 合并一次 fsync,降低刷盘次数。
最小可运行示例:
-- 崩溃恢复时 InnoDB 扫描 redo:
-- 1. 遇到 prepare 事务,查 binlog 是否存在该 xid
-- 2. 存在则 commit,不存在则 rollback
-- 这保证主库与从库(基于 binlog)一致
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 主从切换后数据不一致 | binlog 与 redo 不一致 | 保持双 1;定期校验 |
| 组提交延迟 | 单事务 fsync 开销 | 调大 binlog 组提交窗口(生产自动) |
面试考法:"两阶段提交解决什么问题""崩溃恢复怎么判断提交还是回滚"。要点:保证 redo 与 binlog 原子;prepare + binlog 存在则提交。
5.5 PostgreSQL WAL、checkpoint 与 full_page_writes
为什么需要它:PG 的 WAL(Write-Ahead Log)等价于 MySQL 的 redo log,但 PG 没有独立的 binlog,WAL 同时承担崩溃恢复与流复制。checkpoint 控制脏页刷盘节奏。
核心机制:
- WAL 记录(WAL record)描述对数据页的物理修改,先写 WAL 再写数据页。
wal_level:minimal(只崩溃恢复)、replica(默认,支持流复制)、logical(支持逻辑复制)。- checkpoint:把所有脏页刷到数据文件,并写一个检查点 redo 位置;崩溃后从该位置重放。
full_page_writes(默认 on):checkpoint 后第一次修改某页时,把整页写入 WAL。防止"部分页写断裂"(torn page)——磁盘页 16KB/8KB,写一半断电。wal_buffers、checkpoint_timeout(默认 5min)、max_wal_size(默认 1GB)控制节奏。- WAL 段文件默认 16MB,命名
000000010000000000000001等。
最小可运行示例:
SHOW wal_level; -- replica
SHOW full_page_writes; -- on
SHOW checkpoint_timeout; -- 5min
SHOW max_wal_size; -- 1GB
-- 强制 checkpoint
CHECKPOINT;
-- 查看 WAL 位置
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0');
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| WAL 目录暴涨 | 从库延迟或归档未配置 | 配 archive_mode;检查复制 |
| 频繁 checkpoint 影响写入 | max_wal_size 太小 | 调大到 4–8GB |
| 关了 full_page_writes 后崩溃页损坏 | 没有整页写保护 | 保持 on(除非存储保证原子写) |
| wal_level=minimal 无法建从库 | 没开复制 | 改 replica/logical |
面试考法:"WAL 与 MySQL redo 对应关系""checkpoint 作用""full_page_writes 为什么"。要点:WAL 同时做崩溃恢复与复制;checkpoint 定重放起点;full_page_writes 防 torn page。
5.6 逻辑备份:mysqldump 与 pg_dump
为什么需要它:逻辑备份导出 SQL/CSV,跨版本兼容、可选择性恢复,但大库慢、锁风险高。是最常用也是最容易被误用的备份方式。
核心机制:
mysqldump:--single-transaction(InnoDB):在一个事务里一致性快照,不锁表(推荐)。--master-data=2:记录 binlog 位点到注释里。--all-databases/--databases shop选择范围。- 大库(百 GB+)慢,适合中小库与迁移。
pg_dump:-Fc自定义压缩格式,可选择性恢复(pg_restore)。-d shop指定库。-j N(pg_dumpall)并行。pg_dumpall导出全集群(角色、表空间)。
- 一致性:InnoDB 用 MVCC 快照;MyISAM 要锁表。
最小可运行示例:
# MySQL 逻辑备份(一致性快照)
docker exec mysql8 mysqldump -uroot -p'Root@123' \
--single-transaction --master-data=2 --routines --triggers \
shop > shop_backup.sql
# 恢复
docker exec -i mysql8 mysql -uroot -p'Root@123' shop < shop_backup.sql
# PostgreSQL 逻辑备份(压缩格式)
docker exec pg16 pg_dump -U postgres -Fc -d shop -f /tmp/shop.dump
# 选择性恢复(只恢复某张表)
docker exec -i pg16 pg_restore -U postgres -d shop -t orders /tmp/shop.dump
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 备份时锁表 | 没用 --single-transaction | InnoDB 加 --single-transaction |
| 大库 mysqldump 几小时 | 逻辑导出逐行 INSERT | 大库用物理备份 XtraBackup/pg_basebackup |
| 恢复后自增 ID 不对 | 没导出 auto_increment | 加 --routines;或重建 |
| pg_dump 跨大版本失败 | 版本不兼容 | 用目标库版本的 pg_dump |
面试考法:"mysqldump 怎么不锁表""大库备份用什么"。要点:--single-transaction 一致性快照;大库用物理备份。
5.7 物理备份与 PITR
为什么需要它:物理备份直接复制数据文件与日志,快、适合大库,但恢复要对应版本。PITR(时间点恢复)基于全量备份 + 归档日志,能恢复到误操作前任意一秒,是生产数据保护的最后一道防线。
核心机制:
- MySQL 物理备份:
- Percona XtraBackup(开源):在线热备,复制 InnoDB 数据文件 + 备份 binlog 位点。
- 恢复:复制回数据目录,应用 redo log 前滚。
- PostgreSQL 物理备份:
pg_basebackup:在线基础备份,复制数据目录 + WAL。- 配合
archive_mode=on+archive_command归档 WAL 到独立目录。
- PITR 流程:
- 恢复最近一次全量物理备份到新实例。
- 把归档 WAL 按时间线回放。
- 用
recovery_target_time(PG)或mysqlbinlog --stop-datetime(MySQL)停在误操作前。
- RPO(恢复点目标):能容忍丢多少数据;RTO(恢复时间目标):多久恢复。
最小可运行示例:
# PG PITR 演练:
# 1. 配 archive_mode=on, archive_command='cp %p /archive/%f'
# 2. pg_basebackup 做全量
# 3. 业务运行,产生 WAL
# 4. 误操作:DROP TABLE orders; 记下时间
# 5. 恢复:把全量备份复制到新数据目录,配 recovery.conf:
# restore_command = 'cp /archive/%f %p'
# recovery_target_time = '2026-11-20 14:30:00' -- 误操作前
# 6. 启动 PG,自动回放 WAL 到目标时间停止
# MySQL PITR:
# 1. mysqldump --master-data=2 记录位点
# 2. 恢复全量
# 3. mysqlbinlog --start-position=<位点> --stop-datetime="误操作前" \
# bin.000001 bin.000002 | mysql -uroot
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 备份做了但恢复不了 | 从没演练过 | 定期恢复演练(每季度) |
| PITR 找不到误操作前位点 | 没记时间/位点 | 变更操作记录精确到秒 |
| WAL 没归档 | archive_command 没配 | 配归档到独立磁盘 |
| 物理备份恢复版本不匹配 | 跨版本 | 用同版本备份与恢复 |
面试考法:"备份策略怎么定""PITR 流程""RPO/RTO"。要点:全量+增量+日志归档;定期演练;RPO=丢多少、RTO=多久。
六、分层练习
6.1 复现型
| 练习 | 要求 | 验收标准 | 预计耗时 |
|---|---|---|---|
| 观察 redo 参数 | 查 innodb_flush_log_at_trx_commit、sync_binlog | 能说出双 1 默认值 | 0.5 小时 |
| 崩溃恢复实验 | COMMIT 后 docker kill,重启看数据是否在 | 已提交事务不丢 | 1 小时 |
| binlog 格式对比 | 分别用 statement/row 插入带 NOW() 的行,看日志 | 能说出 row 准确 | 1 小时 |
| 逻辑备份恢复 | mysqldump 导出 shop,删库后恢复 | 数据完整 | 1 小时 |
| PG WAL 配置 | 开 archive_mode,做一次 pg_basebackup | WAL 归档到 /archive | 1.5 小时 |
6.2 改造型
| 练习 | 要求 | 验收标准 | 预计耗时 |
|---|---|---|---|
| 刷盘参数实验 | 把 innodb_flush_log_at_trx_commit 改 2,崩溃后观察丢数据 | 能说出丢 1 秒边界 | 1 小时 |
| PITR 演练 | 误删表后用 binlog/WAL 恢复到删除前 | 订单表恢复,数据完整 | 3 小时 |
| 备份脚本 | 写一个每周全量 + 每日增量 + 每小时归档的脚本 | 有日志、有校验 | 2 小时 |
| 长事务清理 | 查 INNODB_TRX,杀掉长事务,观察 undo 下降 | undo 不再增长 | 1 小时 |
6.3 设计型
| 练习 | 要求 | 验收标准 | 预计耗时 |
|---|---|---|---|
| 备份策略 | 对一个 50GB 业务库定 RPO=5min、RTO=1h 的方案 | 包含全量/增量/归档/演练 | 2 小时 |
| 高可用与备份关系 | 说明主从复制能否替代备份 | 能说出误删会同步到从库 | 1 小时 |
| 故障复盘 | 模拟"误删表 10 分钟后发现",写恢复时间线 | 步骤可执行 | 2 小时 |
七、排障表
| 现象 | 可能原因 | 排查步骤 | 解决办法 |
|---|---|---|---|
| 宕机后已提交事务丢失 | innodb_flush_log_at_trx_commit≠1 或 sync_binlog≠1 | 查参数 | 改双 1 |
| undo 表空间暴涨 | 长事务阻碍 purge | 查 INNODB_TRX | 杀长事务 |
| 主从数据不一致 | statement 格式函数不确定性 | 看 binlog_format | 改 row |
| mysqldump 锁表 | 没用 --single-transaction | 看参数 | InnoDB 加该参数 |
| PG WAL 目录满 | 归档失败或从库延迟 | 查 pg_stat_archiver | 修 archive_command;查复制 |
| PITR 找不到目标时间 WAL | WAL 没归档或被覆盖 | 查 /archive 目录 | 提前配归档,保留期足够 |
| checkpoint 频繁 | max_wal_size 太小 | 查日志 | 调大 |
| 备份文件恢复失败 | 没做过恢复演练 | 试恢复 | 定期演练 |
八、出口条件
全部勾选后再进入 阶段 5 复制、高可用与读写分离。
- [ ] 能画出 redo/undo/binlog 三者的写入顺序与作用
- [ ] 能解释两阶段提交 prepare/commit 怎么保证 redo 与 binlog 一致
- [ ] 能说清双 1 是哪两个参数、各值的丢数据边界
- [ ] 能对比 statement/row/mixed,说明主从为什么用 row
- [ ] 能做一次 mysqldump/pg_dump 并恢复到测试库
- [ ] 能在测试环境完成一次 PITR,恢复到误删前一秒
- [ ] 能写出含 RPO/RTO 的备份策略与演练频率
自测题:
- redo log 与 binlog 有什么区别?
- 要点:redo 是 InnoDB 物理日志(页修改),崩溃重放;binlog 是 Server 层逻辑日志(行变更),用于复制与 PITR。
innodb_flush_log_at_trx_commit=2时宕机会丢多少数据?- 要点:提交到 OS 页缓存,每秒 fsync;OS 崩溃丢 1 秒,MySQL 崩溃不丢。
- 两阶段提交怎么决定崩溃后提交还是回滚?
- 要点:prepare 事务在 redo 中,若 binlog 也有该 xid 则提交,否则回滚。
- 主从复制为什么推荐 row 格式?
- 要点:statement 的 NOW()/UUID()/非确定性语句在从库重放结果不同;row 记录行前后值,准确。
- PG 的 full_page_writes 解决什么问题?
- 要点:torn page(页写一半断电);checkpoint 后首次修改整页写 WAL。
- 逻辑备份与物理备份各自适用场景?
- 要点:逻辑(mysqldump/pg_dump)跨版本、可选择性恢复、中小库;物理(XtraBackup/pg_basebackup)大库、快、要同版本。
- 只做主从复制不做备份,误删表会怎样?
- 要点:binlog 会把 DELETE 复制到从库,从库数据也被删;必须有独立备份与 PITR。