第 16 章 备份与恢复

📖 本篇简介：备份是 DBA 的底线，也是业务出问题时最后一根救命稻草——没有备份的数据库等于在裸奔。本篇假设你已经会登录 MySQL、会建库建表、了解 binlog 的存在。学完之后，你将能熟练使用 mysqldump 做逻辑备份、说清逻辑备份与物理备份的取舍、按流程做数据恢复，并搭一套定时备份方案。本示例基于 MySQL 8.0。

目录

• 1 为什么需要备份
• 2 逻辑备份与物理备份对比
• 3 mysqldump 实战
• 4 数据恢复流程
• 5 其他备份工具简介
• 6 定时备份方案
• 7 备份策略速查表
• 8 常见坑与注意事项
• 9 练习题

1 为什么需要备份

数据库可能丢数据的原因很多：误删表（DROP TABLE、DELETE 忘 WHERE）、程序 bug 写错数据、磁盘损坏、服务器整机故障、机房火灾、勒索病毒。备份就是为了在这些极端情况下还能把数据找回来。

衡量备份方案好不好，看两个核心指标：

• RPO（Recovery Point Objective，恢复点目标）：最多能容忍丢多长时间的数据。比如每小时备一次，RPO 就是 1 小时。
• RTO（Recovery Time Objective，恢复时间目标）：最坏情况下多久能恢复服务。比如全量备份 + binlog 重放，RTO 可能是 30 分钟。

一个合格的备份方案必须先想清楚这两个数字，再去选工具。

常见事故场景里，误删数据占比最高。一条没有 WHERE 的 DELETE FROM orders 或者一次手抖的 DROP TABLE，就能让几个月的数据瞬间归零。备份方案的核心价值，就是在这种时刻给你一张"后悔药"。

-- 本示例基于 MySQL 8.0
-- 先看看 binlog 是否开启（做增量备份依赖它）
SHOW VARIABLES LIKE 'log_bin';          -- ON 表示开启
SHOW VARIABLES LIKE 'binlog_format';    -- 推荐 ROW
SHOW BINARY LOGS;                       -- 列出当前所有 binlog 文件

2 逻辑备份与物理备份对比

除了逻辑/物理这个维度，备份还可以按"是否全量"再分：

| 备份方式 | 说明 | 优缺点 |

|---|---|---|

| 全量备份 | 每次备份整库 | 恢复简单，但慢、占空间 |

| 增量备份 | 只备上次备份后变化的数据 | 快、省空间，但恢复要层层叠加 |

| 差异备份 | 备上次全量后所有变化 | 折中 |

MySQL 生态里，mysqldump 默认是全量；XtraBackup 支持全量 + 增量；binlog 本质是一种连续的增量日志。

| 维度 | 逻辑备份（mysqldump） | 物理备份（XtraBackup） |

|---|---|---|

| 本质 | 导出成 SQL 文本/CSV | 直接拷贝数据文件 |

| 备份速度 | 慢（逐行读） | 快（拷文件） |

| 恢复速度 | 慢（要重放 SQL） | 快（直接拷回去） |

| 占用空间 | 文本较小（可压缩） | 通常较大 |

| 跨版本/跨平台 | 方便 | 受版本和目录限制 |

| 是否锁表 | 不加 --single-transaction 会锁 | 几乎不锁 |

| 适用场景 | 中小库、迁移、轻量备份 | 大库、高可用、要求 RTO 短 |

一句话：小库用 mysqldump 足够，大库上 TB 级必须上物理备份 + binlog 增量。

3 mysqldump 实战

mysqldump 是 MySQL 官方自带的逻辑备份工具，在 MySQL 安装目录的 bin 下。它把库里的数据导成一份 .sql 文本，里面全是 CREATE TABLE 和 INSERT 语句。

# 备份整个数据库 shop（最常用写法）
# --single-transaction：InnoDB 下用一致性快照，不锁表
# --routines：备份存储过程/函数
# --triggers：备份触发器
# --set-gtid-purged=OFF：导出文件不写 GTID，便于在别的库导入
mysqldump -uroot -p \
  --single-transaction --routines --triggers \
  --set-gtid-purged=OFF \
  shop > shop_backup.sql

# 只备份 shop 库的 orders 和 user 两张表
mysqldump -uroot -p --single-transaction shop orders user > shop_tables.sql

