MySQL 日志、备份恢复与复制

这一章解决“误删、宕机、复制延迟后，数据能不能找回来”。示例以 MySQL 8.0 + InnoDB 为前提，所有恢复命令只能在隔离的测试实例演练；不要把恢复导入、RESET MASTER、删除 binlog 等命令直接对生产执行。

先分清三类日志

日志	记录什么	主要作用	能否直接当备份
redo log	InnoDB 页的物理变更意图	崩溃恢复，保证已提交修改能重做	不能，循环覆盖且依赖数据文件
undo log	旧版本与回滚信息	回滚、MVCC 一致性读	不能，生命周期短且会被 purge
binary log	服务器层面的逻辑事件/行变更	复制、审计和按时间点恢复	只有和完整备份配合才有 PITR

一次 UPDATE 可能先写 redo，再提交；binlog 与 redo 的提交协调由两阶段提交保证一致性边界。不要把“binlog 有记录”理解成“数据文件已经恢复”；也不要把 undo 当成可长期回溯历史的审计日志。

内存页修改 → redo 持久化 → binlog 写入/同步 → 提交确认
崩溃恢复：redo 重做已提交页；未完成事务按 undo 回滚

具体刷盘时机受 innodb_flush_log_at_trx_commit、sync_binlog、存储设备和故障类型影响。把两个参数设为 1 能缩小崩溃丢失窗口，但不能替代备份、异地副本和恢复演练。先明确 RPO（最多能丢多少时间）与 RTO（多久恢复服务）。

备份不是“导出一份 SQL”

逻辑备份与物理备份

类型	常见工具	优点	代价
逻辑	mysqldump、MySQL Shell dump	跨版本/跨平台、可选择表、便于审阅	大库恢复慢，重建索引耗时
物理	企业热备、文件系统快照、备份工具	恢复快，适合大实例	版本、文件布局和存储依赖更强
增量	binlog、物理增量工具	降低每日全量成本	链路、保留期和恢复顺序更复杂

InnoDB 的逻辑在线备份常用：

mysqldump --single-transaction --routines --events --triggers \
  --all-databases > full.sql

--single-transaction 依赖事务型表的一致性快照，避免对 InnoDB 长时间加表锁；它不保证 MyISAM、外部文件或跨实例对象一致，也不能与会产生隐式提交的 DDL 随意混用。备份期间仍可能造成 IO、CPU 和锁等待压力。

不应把 SELECT ... INTO OUTFILE 当成完整备份：它不包含表结构、权限、触发器和事务一致性。直接复制 InnoDB 数据文件也不是安全热备方案；必须使用支持 InnoDB 的物理备份或一致快照流程。

备份策略先写成政策

至少明确：

RPO：最多允许丢 5 分钟 → binlog 至少保留并持续归档
RTO：2 小时内恢复 → 评估备份大小、带宽、重建索引时间
全量：每天/每周一次
binlog：连续归档到独立存储，保留超过恢复窗口
加密、访问控制、校验和与保留期限
每月在临时实例做恢复验收

备份成功日志不等于备份可恢复。每次归档记录实例版本、GTID/位点、时间范围、校验和和密钥版本；恢复时先验证完整性再导入。

误删后的按时间点恢复（PITR）

假设每天 02:00 有全量备份，误删发生在 14:37，目标恢复到 14:36:59。流程应在新实例完成：

1. 保护现场：停止会继续写入的应用或切换到只读，记录当前时间、binlog 文件/GTID 和误操作 SQL；不要覆盖原库。
2. 找到 02:00 全量备份及其对应的 binlog 起点，验证备份校验和。
3. 启动隔离恢复实例，先导入全量备份。
4. 按顺序应用从全量起点到目标时刻之前的 binlog，过滤掉误删事务时要极其谨慎。
5. 校验受影响表、外键、计数和业务状态，再决定导出修复数据或切换实例。

示意命令（文件名、账号、时间必须替换并在测试实例确认）：

mysql --host=recovery --user=restore -p < full.sql
mysqlbinlog --read-from-remote-server \
  --start-position=12345 --stop-datetime='2026-09-05 14:36:59' \
  binlog.000123 binlog.000124 | mysql --host=recovery --user=restore -p

