罗罗编程学习记录
本页内容 展开查看

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。
  • 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 流程:
    1. 恢复最近一次全量物理备份到新实例。
    2. 把归档 WAL 按时间线回放。
    3. 用 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 的备份策略与演练频率

自测题:

  1. redo log 与 binlog 有什么区别?
    • 要点:redo 是 InnoDB 物理日志(页修改),崩溃重放;binlog 是 Server 层逻辑日志(行变更),用于复制与 PITR。
  2. innodb_flush_log_at_trx_commit=2 时宕机会丢多少数据?
    • 要点:提交到 OS 页缓存,每秒 fsync;OS 崩溃丢 1 秒,MySQL 崩溃不丢。
  3. 两阶段提交怎么决定崩溃后提交还是回滚?
    • 要点:prepare 事务在 redo 中,若 binlog 也有该 xid 则提交,否则回滚。
  4. 主从复制为什么推荐 row 格式?
    • 要点:statement 的 NOW()/UUID()/非确定性语句在从库重放结果不同;row 记录行前后值,准确。
  5. PG 的 full_page_writes 解决什么问题?
    • 要点:torn page(页写一半断电);checkpoint 后首次修改整页写 WAL。
  6. 逻辑备份与物理备份各自适用场景?
    • 要点:逻辑(mysqldump/pg_dump)跨版本、可选择性恢复、中小库;物理(XtraBackup/pg_basebackup)大库、快、要同版本。
  7. 只做主从复制不做备份,误删表会怎样?
    • 要点:binlog 会把 DELETE 复制到从库,从库数据也被删;必须有独立备份与 PITR。