# 备份 MySQL 里所有库（慎用，文件巨大）
mysqldump -uroot -p --single-transaction --all-databases > all_db.sql

# 导出成 CSV（需要 secure_file_priv 允许的目录）
# SELECT * FROM shop.orders INTO OUTFILE '/tmp/orders.csv'
#   FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

常见参数说明：

| 参数 | 作用 |

|---|---|

| --single-transaction | InnoDB 一致性快照备份，不锁表（强烈推荐） |

| --routines | 备份存储过程和函数 |

| --triggers | 备份触发器 |

| --set-gtid-purged=OFF | 关闭 GTID 导出，便于跨环境导入 |

| --where="..." | 只备份满足条件的行 |

| --no-data | 只导结构，不导数据 |

| --databases db1 db2 | 显式指定要备份的多个库 |

4 数据恢复流程

恢复就是把 .sql 文件重新喂给 MySQL 执行。

# 方式一：命令行直接导入
mysql -uroot -p shop < shop_backup.sql

# 方式二：先登录 MySQL，再 source
# mysql -uroot -p
mysql> USE shop;
mysql> SOURCE /path/to/shop_backup.sql;

如果是"误删了某张表，要恢复到误删前一刻"，完整流程是：

1. 找到误删时间点 T；

2. 用最近一次全量备份恢复出一个临时库；

3. 用 mysqlbinlog 把全量备份之后到 T 之前的 binlog 重放到临时库；

4. 从临时库把误删的表导出，再导回生产库。

# 查看某个 binlog 里的内容（找误删位置）
mysqlbinlog --base64-output=DECODE-ROWS -v /var/lib/mysql/binlog.000123 | less

# 只重放指定时间范围内的 binlog（恢复到误删前 1 秒）
mysqlbinlog --start-datetime="2026-10-07 10:00:00" \
            --stop-datetime="2026-10-07 10:30:00" \
            /var/lib/mysql/binlog.000123 | mysql -uroot -p

下面用 Python + sqlite3 演示一个"导出 SQL → 清空 → 从 SQL 恢复"的完整闭环（逻辑与 mysqldump 思路一致），并把真实输出贴出来：

# 用 sqlite3 演示"备份-恢复"闭环（与 mysqldump 思路一致）
import sqlite3

src = sqlite3.connect(":memory:")
src.execute("CREATE TABLE t(id INT, name TEXT)")
src.executemany("INSERT INTO t VALUES (?,?)", [(1, "a"), (2, "b"), (3, "c")])

# 1. 备份：把整库 DDL + 数据导出成 SQL 文本
backup_sql = []
for row in src.execute("SELECT sql FROM sqlite_master WHERE type='table'"):
    backup_sql.append(row[0] + ";")
for row in src.execute("SELECT * FROM t"):
    backup_sql.append(f"INSERT INTO t VALUES {row};")
backup_text = "\n".join(backup_sql)
print("== 备份文件内容 ==")
print(backup_text)

# 2. 模拟灾难：清空表
src.execute("DELETE FROM t")
print("== 灾难后 ==", src.execute("SELECT COUNT(*) FROM t").fetchone())

# 3. 从备份恢复
dst = sqlite3.connect(":memory:")
dst.executescript(backup_text)
print("== 恢复后 ==", dst.execute("SELECT * FROM t").fetchall())

真实运行输出（Python 3.14 实跑）：

== 备份文件内容 ==
CREATE TABLE t(id INT, name TEXT);
INSERT INTO t VALUES (1, 'a');
INSERT INTO t VALUES (2, 'b');
INSERT INTO t VALUES (3, 'c');
== 灾难后 == (0,)
== 恢复后 == [(1, 'a'), (2, 'b'), (3, 'c')]

可以看到：备份文件就是一段可重放的 SQL 文本，恢复 = 重新执行这段 SQL。

5 其他备份工具简介

| 工具 | 类型 | 特点 |

|---|---|---|

| mysqldump | 逻辑 | 官方自带，小库首选 |

| mysqlpump | 逻辑 | mysqldump 的并行版，速度更快 |

| XtraBackup | 物理 | Percona 开源，热备不锁表，大库标配 |

| MySQL Enterprise Backup | 物理 | 官方付费版，企业级支持 |

