MySQL 备份与恢复实战：mysqldump、XtraBackup、binlog 增量与时间点恢复（PITR）

MySQL 备份与恢复实战：mysqldump、XtraBackup、binlog 增量与时间点恢复（PITR）

备份这事儿有个残酷的特点：平时看起来毫无价值，出事那天决定你的去留。

更残酷的是：绝大多数团队"有备份"，但从来没验证过能不能恢复。
这篇把三种备份方式、增量策略、以及真正救命的 binlog 时间点恢复讲清楚，
顺带给出一套可以直接抄走的备份脚本和恢复演练流程。

一、先建立正确的备份心智模型

在选工具之前，先回答两个问题，它们决定一切：RPO（能容忍丢多少数据）
和RTO（多久能恢复服务）。RPO 是 5 分钟还是 0、RTO 是 2 小时还是 10 分钟，
直接决定你是"每天一备"还是"全备 + 增量 + 实时 binlog"。

然后记住一条铁律：

没有演练过的备份 = 没有备份。

1.1 三种备份层次

现实中的标准组合是：每周一次全备 + 每天一次增量 + 实时备份 binlog，
出事时按"全备恢复 → 增量回放 → binlog 追平到误操作前一秒"的顺序还原。

1.2 冷备 / 温备 / 热备

冷备最简单也最可靠，但现在基本没人能接受停服。
热备是主流，热备的核心就是"拿到一个一致性时间点 + 拿到对应的 binlog 位点"。

二、mysqldump：逻辑备份的基本盘

2.1 一条正确的全库备份命令

mysqldump-h127.0.0.1-uadmin-p'***'\--single-transaction --master-data=2--flush-logs\--routines--triggers--events--hex-blob\--default-character-set=utf8mb4 --set-gtid-purged=OFF\--all-databases|gzip>/backup/full_$(date+%F).sql.gz

每个参数的含义，一个都不能少：

⚠️--single-transaction的坑：

• 只对 InnoDB 保证一致性。库里还有 MyISAM 表的话，得改用--lock-all-tables
  （或者干脆别用 MyISAM）。
• 它靠 RR 快照，所以备份期间不要执行 DDL。DDL 不参与 MVCC 快照，
  会导致导出的数据和之后拿到的 binlog 位点对不上。这也是 DDL 要放低峰的原因之一。
• 长事务会让它卡住：快照要等所有活跃事务结束才能建立。

2.2 常见的部分备份写法

mysqldump-uadmin-p--single-transaction--databasesshop>shop.sql# 单库mysqldump-uadmin-p--single-transaction shop orders order_items>tables.sql# 指定表mysqldump-uadmin-p--no-data shop>shop_schema.sql# 只备结构# 按条件导出（归档常用）：先 EXCHANGE 出分区，再 dump 独立表mysqldump-uadmin-p--single-transaction\--where="created_at >= '2024-01-01' AND created_at < '2024-02-01'"\shop orders>orders_202401.sql

2.3 恢复

mysql-uadmin-p</backup/full_2024-06-01.sql# 全库gunzip</backup/full_2024-06-01.sql.gz|mysql-uadmin-p# 压缩过的# 单库：备份文件里没有 CREATE DATABASE 时，必须先建库再导入mysql-uadmin-p-e"CREATE DATABASE shop DEFAULT CHARSET utf8mb4;"mysql-uadmin-pshop<shop.sql

mysqldump 的致命弱点：恢复慢。
恢复 = 逐条执行 INSERT + 重建所有索引，通常是备份时间的 3~10 倍。
几百 GB 的库恢复到能用，几个小时是常态。
所以大库（> 200GB）不要用 mysqldump 做主备份，改用 XtraBackup。

需要多线程逻辑备份时用mydumper / myloader：

mydumper-uadmin-p'***'-h127.0.0.1--outputdir/backup/mydump_$(date+%F)\--threads8--compress--rows100000--trx-consistency-only myloader-uadmin-p'***'--directory/backup/mydump_2024-06-01\--threads8--overwrite-tables

三、Percona XtraBackup：物理热备的主力

3.1 它凭什么能做到热备

原理一句话：拷数据文件的同时，另起线程持续拷贝 redo log，最后用 redo 把文件"追平"到一致状态。

