MySQL 备份方案对比与选型

来源：运维派 | 发布日期：2026-06-08 原文：https://mp.weixin.qq.com/s/yWlqhlOV2u2QBkAam-gAOw

---

一、备份的核心概念

三个维度

维度	选项	说明
类型	物理 vs 逻辑	物理=拷数据文件；逻辑=导出 SQL
范围	全量 vs 增量 vs 差异	增量=上次备份以来；差异=上次全量以来
模式	在线 vs 离线	在线=运行中备份；离线=停库，SET GLOBAL innodb_fast_shutdown=0 建议先刷脏页

一致性点

备份的核心是"一致性"——不同时刻的数据不能拼在一起恢复。

机制	说明	适用场景
FLUSH TABLES WITH READ LOCK	全库读锁，阻塞所有写	mysqldump 无 --single-transaction 时
--single-transaction	InnoDB 一致性快照，不阻塞 DML	InnoDB 库首选，不支持 MyISAM
XtraBackup redo 追赶	拷贝数据文件 + 持续拷贝 redo，追到一致点	物理热备
LVM/ZFS 快照	读锁瞬间拍快照，文件系统层一致性	大库秒级快照
LOCK INSTANCE FOR BACKUP	MySQL 8.0，备份期间允许 DML、阻塞 DDL	需 BACKUP_ADMIN 权限

备份元数据

每次备份必须记录"对应哪一刻"：

• binlog 位点: binlog.000123:456789（文件名 + 位点）
• GTID: 3E11FA47-...:1-123456
• LSN: XtraBackup 的 xtrabackup_checkpoints 中的 from_lsn / to_lsn

恢复时靠位点追 binlog 实现 PITR。

---

二、工具对比总览

工具	类型	锁	并发	增量	压缩	适用规模
mysqldump	逻辑	无*	单线程	否	管道 gzip	< 50 GB
mysqlpump	逻辑	无*	多线程（按 schema）	否	管道 gzip	10~100 GB
mydumper	逻辑	无*	多线程（按 chunk）	否	内建	100~500 GB
XtraBackup	物理	极短	多线程	✅ 是	qpress/zstd	100 GB ~ 10 TB
mariabackup	物理	极短	多线程	✅ 是	qpress	MariaDB
LVM 快照	物理	短	—	取决于 FS	—	单机大库
云盘快照	物理	短	—	云盘自动	—	云上

*配合 --single-transaction 或 --use-savepoints 时无锁。

---

三、mysqldump 实战要点

核心命令

mysqldump -h127.0.0.1 -P3306 -uroot -p \
  --single-transaction \          # InnoDB 一致性快照（不加则逐表锁）
  --routines \                    # 导出存储过程、函数
  --triggers \                    # 导出触发器（默认即有）
  --events \                      # 导出事件调度器
  --master-data=2 \               # 以注释记录 binlog 位点（=1 会带 FTWRL 锁）
  --set-gtid-purged=OFF \         # OFF=纯数据；ON=真正设置 GTID_PURGED
  --default-character-set=utf8mb4 \
  --max-allowed-packet=512M \     # 大表需要
  --databases db1 db2 \
  | gzip > /backup/full_$(date +%Y%m%d_%H%M%S).sql.gz

参数详解

参数	作用	场景
--single-transaction	START TRANSACTION WITH CONSISTENT SNAPSHOT，不阻塞 DML	InnoDB 库必加
--master-data=2	注释形式记录 binlog 位点（不持锁）	PITR 恢复
--master-data=1	写位点 + FTWRL 锁	生产慎用
--dump-slave	从从库导出，记录主库位点	从库做备份给新从库初始化
--set-gtid-purged=ON	写入 SET @@GLOBAL.GTID_PURGED	GTID 模式新从库 init
--set-gtid-purged=COMMENTED	写注释，不真正设置	备份跨实例迁移

备份机器和恢复机器字符集要一致，时区也要注意——TIMESTAMP 类型在跨时区恢复时可能解释错误。

完整备份脚本