mysqlbinlog 的过滤方式取决于 binlog 格式、事务边界和业务是否跨表；按时间过滤可能包含同一秒的其他事务，按 GTID/精确位点通常更可控。不要凭日志时间戳直接删掉一段文本；先在副本实例回放并检查结果。

恢复后不要直接把整库覆盖回生产。常见安全路径是：在恢复库导出被误删行，人工核对主键、版本和关联关系，再通过受控、幂等的修复脚本写回；修复脚本应记录 repair_id 并审计。若业务继续写入，先评估主库与恢复库的冲突，不能简单全量替换。

binlog 查看与过滤

SHOW VARIABLES LIKE 'log_bin';
SHOW BINARY LOGS;
SHOW MASTER STATUS;
SHOW BINLOG EVENTS IN 'binlog.000123' LIMIT 20;

生产环境需相应权限。SHOW BINLOG EVENTS 适合浏览，不一定展示完整行值；需要分析行事件时在离线副本使用 mysqlbinlog --base64-output=DECODE-ROWS -vv。不要把生产敏感数据粘到公共工具。

ROW 格式通常比 STATEMENT 更能避免非确定性语句在复制/恢复中的差异，但 binlog 仍需保留表结构变更、字符集、时区和 GTID 信息。格式选择和恢复策略要统一设计。

复制：可读扩展，不是备份

经典拓扑是 source 写 binlog，replica 拉取 relay log，再由 applier 应用。复制提供读扩展和故障切换基础，但存在：

• 异步复制可能落后，source 提交后 replica 尚未应用。
• 复制错误、DDL、热点写入和单线程瓶颈会扩大延迟。
• 误删会被复制到所有副本；副本没有独立备份价值。
• 网络分区或故障切换可能带来已确认事务丢失、重复应用或读旧数据。

MySQL 8.0 支持多线程复制、并行应用与 GTID；是否启用、如何按逻辑时钟调度需结合版本和工作负载验证，不要只看一个 Seconds_Behind_Source 数字。延迟指标可能在 SQL 线程停止、网络中断或时钟异常时误导。

SHOW REPLICA STATUS\G

排查至少看：IO/receiver 线程是否运行、SQL/applier 线程是否运行、最后错误号和 SQL、source/relay 日志位置、接收与执行 GTID 集合、事务应用延迟、队列大小。MySQL 8.0.23 以前命令和字段可能显示 SLAVE 名称，升级时以当前版本文档为准。

读己之写与切换

用户刚写入后立即读 replica，可能看不到自己的更新。解决方式按业务选择：

• 写后短时间固定读 source。
• 携带 GTID/位点，等待 replica 应用到该位置。
• 对关键查询使用一致性路由或缓存。

不要用“延迟小于 1 秒”当作严格一致性保证。故障切换前要确认候选 replica 已应用到所需 GTID，并停止旧 source 接受写入，避免双主分叉。切换后的 DNS、连接池、应用重试和幂等也要一起演练。

备份副本与恢复副本

从 replica 做备份可以减轻 source 压力，但必须同时记录复制元数据、备份时的 GTID/位点及 relay 日志状态；恢复后才能安全续接。复制延迟意味着副本备份可能落后，需把备份时刻纳入 RPO 计算。

高可用系统应至少有：

source + 独立备份存储 + 可恢复的 binlog 归档
一份不同故障域的副本
监控：备份年龄、binlog 最老可用时间、复制延迟、恢复演练耗时
明确切换、回切、脑裂和数据修复负责人

RAID、云盘快照、只读副本都不能单独解决误删、逻辑损坏和区域故障。

恢复验收清单

恢复完成后检查：

1. 版本、字符集、时区、SQL mode 和插件与目标兼容。
2. 表数量、行数、主键/唯一约束、外键和索引状态。
3. 误删记录是否恢复，目标时间之后的合法更新是否按计划保留。
4. 应用健康检查、关键读写链路和权限是否正常。
5. binlog、备份任务、监控告警和归档是否重新工作。
6. 用恢复耗时和可接受数据丢失量反推 RTO/RPO 是否达标。

“数据库进程启动了”不算恢复成功；必须做业务级验收并保存证据。

