MySQL 备份与恢复:四类方案、两类事故、一份能照着敲的恢复手册
备份这件事有个荒诞的地方:做备份的人天天做,做恢复的人一年做一次——而真正决定成败的是后者。
所以这一篇不按"备份方式"来组织,按事故来组织。先说清四类备份在恢复时各自的代价,然后直接进两个场景:备机没了怎么拉起来、用户把数据删了怎么找回来。每个场景都给到能直接抄的命令。
一、先定两个数,再谈方案
恢复方案不是选出来的,是算出来的。只需要两个数:
| 业务等级 | 能丢多少(RPO) | 多久要回来(RTO) | 落到方案 |
|---|---|---|---|
| 内部系统 / 测试库 | 24 小时 | 4 小时 | 每天一次 mysqldump,每周一次恢复演练 |
| 一般业务 | 5 分钟 | 1 小时 | 每天物理全备 + binlog 持续归档 |
| 核心交易 | 秒级 | 30 分钟 | 全备 + 增量 + binlog 实时同步 + 延迟从库 + 异地副本 |
一个反直觉的结论:RPO 由 binlog 决定,不由备份频率决定。
每天全备 + binlog 全程归档,RPO 是分钟级;每小时全备但不归档 binlog,RPO 就是一小时。想清楚这一点,“要不要每小时全备一次"这类争论根本不需要发生——该问的是"binlog 有没有被完整搬到另一台机器上”。
二、四类备份:差别不在备份,在恢复
| 方式 | 代表工具 | 100G 库·备份 | 100G 库·恢复 | 恢复粒度 | 硬伤 |
|---|---|---|---|---|---|
| 逻辑备份 | mysqldump |
60–120 min | 180–300 min | 表 / 行 | 恢复要重建全部二级索引,大库等于把业务重跑一遍 |
| 逻辑备份(并行) | mydumper |
20–40 min | 150–240 min | 表 / 行 | 同上,备份快了但恢复还是慢 |
| 物理备份 | xtrabackup |
10–30 min | 15–30 min | 实例 | 必须先 --prepare;不能随便跨大版本 |
| 文件系统快照 | LVM / ZFS / 云盘 | 秒级 | 5–15 min(拷回) | 卷 | 和原库同盘同机房时只是"半个备份" |
数字是 16 核 + SSD、单实例 100G 量级的经验值,具体会浮动,但比例关系是稳的:逻辑备份的恢复耗时通常是备份耗时的 2–3 倍,物理备份的恢复耗时和备份耗时基本持平。
这就是为什么生产库最后都落在物理备份上——不是因为备份快,是因为出事的时候慢不起。
再提一句复制。主从不是备份,理由很简单:DROP TABLE 会在几百毫秒内同步到所有从库。它解决的是"机器挂了",解决不了"数据被删了"。真正能防误删的实时手段只有一个:延迟从库(SOURCE_DELAY=3600)。
三、备份:三条命令,参数得带对
3.1 逻辑备份
mysqldump -uroot -p'xxx' \
--single-transaction \
--source-data=2 \
--set-gtid-purged=OFF \
--routines --triggers --events \
--hex-blob \
--default-character-set=utf8mb4 \
--databases shop order > /backup/full_$(date +%F).sql逐条说清为什么:
--single-transaction:开一个 RR 事务拿一致性快照,不锁表。只对 InnoDB 有效,MyISAM 表还得靠--lock-tables。备份期间绝对不能有 DDL——ALTER TABLE会破坏快照的一致性,恢复出来就是错的数据。--source-data=2:MySQL 8.0.26 之前叫--master-data=2,把 binlog 文件名和位点以注释形式写进 SQL 头部。这是"能接上 binlog"的前提,不加这个参数,备份就只是个孤立的快照。--set-gtid-purged=OFF:不带 GTID 信息,恢复到任意实例都不会冲突。只有当你要用它重建复制链路时才用ON。--hex-blob:二进制列输出成0x...,避免字符集转换把数据弄坏。- 加
--flush-logs会在备份开始时切一次 binlog,让"从哪个文件开始重放"变得明确。
备份完先把位点抓出来记到备份目录里:
head -30 /backup/full_2026-11-21.sql | grep -E 'CHANGE (MASTER|REPLICATION SOURCE)'大库用 mydumper,它按表分文件,后面做单表恢复时能救命:
mydumper -u root -p xxx -B shop -o /backup/shop_2026-11-21 \
-t 8 -F 64 -G -E -R --trx-consistency-only
myloader -u root -p xxx -B shop -d /backup/shop_2026-11-21 -t 8-t 并发线程、-F 单文件 64MB、-G 记录 GTID、-E 导 events、-R 导存储过程。
3.2 物理全备与增量
# 全备,压缩 + 流式直接推到备份机(别落在本机磁盘上)
xtrabackup --backup --user=bkp --password=xxx \
--parallel=4 --compress --compress-threads=4 \
--stream=xbstream | ssh backup@10.0.0.9 "cat > /backup/full_2026-11-21.xbstream"--target-dir 和 --stream 不要同时用。
# 每日增量:只拷上次备份之后变过的页
xtrabackup --backup --user=bkp --password=xxx \
--target-dir=/backup/inc/2026-11-22 \
--incremental-basedir=/backup/full_2026-11-21合并增量时,顺序是硬要求,写反了备份就废了:
# 1. 先合并到全备,中间每一层都要 --apply-log-only
xtrabackup --prepare --apply-log-only --target-dir=/backup/full_2026-11-21
xtrabackup --prepare --apply-log-only \
--target-dir=/backup/full_2026-11-21 \
--incremental-dir=/backup/inc/2026-11-22
# 2. 最后一层不带 --apply-log-only,让 InnoDB 回滚未提交事务
xtrabackup --prepare \
--target-dir=/backup/full_2026-11-21 \
--incremental-dir=/backup/inc/2026-11-23中间层如果漏了 --apply-log-only,未提交事务会被当成已提交写进数据文件——这一步不可逆,恢复出来的库逻辑是错的,而且从表面上完全看不出来。
备份完这个文件里就是"从哪接 binlog":
cat /backup/full_2026-11-21/xtrabackup_binlog_info
# mysql-bin.000123 4567 3f9e2c11-...:1-98213
# ↑位点 ↑GTID 集合3.3 binlog 归档
这是最容易被省掉、又最不能省的一步。主库磁盘一坏,binlog 和全备一起没,全备就只是"某天凌晨那一刻"的数据。
# 备份机上每 5 分钟拉一次,只拉不删(主库自己按保留期清)
*/5 * * * * rsync -a --ignore-existing \
mysql@10.0.0.1:/data/mysql/binlog/ /backup/binlog/主库侧保留期必须留够:
SET GLOBAL binlog_expire_logs_seconds = 604800; -- 7 天8.0 用 binlog_expire_logs_seconds,expire_logs_days 已废弃。保留期要大于"全备周期 + 一次完整恢复所需时间",否则会出现"全备还在,中间那段 binlog 已经没了"——这是恢复失败最常见的原因之一。
3.4 备份完必做的两件事
记校验和。 恢复时才发现备份是坏的,等于没有备份:
md5sum /backup/full_2026-11-21.xbstream > /backup/full_2026-11-21.md5每月真恢复一次。 随便挑一份备份,恢复到隔离实例,起来跑 CHECKSUM TABLE。没恢复过的备份只能算"疑似有备份"。
四、事故一:备机(从库)没了
先算两件事再动手:要传多少数据、binlog 够不够留。
如果数据量 500G、万兆网,纯传输就要 7 分钟以上,加上 prepare 和追 binlog,一小时以内。但如果你重建到一半发现主库的 binlog 已经被 purge 掉了,那就白干。
三条路线,按条件选:
路线 A:从全备重建(最常用,也最稳)
Step 0 · 准备目标实例
这一步决定了后面所有步骤会不会白做。参数必须和源库对齐,其中三个是初始化后改不了的:
| 参数 | 为什么不能错 |
|---|---|
innodb_page_size |
页大小不同,数据文件直接读不了 |
lower_case_table_names |
只在初始化时生效,不一致会导致表名找不到 |
character_set_server / 排序规则 |
恢复后可能出现乱码或索引失效 |
另外确认 gtid_mode、enforce_gtid_consistency 与源库一致,并且给新实例一个唯一的 server_id。
Step 1 · 解压并 prepare
mkdir -p /backup/full_2026-11-21
xbstream -x -C /backup/full_2026-11-21 < /backup/full_2026-11-21.xbstream
xtrabackup --decompress --target-dir=/backup/full_2026-11-21
xtrabackup --prepare --target-dir=/backup/full_2026-11-21如果备份时带了增量,按 3.2 的顺序合并,最后一步不带 --apply-log-only。
Step 2 · 拷回数据目录
systemctl stop mysqld
rm -rf /data/mysql/*
xtrabackup --copy-back --target-dir=/backup/full_2026-11-21 --datadir=/data/mysql
chown -R mysql:mysql /data/mysql
rm -f /data/mysql/auto.cnf # ← 别漏
systemctl start mysqldauto.cnf 里存的是 server_uuid。 物理备份把它一起拷了过来,直接启动就会出现两台机器 UUID 相同——轻则复制报 1593,重则 CLONE 直接失败。删掉它会自动重新生成。
Step 3 · 设置 gtid_purged
SET GLOBAL super_read_only = OFF;
SET GLOBAL gtid_purged = '3f9e2c11-...:1-98213';这个值就是 xtrabackup_binlog_info 的第三列。不设会直接报 ERROR 1840(@@GLOBAL.gtid_purged 与 binlog 不一致),或者起了复制立刻 1236(找不到 binlog 文件)。
Step 4 · 配复制并启动
GTID 模式(推荐):
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='10.0.0.1', SOURCE_PORT=3306,
SOURCE_USER='repl', SOURCE_PASSWORD='xxx',
SOURCE_AUTO_POSITION=1,
SOURCE_CONNECT_RETRY=10, SOURCE_RETRY_COUNT=3,
SOURCE_SSL=1;
START REPLICA;非 GTID 模式,把 SOURCE_AUTO_POSITION=1 换成:
SOURCE_LOG_FILE='mysql-bin.000123', SOURCE_LOG_POS=4567,注意版本差异:8.0.23 之前语法是 CHANGE MASTER TO / MASTER_HOST=...,8.4 已经彻底移除 CHANGE MASTER。 抄老教程抄错语法是很常见的翻车点。
路线 B:CLONE 克隆(8.0.17+,最快)
如果版本一致、同机房、网络够,这是最省事的一条:不占备份空间,不用手工设位点。
-- 两边都装插件
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
-- 在接收端(新备机)执行
CLONE INSTANCE FROM 'clone_user'@'10.0.0.1':3306 IDENTIFIED BY 'xxx';克隆完成后 mysqld 会自动重启,数据目录被覆盖,gtid_executed 自动对齐。之后只剩两句话:
CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.0.0.1', SOURCE_AUTO_POSITION=1, ...;
START REPLICA;前置条件:版本完全一致(含小版本)、同操作系统与字节序、接收端 datadir 为空、账号需要 BACKUP_ADMIN 权限。
按万兆网算,100G 库纯传输约 2 分钟,实际从发起克隆到复制追平,10–20 分钟。这是三条路线里唯一能在半小时内搞定的。
路线 C:没有全备,从 binlog 灌
for f in $(ls /backup/binlog/mysql-bin.0* | sort); do
mysqlbinlog "$f" | mysql -uroot -p
done说实话这条路基本不用于重建备机。原因很直白:binlog 重放的速度比业务产生 binlog 的速度慢,灌到一半新的又产生了,永远追不上。它真正的用途是补一小段缺口,比如"昨天 22:00 到 23:00 的 binlog 丢了一段,用另一个副本的 binlog 补回来"。
起复制之后,必须确认四件事
SHOW REPLICA STATUS\G只看这四个字段,其他都可以先不看:
Replica_IO_Running和Replica_SQL_Running都是YesRetrieved_Gtid_Set与Executed_Gtid_Set相等(说明已追平,不是"正在追")Seconds_Behind_Source = 0Last_Error为空
再交叉核对一次数据一致性:
-- 主库和备机各执行一次,追平后应该完全相同
SELECT @@global.gtid_executed;非 GTID 环境用 pt-table-checksum 做主从校验。
五、事故二:用户把数据删了
第一条纪律:先保护现场
不要在主库上做任何恢复动作。
在主库上直接重放 binlog 到某个时间点,等于把整个实例退回去:误删的数据回来了,但那之后所有正常写入也一起没了——一次单表事故被你扩大成了全实例事故。而且现场被覆盖后,连"到底删了什么"都查不出来。
正确的顺序是:定位 → 在临时实例上恢复 → 只把缺的那部分导回主库。
5.1 先分清是哪一类
| 现象 | 恢复对象 | 走哪个方案 |
|---|---|---|
DELETE / UPDATE 执行错了 |
行数据(表结构还在) | 5.3 / 5.4 |
TRUNCATE / DROP TABLE |
表结构 + 数据 | 5.3(先 CREATE TABLE) |
DROP DATABASE |
整库 | 5.3 |
| 单表数据被覆盖 | 一张表 | 5.5(物理表空间) |
5.2 定位到误操作的那条事务
先把那段时间的 binlog 解出来:
mysqlbinlog --base64-output=decode-rows -vv \
--start-datetime='2026-11-21 14:25:00' \
--stop-datetime='2026-11-21 14:35:00' \
/backup/binlog/mysql-bin.000125 > /tmp/binlog_1430.txt--base64-output=decode-rows 把 ROW 格式解成可读的 ### 注释,-vv 连列类型一起打出来。
输出里长这样:
# at 1234400
#261121 14:32:08 server id 1 end_log_pos 1234480 CRC32 0x8a1f2b3c
...
# at 1234500
#261121 14:32:10 server id 1 end_log_pos 1234620 CRC32 0x1d4e5f60
### DELETE FROM `shop`.`orders`
### WHERE
### @1=88213
### @2='NO20261121001'
### @3=299.00# at 是这条事务在 binlog 里的起始位置,end_log_pos 是结束位置。你要记两个数:
- 误删事务的
# at→ 比如1234500 - 它前一条事务的
end_log_pos→ 比如1234480
重放就停在 1234480,正好停在误删前一刻。
关键:重放要用 position,不要用 datetime。
mysqlbinlog --start-datetime 的时间过滤受时区影响——binlog 记的是 UTC 时间戳,mysqlbinlog 按本机时区去解释。机器时区、time_zone 参数、容器里的 UTC 三者不一致时,差 8 小时是高频事故。datetime 只用来肉眼定位,真正下命令用 position。
5.3 方案 A:恢复到临时实例,只导回丢的表(推荐)
主库一刻不用停,临时实例上搞砸了可以重来。
# 1. 起一个隔离开的实例:3307 端口、独立 datadir
# 2. 把全备恢复进去
xtrabackup --copy-back --target-dir=/backup/full_2026-11-21 --datadir=/data/mysql3307
rm -f /data/mysql3307/auto.cnf
chown -R mysql:mysql /data/mysql3307先把临时实例设成只读,防止有人在上面乱写:
SET GLOBAL super_read_only = ON;
SET GLOBAL gtid_purged = '3f9e2c11-...:1-98213';然后重放 binlog,停在误删前:
mysqlbinlog --start-position=4567 --stop-position=1234480 \
/backup/binlog/mysql-bin.000123 \
/backup/binlog/mysql-bin.000124 \
/backup/binlog/mysql-bin.000125 | mysql -uroot -p -P3307这里有个非常容易搞错的点:--start-position 只作用于第一个文件,--stop-position 只作用于最后一个文件,中间的文件是完整重放的。所以文件必须按顺序全部列出来,不能只给最后一个。
导出那张表:
mysqldump -uroot -p -P3307 --single-transaction --no-create-info \
shop orders --where="created_at >= '2026-11-21 00:00:00'" > /tmp/orders_fix.sql--no-create-info 只出数据不出建表语句,因为主库上表结构还在。如果连表一起丢了,去掉这个参数。
导回主库:
mysql -uroot -p shop < /tmp/orders_fix.sql如果被删的主键可能已经被新数据占用,把 INSERT 换成 REPLACE:
sed -i 's/^INSERT INTO/REPLACE INTO/' /tmp/orders_fix.sql导之前先在临时实例上数一遍行数,导完在主库再数一遍。这两个数字对上了,才算恢复成功。
5.4 方案 B:binlog2sql 闪回(数据量小最快)
如果只删了几百行、涉及一两张表,不用恢复整个实例,直接生成反向 SQL:
python binlog2sql.py -h127.0.0.1 -u root -p xxx \
-d shop -t orders \
--start-file='mysql-bin.000125' \
--start-datetime='2026-11-21 14:30:00' \
--stop-datetime='2026-11-21 14:33:00' \
-B > /tmp/rollback.sql-B 生成回滚语句:DELETE 变 INSERT,UPDATE 自动交换前后值。先看一遍 SQL 再执行,尤其是 UPDATE 的回滚会带上完整的前值。
前置条件:binlog_format=ROW 且 binlog_row_image=FULL。如果是 MINIMAL,binlog 只记变了的列,反向 SQL 会缺字段,直接执行是错的。
同类工具还有 my2sql(Go 写的,解析大文件更快)。
5.5 方案 C:只恢复一张表,不碰整库
如果丢的是单表、且能接受"恢复到备份时刻"而不是"恢复到误删前一刻",物理表空间迁移最省事。
# 1. 在全备上 prepare 并导出(生成 .ibd 和 .cfg)
xtrabackup --prepare --export --target-dir=/backup/full_2026-11-21-- 2. 目标库上先保证表结构存在,然后腾空它
ALTER TABLE shop.orders DISCARD TABLESPACE;# 3. 把两个文件拷进去
cp /backup/full_2026-11-21/shop/orders.ibd \
/backup/full_2026-11-21/shop/orders.cfg /data/mysql/shop/
chown mysql:mysql /data/mysql/shop/orders.*-- 4. 挂载
ALTER TABLE shop.orders IMPORT TABLESPACE;前置条件:innodb_file_per_table=ON、表结构完全一致(列顺序、类型、索引一个都不能差)、目标表上没有外键引用。
代价要说清:这是备份时刻的数据,备份之后新增的行不在里面。要精确到秒,还是得走 5.3。
5.6 方案 D:原地 PITR,为什么基本不推荐
在主库上直接 --stop-position 重放,等于把实例退回到那个时间点,之后的所有写入全部丢失。除非是整库级灾难且业务明确同意回退,否则不要做。
真要做,先做两件事:FLUSH TABLES WITH READ LOCK 锁住写入,并把当前数据目录完整拷一份留证——那一份是唯一的现场记录。
六、恢复完必须做的三件事
- 数据校验:
CHECKSUM TABLE shop.orders EXTENDED;对比恢复前后的值;或者用pt-table-checksum做主备比对 - 业务校验:让业务方点几条关键记录(订单号、金额、状态、时间戳),技术指标对不代表业务数据对
- 写记录:谁、什么时候、恢复到哪个 position、丢了多少行。这份记录是下次复盘和改进的唯一依据
七、六个会当场翻车的细节
auto.cnf忘了删。 物理恢复后server_uuid重复,复制报 1593、克隆直接失败。删掉即好,但你要知道是它。gtid_purged没设。 ERROR 1840,或者复制起来了立刻 1236。这个值就在xtrabackup_binlog_info第三列,没有任何理由漏。--apply-log-only顺序反了。 未提交事务被写进数据文件,不可逆,而且表面完全看不出来。合并增量时,除了最后一层,每层都要带这个参数。- 用 datetime 定位 binlog。 时区差 8 小时,重放到错误位置。规则很简单:定位可以看 datetime,重放一律用 position。
- binlog 被 purge 了。 全备到误删之间缺一段,恢复必然不完整。
binlog_expire_logs_seconds要留够"全备周期 + 恢复耗时"。 - 在主库上原地恢复。 现场被覆盖,事故原因永远查不清了。永远先起临时实例。
速记:一页背下来
| 场景 / 概念 | 一句话 |
|---|---|
| RPO 由谁决定 | 不由备份频率,由 binlog 有没有被完整归档决定 |
| 选型依据 | 不看备份多快,看恢复多快 |
| 逻辑备份恢复慢在哪 | 每一行一条 INSERT + 重建全部二级索引 |
| 物理备份前提 | 必须先 --prepare;页大小、lower_case_table_names 必须一致 |
| 增量合并铁律 | 中间层都带 --apply-log-only,只有最后一层不带 |
| 位点从哪读 | xtrabackup_binlog_info:文件 / 位点 / GTID 三列 |
| 从库重建三路线 | 全备重建(稳)· CLONE(快,8.0.17+)· binlog 重放(几乎追不上) |
| 物理恢复后必做 | 删 auto.cnf、设 gtid_purged |
| 误删第一反应 | 别碰主库,先起临时实例 |
| 定位误删 | mysqlbinlog --base64-output=decode-rows -vv,记两个 position |
| 重放停在哪 | 停在误删事务的前一条事务的 end_log_pos |
| 多文件重放 | --start-position 只管第一个文件,--stop-position 只管最后一个 |
| 小量数据救急 | binlog2sql -B 生成反向 SQL,要求 ROW + FULL |
| 单表恢复 | --prepare --export → DISCARD → 拷 .ibd/.cfg → IMPORT |
| 备份有效的唯一证明 | 真恢复起来过一次 |
系列导航:下一篇 ⑨ 备份恢复全景——这一篇讲"出事那天怎么敲",那一篇讲背后的地图和没有备份时的抢救顺位;往前接 ④ 常用命令与巡检(恢复前后要看的那些状态);binlog 里到底记了什么,回看 ⑥ redo log 与持久化;两台机器怎么同步、延迟从库怎么配,在 ② 主从复制。