#!/usr/bin/env bash
# /opt/scripts/mysql_full_backup.sh
set -euo pipefail
BACKUP_DIR="/data/backup/mysql"
LOG_DIR="/var/log/mysql_backup"
DATE=$(date +%Y%m%d_%H%M%S)
DAY=$(date +%Y%m%d)
KEEP_DAYS=7

mkdir -p "${BACKUP_DIR}/${DAY}" "${LOG_DIR}"
exec >"${LOG_DIR}/full_${DATE}.log" 2>&1
echo "[$(date +%F %T)] start full backup"

mysqldump \
  --user=backup_user \
  --single-transaction \
  --routines --triggers --events \
  --master-data=2 \
  --set-gtid-purged=OFF \
  --default-character-set=utf8mb4 \
  --max-allowed-packet=512M \
  --all-databases \
  | gzip > "${BACKUP_DIR}/${DAY}/full_${DATE}.sql.gz"

echo "[$(date +%F %T)] end full backup"
find "${BACKUP_DIR}" -mindepth 1 -maxdepth 1 -type d -mtime +${KEEP_DAYS} -exec rm -rf {} \;

恢复方式

gunzip -c /backup/full_20260608.sql.gz | mysql -uroot -p           # 全库
gunzip -c full_20260608.sql.gz | mysql -uroot -p db1               # 单库

⚠️ 常见陷阱

陷阱	后果	预防
没加 --single-transaction	跨表数据不一致	必加，全 InnoDB 是前提
MyISAM 混用	快照不支持 MyISAM	生产库全 InnoDB，或 --lock-all-tables
大库 > 100 GB	单线程瓶颈，备份耗时超 6 小时	切 XtraBackup 或 mydumper
备份期间 DDL	dump 和 binlog 对不上	备份窗避开 DDL 窗口
kill -9 备份进程	大事务回滚拖死主库 IO	用 kill -15 优雅退出

---

四、mydumper — 高并发逻辑备份

mydumper 按表主键/chunk 拆分，多线程并行导出。每个线程导一个 chunk，配合 myloader 实现并行恢复。

命令

mydumper \
  --host=127.0.0.1 --port=3306 --user=backup_user \
  --threads=8 \               # 并发线程数（建议 = CPU 核数/2）
  --rows=200000 \             # 每 chunk 行数
  --chunk-filesize=128 \      # 每 chunk 文件大小（MB）
  --compress \                # gzip 压缩输出
  --outputdir=/backup/mydumper/$(date +%Y%m%d) \
  --logfile=/var/log/mydumper.log

恢复（myloader）

myloader \
  --host=127.0.0.1 --port=3307 --user=root \
  --threads=8 \
  --directory=/backup/mydumper/20260608 \
  --overwrite-tables --enable-binlog

--enable-binlog：恢复时写 binlog（默认不写），要 PITR 则必须开。

---

五、XtraBackup — InnoDB 热备事实标准

工作原理

启动后台线程持续拷贝 redo log → 拷贝所有 InnoDB 数据文件 → InnoDB checkpoint 刷脏页 → 记录最终 LSN → 停 redo 拷贝线程 → 写 xtrabackup_checkpoints、xtrabackup_binlog_info。整个过程不阻塞 DML，只在最后 FLUSH TABLES WITH READ LOCK 极短时间（秒级）锁非 InnoDB 表。

全量备份

xtrabackup --user=backup_user \
  --target-dir=/backup/xb/$(date +%Y%m%d)_full \
  --backup --parallel=8 \
  --compress --compress-threads=4

增量备份

xtrabackup --user=backup_user \
  --target-dir=/backup/xb/inc_$(date +%Y%m%d) \
  --incremental-basedir=/backup/xb/latest_full \
  --backup --parallel=4 --compress

Prepare（最关键的验证步骤）

--prepare 模拟 crash recovery：重放 redo log + undo 回滚，让备份达到一致状态。

# 全量 prepare
xtrabackup --prepare --target-dir=/backup/xb/full_20260608

# 增量合并（先 apply-log-only 合并增量，最后再 prepare 一次）
xtrabackup --prepare --apply-log-only \
  --target-dir=/backup/xb/full_20260608