1. 启动一个后台进程，实时监听 redo log 变化，写入 xtrabackup_log 2. 拷贝 InnoDB 数据文件（.ibd）和系统表空间（此时文件是"不一致"的，没关系） 3. FLUSH TABLES WITH READ LOCK（短暂锁，防 DDL，同时取 binlog 位点） 4. 拷贝 .frm / 非 InnoDB 表文件 / 记录 binlog 位点 5. UNLOCK TABLES，停止 xtrabackup_log

恢复时，XtraBackup 内嵌一个 InnoDB 实例，回放 xtrabackup_log，
已提交事务应用、未提交事务回滚——过程和 InnoDB 崩溃恢复一模一样。

⚠️ 版本对应关系要记牢：

而且8.0 版本的 XtraBackup 去掉了innobackupex，直接用xtrabackup命令。

3.2 全量备份与恢复

# 备份xtrabackup--backup--user=admin--password='***'--host=127.0.0.1\--target-dir=/backup/full_$(date+%F)# 准备（prepare）= 崩溃恢复，把文件追平到一致状态xtrabackup--prepare--target-dir=/backup/full_2024-06-01# 恢复：停库 → 清空数据目录 → 拷回 → 改权限 → 启动systemctl stop mysqldrm-rf/var/lib/mysql/* xtrabackup --copy-back --target-dir=/backup/full_2024-06-01chown-Rmysql:mysql /var/lib/mysql systemctl start mysqld

想保留原备份文件就用--copy-back；想省一次拷贝用--move-back（备份会被消耗掉）。

prepare 这一步必须在恢复之前做，也可以提前做——很多团队在备份机上就 prepare 好，
这样真正故障时就只剩copy-back+ 启动，RTO 短很多。

3.3 增量备份

增量备份只拷LSN 大于上次备份的页，所以很快、很小。

# 周日：全备xtrabackup--backup--target-dir=/backup/full--user=admin--password='***'# 周一：基于全备的增量xtrabackup--backup--target-dir=/backup/inc1 --incremental-basedir=/backup/full\--user=admin--password='***'# 周二：基于上一次增量的增量xtrabackup--backup--target-dir=/backup/inc2 --incremental-basedir=/backup/inc1\--user=admin--password='***'

恢复时的 prepare 顺序是最容易搞错的地方：

# 1. 先 prepare 全备，注意 --apply-log-only（不回滚未提交事务）xtrabackup--prepare--apply-log-only --target-dir=/backup/full# 2. 依次把增量并入全备（除最后一个外都要带 --apply-log-only）xtrabackup--prepare--apply-log-only --target-dir=/backup/full --incremental-dir=/backup/inc1# 3. 最后一个增量：不带 --apply-log-only，让它做最终回滚xtrabackup--prepare--target-dir=/backup/full --incremental-dir=/backup/inc2# 4. 拷回systemctl stop mysqldrm-rf/var/lib/mysql/* xtrabackup --copy-back --target-dir=/backup/fullchown-Rmysql:mysql /var/lib/mysql systemctl start mysqld

为什么最后一个增量不能加--apply-log-only？
因为未提交事务可能在最后一个增量里才结束，提前回滚会破坏一致性。
记住口诀：除最后一次外，全部加--apply-log-only。

3.4 关键元数据文件

备份目录里有几个文件，出事了要看它们：

cat/backup/full/xtrabackup_binlog_info# mysql-bin.000042 197362 a1b2c3d4-...:1-98213

3.5 压缩、流式备份与 Clone Plugin

# 压缩备份（需额外安装 qpress），恢复前先 --decompressxtrabackup--backup--compress--compress-threads=4--target-dir=/backup/full_comp# 流式备份：直接传到备份机，不在本地落盘xtrabackup--backup--stream=xbstream --target-dir=/tmp\|sshbackup@10.0.0.9"xbstream -x -C /backup/full_$(date+%F)"

不想装 XtraBackup 的话，8.0.17+ 自带Clone Plugin也能做物理备份：

CLONELOCALDATADIRECTORY='/backup/clone_20240601';-- 本地克隆CLONE INSTANCEFROM'admin'@'10.0.0.8':3306IDENTIFIEDBY'***';-- 远程克隆

限制：克隆会清空目标目录 / 本地实例，且版本必须完全一致。
它更适合"快速建从库"，做长期备份管理不如 XtraBackup 灵活。

四、binlog：把 RPO 从"一天"压到"几乎为零"

全备只能回到"备份那个时刻"。要少丢数据，必须靠 binlog 做增量。

4.1 必须先配好这些