| binlog | 增量 | 配合全量备份做任意时间点恢复 |

5.1 CSV 导出：轻量单表备份

只需要备份某几张表做数据交换时，用 SELECT ... INTO OUTFILE 导出 CSV 更轻量：

-- 把 orders 表导出成 CSV（路径受 secure_file_priv 限制）
SELECT * FROM shop.orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 对应恢复语句
LOAD DATA INFILE '/tmp/orders.csv'
INTO TABLE shop.orders
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

这种方式适合跨系统数据交换，不适合整库灾备。它的优点是文件轻、导入导出快，缺点是只导数据不导表结构，也不导索引约束，恢复时要先把表结构建好。另外导出路径受 secure_file_priv 参数限制，只能写到指定目录。

生产常见组合：每天凌晨一次 XtraBackup 全量 + 实时 binlog 增量，RPO 可做到秒级。

5.2 XtraBackup 物理备份实操

XtraBackup（Percona 出品）是大库物理热备的事实标准。它直接拷贝 InnoDB 的数据文件，备份期间几乎不锁表。

# 全量备份（需要 xtrabackup 用户）
xtrabackup --user=root --password=xxx --backup --target-dir=/data/xb/full

# 准备备份（把 redo log 应用到数据文件，使其一致）
xtrabackup --prepare --target-dir=/data/xb/full

# 恢复：停库、把备份拷回数据目录、改属主、再起库
systemctl stop mysql
rsync -av /data/xb/full/ /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

物理备份的优势是恢复快——数据文件拷回去就能起库，不用重放几百 MB 的 INSERT 语句。

5.3 恢复演练流程

恢复演练不是"能导进去就行"，要按真实事故流程走一遍：

1. 找一台干净的临时实例（端口 3307）；

2. 把最近一次全量备份恢复上去；

3. 重放 binlog 到指定时间点；

4. 连上业务校验几条关键数据（订单数、用户数、对账金额）；

5. 记录恢复耗时（即真实 RTO）。

-- 恢复后做数据校验的常用 SQL
SELECT COUNT(*) FROM shop.orders;           -- 订单总数对不对
SELECT COUNT(*) FROM shop.user;             -- 用户总数
SELECT SUM(amount) FROM shop.orders WHERE status='paid';  -- 已支付总额

演练发现恢复不出来，比真出事才发现强一万倍。建议每季度至少做一次。

6 定时备份方案

Linux 下用 crontab 每天凌晨 2 点备份：

# 编辑 crontab
crontab -e

# 加一行：每天 02:00 备份 shop 库，文件按日期命名，保留 7 天
0 2 * * * /usr/bin/mysqldump -uroot -p'YourPass' \
  --single-transaction --routines --triggers \
  /data/backup/shop_$(date +\%Y\%m\%d).sql.gz

# 再写一行：每天凌晨 3 点删除 7 天前的旧备份
0 3 * * * find /data/backup -name "shop_*.sql.gz" -mtime +7 -delete

Windows 下用"任务计划程序"，定时执行一个 .bat：

@echo off
rem Windows 定时备份脚本示例
set BACKUP_DIR=D:\backup
set DATE=%date:~0,4%%date:~5,2%%date:~8,2%
mysqldump -uroot -pYourPass --single-transaction shop > %BACKUP_DIR%\shop_%DATE%.sql

关键提醒：备份完一定要定期做恢复演练，否则备份文件本身可能已经损坏，真出事时才发现恢复不出来，等于没备。

演练时还要顺便记录两个数字：恢复一份全量备份到底花了多久（这就是真实 RTO），以及重放 binlog 到指定时间点花了多久。把这两个数字写进运维文档，下次出事故时心里有数，不会手忙脚乱。

7 备份策略速查表

| 场景 | 推荐方案 | RPO | RTO |

|---|---|---|---|

| 个人小项目 < 10G | 每天 mysqldump | 24 小时 | 分钟级 |

| 中小业务 10G~100G | mysqldump 每日全量 + binlog | 秒级 | 10~30 分钟 |

| 大库 > 100G | XtraBackup 全量 + binlog 增量 | 秒级 | 30 分钟内 |

| 金融核心 | 主从 + 半同步 + 每日全量 + 实时 binlog | 接近 0 | 分钟级 |

| 命令 | 作用 |