xtrabackup --prepare --apply-log-only \
  --target-dir=/backup/xb/full_20260608 \
  --incremental-dir=/backup/xb/inc_20260609
xtrabackup --prepare --target-dir=/backup/xb/full_20260608

--apply-log-only：增量合并时只做 redo 不做 undo，等所有增量合完再做回滚。

完整恢复流程

# 1. prepare
xtrabackup --prepare --target-dir=/backup/xb/full_20260608

# 2. 停目标实例，备份原数据目录
mysqladmin -uroot -p shutdown
mv /var/lib/mysql /var/lib/mysql_old_$(date +%s)
mkdir /var/lib/mysql && chown mysql:mysql /var/lib/mysql

# 3. copy-back
xtrabackup --copy-back --target-dir=/backup/xb/full_20260608
chown -R mysql:mysql /var/lib/mysql

# 4. 启动
systemctl start mysqld

# 5. 追 binlog（从 xtrabackup_binlog_info 读位点）
cat /var/lib/mysql/xtrabackup_binlog_info
mysqlbinlog --start-position=456789 \
  --stop-datetime="2026-06-09 10:00:00" \
  /var/lib/mysql/binlog.000123 | mysql -uroot -p

完整脚本

#!/usr/bin/env bash
# /opt/scripts/mysql_xb_full.sh
set -euo pipefail
export XTRABACKUP_PARALLEL=8
USER=backup_user
BASE=/backup/xb
TS=$(date +%Y%m%d_%H%M%S)
TARGET=${BASE}/full_${TS}
LOG=/var/log/xb_full_${TS}.log
mkdir -p "$BASE"
exec >"$LOG" 2>&1
echo "[$(date +%F %T)] start xtrabackup full"

xtrabackup --user="$USER" \
  --target-dir="$TARGET" --backup \
  --parallel="${XTRABACKUP_PARALLEL}" \
  --compress --compress-threads=4

echo "[$(date +%F %T)] end xtrabackup full"
test -f "$TARGET/xtrabackup_checkpoints" || { echo "checkpoint missing"; exit 1; }
test -f "$TARGET/xtrabackup_binlog_info" || { echo "binlog_info missing"; exit 1; }
echo "OK"

XtraBackup 版本差异

版本	对应 MySQL	关键差异
2.4	5.5/5.6/5.7	默认 --parallel=1
8.0	8.0	--lock-ddl、--redo-log-archive，备份格式与 2.4 不兼容

用 XtraBackup 搭建从库

# 主库做全备 → prepare → rsync 到从机 → 从机启动 → 配置复制
xtrabackup --user=backup_user --backup --target-dir=/backup/clone
xtrabackup --prepare --target-dir=/backup/clone
rsync -avz /backup/clone/ root@slave:/var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql && systemctl start mysqld

---

六、binlog 备份与 PITR

三种格式

格式	优点	缺点	推荐场景
STATEMENT	体积小	NOW()/UUID() 非确定	已淘汰
ROW	精确，每行变更都记录	体积大，每天可能数百 GB	★ 推荐默认
MIXED	自动选择	不够可控	兼容场景

binlog 实时归档脚本

#!/usr/bin/env bash
set -euo pipefail
REMOTE_HOST=127.0.0.1
MYSQL_USER=backup_user
LOCAL_DIR=/backup/binlog
mkdir -p "$LOCAL_DIR"

# 实时拉取（长跑进程）
mysqlbinlog --read-from-remote-server \
  --host="$REMOTE_HOST" --port=3306 --user="$MYSQL_USER" \
  --raw --stop-never \
  --result-file="$LOCAL_DIR" \
  binlog.000001

更常见的做法：配合 cron 先 FLUSH BINARY LOGS 再拉取最近一个 binlog，或者 mysqlbinlog --read-from-remote-server + binlog consumer 进程长跑。

PITR 恢复

# 基于 binlog 位点
mysqlbinlog --start-position=456789 \
  --stop-datetime="2026-06-09 10:00:00" \
  /backup/binlog/binlog.000123 | mysql -uroot -p