[mysqld] server_id = 1 log_bin = /data/mysql/mysql-bin # 8.0 默认已开启 binlog_format = ROW # 强烈建议 ROW，STATEMENT 有不确定性风险 sync_binlog = 1 # 每次提交刷盘，宕机不丢 binlog innodb_flush_log_at_trx_commit = 1 binlog_row_image = FULL # gh-ost 和闪回工具都依赖它 binlog_expire_logs_seconds = 604800 # 保留 7 天（5.7 用 expire_logs_days）

SHOWVARIABLESLIKE'binlog_expire_logs_seconds';SHOWBINARYLOGS;SHOWMASTERSTATUS;-- 8.4 起为 SHOW BINARY LOG STATUS

⚠️sync_binlog=1和innodb_flush_log_at_trx_commit=1就是常说的"双一"，
是最安全的配置，代价是每次提交多一次 fsync。金融类业务必须双一，
普通业务可以折中成sync_binlog=100+innodb_flush_log_at_trx_commit=2。

4.2 定期刷出并异地保存 binlog

FLUSH LOGS;-- 切一个 binlog，老的就可以拿去备份了（命令行：mysqladmin flush-logs）

备份 binlog 的最简做法：定时把已写完的 binlog 文件 rsync 到备份机。
更稳妥的是用mysqlbinlog --read-from-remote-server实时拉取（相当于一个轻量 binlog server）。

4.3 时间点恢复（PITR）完整流程

场景：周日 02:00 做全备，周三 14:30 有人执行了UPDATE orders SET status=0忘加 WHERE。
要恢复到 14:29。

# 1. 恢复全备（数据回到周日 02:00）systemctl stop mysqld;rm-rf/var/lib/mysql/* xtrabackup --copy-back --target-dir=/backup/full;chown-Rmysql:mysql /var/lib/mysql# 2. 先隔离业务！用 --skip-networking 启动，或改防火墙，别让应用连进来systemctl start mysqld --skip-networking# 3. 找到全备对应的 binlog 位点cat/backup/full/xtrabackup_binlog_info# mysql-bin.000042 197362# 4. 定位误操作位置（ROW 格式要 decode 才看得懂）mysqlbinlog --base64-output=decode-rows-v--start-position=197362\/data/mysql/mysql-bin.000042|grep-n-B5-A5"UPDATE \`orders\`"# 5. 假设误操作位点在 8934201，恢复到它之前（stop-position 是开区间）mysqlbinlog --start-position=197362--stop-position=8934200\/data/mysql/mysql-bin.000042 /data/mysql/mysql-bin.000043|mysql-uadmin-p# 也可以按时间：--start-datetime / --stop-datetime='2024-06-05 14:29:59'# 6. 校验数据，确认无误后再开放业务

关键注意点：一定要先隔离业务再恢复；--stop-position是开区间，要停在误操作的前一个位置；
多个 binlog 文件按顺序写在一条命令里即可；用 GTID 的库加--skip-gtids避免冲突。

4.4 只回滚一条误操作（闪回思路）

全库 PITR 太重时，可以只把误操作"反向"执行。
ROW 格式下UPDATE的 binlog 同时有 before image 和 after image，
用binlog2sql这类工具能自动生成反向 SQL：

python binlog2sql.py-h127.0.0.1-uadmin-p'***'--start-file='mysql-bin.000042'\--start-datetime='2024-06-05 14:30:00'--stop-datetime='2024-06-05 14:31:00'-B# -B 生成回滚 SQL

⚠️ 前提是binlog_row_image=FULL，否则 before image 不全，生成不了反向 SQL——这也是前文强调要设 FULL 的原因。

五、三种方案怎么选

额外强烈推荐：延迟从库（Delayed Replica）。对付"误删数据"这类人为事故，它比任何备份都快：

STOP REPLICA;-- 从库上设置延迟 1 小时CHANGEREPLICATIONSOURCETOSOURCE_DELAY=3600;STARTREPLICA;-- 出事后把从库追到误操作前一秒STARTREPLICA UNTIL SOURCE_LOG_FILE='mysql-bin.000042',SOURCE_LOG_POS=8934200;

（5.7 用的是STOP SLAVE/CHANGE MASTER TO MASTER_DELAY/START SLAVE UNTIL。）

六、一套可以直接抄的备份策略