|---|---|

| mysqldump -u root -p db > x.sql | 逻辑备份 |

| mysql -u root -p db < x.sql | 逻辑恢复 |

| mysqlbinlog binlog.000123 | 查看 binlog |

| mysqlbinlog --start/stop-datetime | 按时间点重放 binlog |

| SHOW BINARY LOGS; | 列出 binlog |

记忆要点：备份用 mysqldump，恢复用 mysql 重放 SQL，任意时间点恢复靠 mysqlbinlog 重放日志。三件套配合，覆盖绝大多数备份恢复场景。

7.1 全量 + 增量的组合策略

只靠每天一次全量备份，万一上午 10 点误删，你只能恢复到凌晨 2 点，丢 8 小时数据。生产标准做法是全量 + binlog 增量：

| 时间点 | 动作 |

|---|---|

| 每天 02:00 | 一次全量备份（mysqldump 或 XtraBackup） |

| 全天 | binlog 持续记录所有写操作 |

| 误删发生在 10:30 | 恢复到凌晨全量，再重放 binlog 到 10:29 |

这样 RPO 能压到秒级。前提是 binlog 必须开启且保留时间足够长（至少覆盖两次全量之间的间隔）。

-- 确认 binlog 保留天数（expire_logs_days 在 8.0 被 binlog_expire_logs_seconds 取代）
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
-- 8.0 默认 2592000 秒 = 30 天，一般够用

7.2 备份文件的校验

备份完不校验等于白备。两件事必做：

1. 校验文件大小：备份出来的 .sql 不能是 0 字节，不能比上次明显小一截。

2. 抽样恢复：每周挑一个备份文件，在临时实例上恢复一次，确认能导进去。

# 看备份文件大小是否合理
ls -lh /data/backup/shop_*.sql

# 在临时实例上试恢复（不影响生产）
mysql -uroot -p -P 3307 shop_test < /data/backup/shop_20261007.sql

7.3 冷备与热备

| 方式 | 是否停库 | 适用 |

|---|---|---|

| 冷备 | 停 MySQL 再拷数据文件 | 可停机的小系统，最稳但业务中断 |

| 温备 | 加全局读锁，拷文件 | 业务只读窗口 |

| 热备 | 不停机在线备份 | mysqldump --single-transaction、XtraBackup |

生产几乎都用热备。mysqldump 的 --single-transaction 就是 InnoDB 的热备。

7.4 大表备份的提速

单表上百 G 时，单个 mysqldump 进程很慢。可以：

• 用 mysqlpump 并行备份；
• 按表分库分进程并行 dump；
• 用 --where 把历史冷数据分批导出。

# 只备份最近 3 个月的订单（大表瘦身）
mysqldump -uroot -p --single-transaction \
  --where="create_time > '2026-07-01'" shop orders > orders_recent.sql

8 常见坑与注意事项

坑 1：备份文件和数据库放同一块盘。

盘坏了备份一起没。备份必须拷到异地或对象存储（OSS/S3），至少"两地三中心"。

坑 2：从不做恢复演练。

备份文件可能因为权限、版本、字符集问题恢复失败，平时不测，出事才发现就晚了。建议每月做一次恢复演练。

坑 3：mysqldump 不加 --single-transaction 导致锁表。

不加这个参数，InnoDB 也会加全局读锁，业务写入全堵。备份大库必须加 --single-transaction。

坑 4：--all-databases 备份里带着 mysql 库，恢复时把别的环境的账号也覆盖了。

恢复时如果只想恢复业务库，别拿 --all-databases 的文件直接导，先提取业务库那段。

# 从全量备份里只恢复 shop 库
# 可以用 sed/awk 截取，或更稳妥地：恢复到一个临时实例，再单独导 shop

坑 5：binlog 没开，做不了任意时间点恢复。

默认有些发行版 binlog 是关的，先 SHOW VARIABLES LIKE 'log_bin'; 确认。做备份方案前第一件事就是确认 binlog 开启且格式为 ROW。

坑 6：恢复时直接导到生产库。

恢复是高风险操作，万一恢复文件里带了旧的 DROP DATABASE，会把现在的数据覆盖掉。正确做法是先恢复到临时实例，校验无误后再把需要的表导回生产。

坑 7：备份脚本密码写在命令行。