# GTID 模式下
SET @@GLOBAL.GTID_PURGED='3E11FA47-...:1-123456';
# 然后追 binlog 到指定时间点

GTID 模式下跳过误操作（空事务技巧）

-- 误操作 DROP TABLE 后，注入空事务跳过该 GTID
SET GTID_NEXT='3E11FA47-...:1234';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';

✅ 必开配置

# my.cnf — 8.0 默认 ON，5.7 需要显式开启
log_bin = ON
binlog_format = ROW
expire_logs_days = 7
server_id = 1          # binlog 必须配 server_id

实例上线 checklist 必须包含 "binlog 已开启"。翻车案例：my.cnf 没配 log_bin，误删表后无 binlog 可追，数据永久丢失。

---

七、LVM / ZFS / 文件系统快照

LVM 快照

# 1. 拿全局读锁 + 记位点
mysql -uroot -p -e "FLUSH TABLES WITH READ LOCK; FLUSH LOGS; SHOW MASTER STATUS;"
# 2. 创建快照（-L 指定可用空间，满了会崩溃）
lvcreate -L 10G -s -n mysql_snap /dev/vg0/mysql_data
# 3. 释放锁
mysql -uroot -p -e "UNLOCK TABLES;"
# 4. 挂载快照并备份
mount -o ro /dev/vg0/mysql_snap /mnt/mysql_snap
tar czf /backup/lvm_$(date +%Y%m%d).tar.gz -C /mnt/mysql_snap .
umount /mnt/mysql_snap && lvremove -f /dev/vg0/mysql_snap

ZFS 快照

mysql -uroot -p -e "FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;"
zfs snapshot tank/mysql@$(date +%Y%m%d_%H%M%S)
mysql -uroot -p -e "UNLOCK TABLES;"
zfs send tank/mysql@snap | gzip > /backup/zfs_snap.gz

快照适合"拍下整个数据目录"，但 InnoDB redo log 持续写入。最好配合 xtrabackup --prepare 把一致性点的数据恢复成可用状态。

---

八、云厂商快照

实现机制

CoW（Copy-on-Write） 或 RoW（Redirect-on-Write）。云盘快照不感知 MySQL 状态，必须在业务低峰拍 + FTWRL 锁来保证一致性。

一致性陷阱

# 正确流程：锁 → 拍快照 → 解锁
mysql -e "FLUSH TABLES WITH READ LOCK; FLUSH LOGS;"
# 立刻执行云厂商快照命令
aliyun ecs CreateSnapshot --DiskId xxx
mysql -e "UNLOCK TABLES;"

云厂商快照 vs XtraBackup

维度	云盘快照	XtraBackup
备份速度	秒级（元数据快照）	取决于数据量
恢复速度	拉起新盘/回滚，分钟级	prepare + 启动
跨版本兼容	完全依赖云盘	跨大版本有要求
增量	云盘自动	显式增量
演练	拉起新实例	拉起演练库

推荐组合：XtraBackup 做应用级一致性备份 + 云盘快照做底座。

---

九、选型方案：按数据量级

数据量	推荐方案	备份频率	恢复特点
< 10 GB	mysqldump + binlog	每天全量	恢复快，< 10 分钟
10~200 GB	mydumper + binlog	每周全量 + 每日 binlog	myloader 并行恢复
200 GB ~ 1 TB	XtraBackup 全量+增量+binlog	周日全量，周一~六增量	prepare + copy-back
1~10 TB	XtraBackup 并行 + 分库分表	按库并行，binlog server	多实例并行恢复
> 10 TB	分区+多副本+对象存储	XtraBackup 全量+增量+binlog 上云	分片恢复
强 SLA	上述 + 延迟从库 + binlog server	RPO < 5min，RTO < 30min	从延迟从库即时拉起

RTO/RPO 参考

场景	RPO	RTO	最小方案
核心交易	5 分钟	30 分钟	binlog 持续归档 + 延迟从库
普通业务	1 小时	4 小时	每天全量 + binlog
非关键	24 小时	24 小时	每天全量即可

---

十、运维要点

调度