#!/bin/bash# /usr/local/bin/mysql_backup.sh —— 周日全备，其余日子增量set-euopipefailDATE=$(date+%F);DOW=$(date+%u);BASE=/backup/mysql;USER=admin;PASS='***'if["$DOW"-eq7];then# 全备TARGET=${BASE}/full_${DATE};rm-rf"${BASE}/inc_"* xtrabackup--backup--user=${USER}--password=${PASS}--target-dir="${TARGET}"xtrabackup--prepare--target-dir="${TARGET}"# 提前 prepare，缩短 RTOelse# 增量：基于上一次增量，没有就基于全备BASEDIR=$(ls-d${BASE}/inc_*2>/dev/null|tail-1)BASEDIR=${BASEDIR:-$(ls -d ${BASE}/full_*|tail-1)}TARGET=${BASE}/inc_${DATE}xtrabackup--backup--user=${USER}--password=${PASS}--target-dir="${TARGET}"\--incremental-basedir="${BASEDIR}"firsync-a--delete/data/mysql/mysql-bin.* backup@10.0.0.9::mysql-binlog/# binlog 异地find${BASE}-maxdepth1-typed-mtime+30-execrm-rf{}\;# 保留 30 天echo"$(date)backup${TARGET}done">>/var/log/mysql_backup.log

配套的监控告警：备份是否成功（退出码 + 日志关键字）、备份文件大小是否合理
（突然变小通常是出了大问题）、备份耗时趋势、最近一次成功备份距现在多久（超过 48 小时必须告警）。

七、恢复演练：别跳过这一步

每季度至少做一次：找一台空闲机器装同版本 MySQL → 用最近的备份完整恢复（含增量 + binlog）→
记录总耗时，这就是你的真实 RTO→ 抽样校验数据 → 把 RTO 写进文档跟业务容忍度对齐。

CHECKSUMTABLEorders;-- 校验用SELECTCOUNT(*),MAX(id),MAX(created_at)FROMorders;

⚠️ 演练中最常发现的问题：备份脚本里的密码早就过期了（一直在"假备份"）、
备份目录磁盘满了只有空文件、增量链的--incremental-basedir指错导致链断、
恢复后才想起备份机的 MySQL 版本和数据目录不兼容。

八、常见误区

误区 1：有了主从复制就不用备份。——主库上一条DROP TABLE毫秒级同步到从库，复制不是备份，它只保证冗余，不防误操作。

误区 2：备份文件生成了就等于备份成功。——没恢复过就不知道它是否可用，"备份了 3 年第一次恢复发现全是空文件"是真实案例。

误区 3：binlog 随便设个过期时间就行。——binlog 保留时间必须≥ 全备周期；7 天一次全备却只留 3 天 binlog，中间 4 天就是恢复不了的空洞。

误区 4：--single-transaction万能。——库里有 MyISAM 表、或备份期间有 DDL，一致性就不成立。

误区 5：XtraBackup 增量 prepare 时全部加--apply-log-only。——最后一次不能加，否则未提交事务不回滚。

误区 6：恢复完直接开放业务。——先隔离校验再放流量，恢复现场被二次污染是真实发生过的。

误区 7：备份和原始数据放同一块盘。——磁盘一坏两边一起没。遵循3-2-1 原则：3 份副本、2 种介质、1 份异地。

小结

• RPO 和 RTO 决定备份方案，先跟业务对齐这两个数字再谈工具
• 三种备份层次：逻辑备份（mysqldump/mydumper）、物理备份（XtraBackup/Clone）、binlog 增量
• mysqldump 必备参数：--single-transaction --master-data=2 --flush-logs --routines --triggers --events --hex-blob
• mysqldump 的弱点是恢复慢（要重放 SQL + 建索引），大库请用 XtraBackup
• XtraBackup 靠"拷数据文件 + 并行追 redo"实现热备，恢复时 prepare 相当于一次崩溃恢复
• 增量 prepare 口诀：除最后一次外，全部加--apply-log-only
• 出事后看xtrabackup_binlog_info拿位点，再用mysqlbinlog --start-position/--stop-position追平
• binlog 才是把 RPO 压到秒级的关键：binlog_format=ROW+binlog_row_image=FULL+ 双一 + 过期时间 ≥ 全备周期
• 人为误删的克星是延迟从库（SOURCE_DELAY）和 binlog 闪回工具
• 复制不是备份；备份必须异地；必须定期做恢复演练并测出真实 RTO

下一篇聊生产关键参数调优——备份决定了你能不能活下来，参数决定了你能不能跑得稳。