mysqldump -uroot -pPassword 会把密码留在 shell 历史里，也可能被 ps 看到。用 --login-path 或配置文件存密码更安全。

# 用 mysql_config_editor 存登录凭据，命令行不再暴露密码
mysql_config_editor set --login-path=backup --user=root --host=127.0.0.1
mysqldump --login-path=backup --single-transaction shop > shop.sql

9 练习题

练习 1：mysqldump 备份 InnoDB 库时，为什么必须加 --single-transaction？

<details>

<summary>参考答案</summary>

不加该参数，mysqldump 会加全局读锁，备份期间业务无法写入，大库备份时间长会严重影响线上。加上后，它在一个一致性快照事务里读取数据，InnoDB MVCC 保证备份期间不锁写，且备份出来的数据是某个时间点的一致快照。

</details>

练习 2：逻辑备份和物理备份怎么选？

<details>

<summary>参考答案</summary>

小库（< 几十 G）、需要跨版本迁移、数据量不大，用 mysqldump 逻辑备份即可；大库（上百 G 到 TB）、要求 RTO 短、不能长时间锁表，用 XtraBackup 物理备份。生产通常是物理全量 + binlog 增量组合。

</details>

练习 3：误删了 orders 表，要恢复到误删前 1 分钟，完整步骤是什么？

<details>

<summary>参考答案</summary>

1) 用最近一次全量备份恢复出临时实例；2) 用 mysqlbinlog 把全量备份后到误删前 1 分钟之间的 binlog 重放到临时实例；3) 从临时实例导出 orders 表；4) 把 orders 表导回生产库。关键是先定位误删的 binlog 位置或时间点。

</details>

练习 4：crontab 里怎么写一个每天凌晨 2 点备份 shop 库、保留 7 天的任务？

<details>

<summary>参考答案</summary>

0 2 * * * /usr/bin/mysqldump -uroot -p'Pass' --single-transaction shop > /data/backup/shop_$(date +\%Y\%m\%d).sql
0 3 * * * find /data/backup -name "shop_*.sql" -mtime +7 -delete

</details>

练习 5：RPO 和 RTO 分别指什么？每小时备一次全量，RPO 大概是多少？

<details>

<summary>参考答案</summary>

RPO 是最多能容忍丢多长时间的数据（恢复点目标）；RTO 是最坏情况下多久能恢复服务（恢复时间目标）。每小时全量备份一次，最坏情况会丢最近 1 小时的数据，所以 RPO ≈ 1 小时。要把 RPO 压到秒级，必须依赖 binlog 实时增量。

</details>

练习 6：XtraBackup 和 mysqldump 的本质区别是什么？大库为什么选前者？

<details>

<summary>参考答案</summary>

mysqldump 是逻辑备份，把数据导成 SQL 文本，恢复时要重放所有 INSERT，大库恢复慢；XtraBackup 是物理备份，直接拷贝 InnoDB 数据文件，恢复时拷回去就能起库，快几个数量级。大库（百 G 以上）恢复时间敏感，必须用物理备份。

</details>

练习 7：为什么备份文件不能和数据库放在同一块磁盘上？

<details>

<summary>参考答案</summary>

如果盘坏了，数据库和备份一起没，备份就失去意义。备份必须拷到异地、另一块盘或对象存储（OSS/S3），做到"故障不共因"。至少遵循 3-2-1 原则：3 份副本、2 种介质、1 份异地。

</details>

练习 8：mysqldump 导出的 SQL 文件里为什么会有 DROP TABLE IF EXISTS？这是好是坏？

<details>

<summary>参考答案</summary>

mysqldump 默认会在建表语句前加 DROP TABLE IF EXISTS，这样恢复时如果表已存在会先删掉再重建。好处是恢复幂等、不会报"表已存在"；坏处是恢复到已有数据的库时会把现有表清掉。所以恢复前一定确认目标库是空的，或者只导需要的表段。

</details>

本篇小结：备份方案的核心是"全量 + binlog 增量"，mysqldump 负责全量、mysqlbinlog 负责任意时间点恢复；备份文件要异地存放，并且定期做恢复演练，否则等于没备。

>

三条铁律：备份要异地、恢复要演练、密码不进命令行。

下一篇预告：第 17 章 EXPLAIN执行计划详解