本地恢复演练

只在 Docker 临时 MySQL 实例和测试数据执行：

1. 建订单表，插入三笔带时间和业务 ID 的数据。
2. 使用 mysqldump --single-transaction 做全量备份，记录备份文件哈希。
3. 开启 binlog 后再插入、更新一笔，确认能从 binlog 看到事务。
4. 在测试实例执行一条可识别的误删，记录精确时间。
5. 用全量备份恢复到另一端口，再只回放误删前的 binlog。
6. 对比恢复库与原测试库：误删行应存在，误删后的合法更新不应被错误带入。
7. 记录总耗时、备份大小、binlog 范围与每一步证据。

不要在没有备份的唯一数据目录上尝试恢复；不要为了演练删除真实数据。真正的演练应定期自动化，并测试密钥丢失、binlog 归档中断、备份损坏和副本延迟等失败路径。

官方参考

• MySQL 数据库备份方法
• MySQL 按时间点恢复
• MySQL 二进制日志
• MySQL 复制概览
• mysqldump 一致性备份

---

面试问答

1. redo log、undo log、binlog 各自是干什么的？

• redo log 记录 InnoDB 页的物理变更意图，负责崩溃恢复，保证已提交的修改能重做；循环覆盖且依赖数据文件，不能直接当备份。
• undo log 存旧版本和回滚信息，负责回滚和 MVCC 一致性读；生命周期短、会被 purge，不是长期审计日志。
• binlog 是服务器层的逻辑变更日志，用于复制、审计和按时间点恢复；要和完整备份配合才有 PITR。
• 加分：binlog 与 redo 的提交协调由两阶段提交保证一致性边界，不能把"binlog 有记录"理解成"数据文件已经恢复"。

2. 线上误删了数据，怎么恢复到误删前那一刻？

• 先保护现场：停止写入或切只读，记录当前时间、binlog 位点和误操作 SQL，不要覆盖原库。
• 找到误删前的全量备份和对应 binlog，在隔离的新实例上先导全量、再按顺序回放 binlog 到目标时刻。
• 校验受影响表、外键、计数和业务状态后，导出被误删行，人工核对再用受控、幂等的修复脚本写回，不要整库覆盖生产。
• 别踩的坑：按时间过滤 binlog 可能包含同一秒的其他事务，不要凭时间戳直接删一段日志文本，先在副本实例回放并检查结果。

3. 从库（副本）能不能当备份用？

• 不能。复制是异步的，可能落后；更关键的是误删和逻辑损坏也会被复制到所有副本，副本没有独立备份价值。
• 副本提供的是读扩展和故障切换基础，高可用结构应该是 source + 独立备份存储 + 可恢复的 binlog 归档 + 一份不同故障域的副本。
• 加分：从副本做备份可以减轻主库压力，但要同时记录复制元数据和 GTID/位点，并把备份时刻纳入 RPO 计算。

4. RPO 和 RTO 怎么决定备份策略？

• RPO 是最多允许丢多少数据：允许丢 5 分钟，就要求 binlog 至少保留并持续归档到独立存储。
• RTO 是多久恢复服务：2 小时内恢复，就要评估备份大小、带宽和重建索引时间。
• 备份成功日志不等于备份可恢复：每次归档记录实例版本、GTID/位点、校验和，每月在临时实例做恢复验收。
• 加分：innodb_flush_log_at_trx_commit 和 sync_binlog 设为 1 能缩小崩溃丢失窗口，但不能替代备份、异地副本和恢复演练。

5. mysqldump --single-transaction 是万能备份方案吗？

• 它靠事务型表的一致性快照做在线逻辑备份，避免对 InnoDB 长时间加表锁，跨版本、可选表、便于审阅。
• 局限：不保证 MyISAM、外部文件的一致性，不能与会产生隐式提交的 DDL 随意混用；大库恢复慢、重建索引耗时。
• 大实例可以选物理备份：恢复快，但版本、文件布局和存储依赖更强。
• 别踩的坑：SELECT ... INTO OUTFILE 和直接复制 InnoDB 数据文件都不是安全备份——前者没有表结构、权限、触发器和事务一致性。