LumantoBlog

Appearance

MySQL 第十章：MySQL 备份与恢复实战 ​

本章目标：掌握 MySQL 备份与恢复的核心策略，能够根据业务场景选择合适方案，并具备误删、宕机、迁移等场景下的恢复能力。

1. 为什么要做备份 ​

数据库故障不只来自“服务器坏了”，更常见是：

  • 人为误操作（误删库/表/数据）
  • 程序 Bug 批量写坏数据
  • 磁盘损坏或云盘故障
  • 安全事件（勒索、入侵）

备份的本质是：把不可避免的事故，变成可恢复的事故。

2. 备份策略总览（推荐组合） ​

生产环境常见策略：全量 + 增量（binlog）+ 定期恢复演练。

graph LR
    A[全量备份 每日/每周] --> B[保存基线]
    C[binlog持续归档] --> D[记录增量变更]
    B --> E[故障恢复]
    D --> E
    E --> F[恢复到指定时间点]

3. 逻辑备份：mysqldump ​

逻辑备份适合中小规模库、跨版本迁移、结构可读性要求高的场景。

3.1 备份单库 ​

bash
mysqldump -uroot -p --single-transaction --routines --triggers blog_db > blog_db.sql

3.2 备份多库 ​

bash
mysqldump -uroot -p --single-transaction --databases blog_db user_db > multi_db.sql

3.3 全库备份 ​

bash
mysqldump -uroot -p --single-transaction --all-databases > all_db.sql

3.4 恢复逻辑备份 ​

bash
mysql -uroot -p blog_db < blog_db.sql

--single-transaction 适用于 InnoDB，可在不锁表的情况下拿到一致性快照。

4. 物理备份（认知版） ​

物理备份是直接备份数据文件，常用于大库和高性能场景。

常见工具：

  • MySQL Enterprise Backup（商业）
  • Percona XtraBackup（开源常用）

特点：

  • 速度快、恢复快
  • 对存储和版本环境依赖更强
  • 运维门槛高于逻辑备份

5. binlog 增量备份与时间点恢复（PITR） ​

5.1 开启 binlog（示例） ​

ini
[mysqld]
log_bin=mysql-bin
binlog_format=ROW
server_id=1
expire_logs_days=7

5.2 误删恢复思路 ​

  1. 先恢复最近一次全量备份
  2. 使用 binlog 回放到误删前时刻
  3. 验证数据后再切换业务

5.3 binlog 查看（示例） ​

bash
mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000123 > binlog_123.txt

6. 实战场景：误删订单数据恢复 ​

场景：

  • 每天凌晨 02:00 做全量备份
  • 当天 15:20 误执行 DELETE FROM orders;
  • 目标：恢复到 15:19:59

处理流程：

  1. 新建临时恢复库（避免污染线上）
  2. 导入 02:00 全量备份
  3. 回放 binlog 到 15:19:59
  4. 校验订单数据完整性（行数/抽样/业务对账）
  5. 切换数据或回写线上

7. 备份一致性与锁 ​

7.1 一致性备份关键点 ​

  • InnoDB：优先 --single-transaction
  • MyISAM：可能需要锁表保证一致性

7.2 什么时候要锁表 ​

  • 混用非事务引擎
  • 需要完全强一致快照且业务允许短暂写阻塞

8. 自动化备份建议 ​

  1. 使用定时任务（Linux crontab / Windows 计划任务）自动执行备份
  2. 备份文件按日期命名并压缩
  3. 本地 + 异地双份存储（对象存储/另一机房）
  4. 记录备份日志与告警（失败及时通知）
  5. 定期清理过期备份，避免磁盘打满

示例命名：

  • blog_db_2026-04-21_020000.sql.gz

9. 恢复演练清单（非常关键） ​

很多团队“有备份但恢复不了”，根因是没演练。

建议每月最少一次恢复演练，检查：

  • 备份是否可读、可导入
  • 恢复耗时是否符合 RTO 要求
  • 可恢复数据时间点是否符合 RPO 要求
  • 恢复脚本是否一键化、文档是否最新

10. RPO / RTO 指标 ​

  • RPO（Recovery Point Objective）：最多能容忍丢多少数据（时间）
  • RTO（Recovery Time Objective）：最多能容忍停机多久

示例：

  • RPO = 5 分钟（最多丢 5 分钟数据）
  • RTO = 30 分钟（30 分钟内要恢复服务）

11. 面试高频点 ​

11.1 逻辑备份和物理备份怎么选 ​

  • 逻辑备份：简单、可读、跨版本友好
  • 物理备份：速度快，适合大库与高恢复要求

11.2 为什么只做全量备份不够 ​

全量之间的数据变更会丢失，必须结合 binlog 做增量恢复。

11.3 线上误删表你会怎么处理 ​

先止血（只读/下线写流量）→ 恢复到临时库 → 核验 → 切换。

11.4 什么是“有备份但不可恢复” ​

没有恢复演练、备份损坏未发现、版本不兼容、缺少 binlog 都会导致失败。

12. 练习题 ​

  1. 写出一条备份单库并支持一致性快照的 mysqldump 命令。
  2. 设计你自己的“全量 + binlog”备份策略（频率、保留天数、存储位置）。
  3. 描述一次误删数据后的完整恢复流程。
  4. 解释 RPO 和 RTO，并给出你项目中的目标值。
  5. 写一份简版“每月恢复演练 checklist”。

13. 本章小结 ​

  • 备份不是“导出 SQL 文件”这么简单，而是一套可验证的恢复体系。
  • 最实用策略是：全量备份 + binlog 增量 + 定期演练。
  • 真正成熟的数据库运维能力，体现在故障发生后“可控、可恢复、可复盘”。