# crontab
0 3 * * 0 /opt/scripts/mysql_xb_full.sh          # 周日03:00 全量
0 3 * * 1-6 /opt/scripts/mysql_xb_inc.sh         # 周一~六03:00 增量
0 * * * * /opt/scripts/binlog_archive.sh          # 每小时 binlog 归档

备份窗口

• 业务低峰：凌晨 2~6 点
• 避开整点（和其他批量任务撞车）
• 避开大促、节假日

保留策略

• 本地保留 7 天：find /backup -type d -mtime +7 -exec rm -rf {} \;
• 异地保留 30 天：rsync + ssh find
• 冷归档 90~365 天：对象存储（S3/OSS）
• 清理脚本必须加 -mindepth 1，避免把基础目录自身误删

监控告警

# 备份失败告警
increase(mysql_backup_failure_total[1h]) > 0

# 备份盘使用率 > 85%
(node_filesystem_size_bytes - node_filesystem_avail_bytes)
  / node_filesystem_size_bytes > 0.85

# 备份耗时 P95 > 2 小时
histogram_quantile(0.95, rate(mysql_backup_duration_seconds_bucket[1h])) > 7200

备份元数据

每次备份生成 metadata.json，记录主机、实例、类型、LSN、binlog 位点、大小、耗时：

{"host":"db01","instance":"mysql3306","type":"full",
 "lsn":"12345678","binlog_file":"binlog.000123",
 "binlog_pos":456789,"size_bytes":858993459200}

---

十一、翻车案例精选

案例 1：跨表数据不一致

现象：恢复后订单表和用户表数据对不上。 根因：mysqldump 没加 --single-transaction，逐表持锁导出，表之间业务持续写入。 预防：--single-transaction 不是可选优化，是 InnoDB 的事实标准。全库 InnoDB。

案例 2：XtraBackup Prepare 磁盘满

现象：备份 800 GB 文件，目标盘剩 1.5 TB，prepare 时报 "no space left"。 根因：--prepare 产生临时文件，需额外 20~30% 空间。 预防：备份盘容量规划 = 备份大小 × 1.5。加 --use-memory=2G 限制内存。

案例 3：备份进程被 kill → 主库长事务回滚

现象：运维 kill 了跑备份的 mysqldump 进程，主库大事务回滚拖死 IO。 根因：--single-transaction 起的大事务，kill -9 导致回滚。 预防：用 kill -15 优雅退出。mysqldump 进程单独监控。

案例 4：binlog 没开，PITR 失败

现象：误删表后要恢复到误删前一刻，binlog 不存在。 根因：my.cnf 没配 log_bin，MySQL 5.7 默认 OFF。 预防：加 log_bin=ON，instance 上线 checklist 必须检查。

案例 5：备份被加密勒索

现象：备份机器上的 .sql.gz 被加密成 .enc。 预防：异地对象存储 + 备份机器网络隔离 + IAM 最小权限。

案例 6：备份齐全但恢复全军覆没

现象：全量 + 增量 + binlog 都在，但 prepare 报错、binlog 损坏、磁盘不够。 根因：备份从没演练过。没人知道"恢复一次要多久、要不要停服"。 预防：季度恢复演练，演练结论写进 Runbook，结果汇报到 SRE 周会。

备份和可恢复不是一回事。没有演练的备份方案都是"理论备份"。

---

十二、一句话选型

库小+简单 → mysqldump + binlog 库中+写多 → mydumper + binlog 库大+InnoDB → XtraBackup + binlog 库超大 → 分区 + XtraBackup + binlog server 强 SLA → 上述 + 延迟从库 + binlog server 云上 → XtraBackup 一致性备份 + 云盘快照底座

---

关联页面

页面	关联点
mysql-performance-config	MySQL 性能调优与慢查询排查
mysql-replication-guide	MySQL 主从复制与 GTID
mysql-slow-query-case-study	MySQL 慢查询案例复盘
linux-rm-rf-recovery-guide	Linux 下 rm -rf 误删文件后的应急响应与数据恢复实战指南，覆盖 ext4/xfs/btr
redis-backup-recovery	Redis 备份恢复实战（跨数据库备份方法论参考）