MySQL 备份与恢复：mysqldump、XtraBackup 与 binlog 时间点恢复

一、结论先给

没做过恢复演练的备份等于没备份。 这是数据库运维第一铁律。真出事时你面对的是老板的追问和每分钟的损失，没演练过就一定有你想不到的坑（权限、版本、字符集、磁盘）。

方案	备份速度	恢复速度	是否锁表	适用规模
`mysqldump`（逻辑）	慢	最慢（要重放 SQL）	不锁（InnoDB 加 `--single-transaction`）	< 50GB
`XtraBackup`（物理）	快	快（拷文件）	不锁	任意，大库首选
binlog（增量）	实时	需配合全量	—	所有场景必备

结论：小库 mysqldump + binlog；大库 XtraBackup 全量 + 增量 + binlog。

👉 备份留存要算容量，用磁盘容量计算器按"日增量 × 保留天数"估算，别等磁盘告警才发现备份写不进去（见 Linux 磁盘满了怎么排查）。

二、先确认 binlog 是开着的

binlog 是时间点恢复的唯一依据，没开就没有任何"恢复到删库前一秒"的可能。

SHOW VARIABLES LIKE 'log_bin';                -- ON 才行
SHOW VARIABLES LIKE 'binlog_format';          -- 必须是 ROW
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
SHOW BINARY LOGS;                             -- 当前有哪些 binlog
SHOW MASTER STATUS;                           -- 当前在写哪个文件哪个位置

开启（my.cnf，改完重启）：

[mysqld]
server_id        = 1               # 主从环境必须唯一
log_bin          = /var/lib/mysql/mysql-bin
binlog_format    = ROW             # STATEMENT 会有不确定性，MIXED 也可能出事
sync_binlog      = 1               # 每次提交都刷盘，最安全（性能有损耗）
innodb_flush_log_at_trx_commit = 1 # 双一配置，数据零丢失
binlog_expire_logs_seconds = 604800  # 保留 7 天

双一配置（sync_binlog=1 + innodb_flush_log_at_trx_commit=1）是数据安全的底线，牺牲一点性能换崩溃时不丢数据。金融类业务必开。

三、mysqldump：小库的标准做法

3.1 关键参数

mysqldump -u root -p \
  --single-transaction \        # ⭐ InnoDB 一致性快照，不锁表
  --master-data=2 \             # 记录备份时的 binlog 位置（注释形式），时间点恢复必备
  --routines --triggers --events \   # 存储过程/触发器/事件
  --set-gtid-purged=OFF \       # 未开 GTID 时用它，开了 GTID 则按需
  --default-character-set=utf8mb4 \
  app > app_$(date +%F).sql

参数含义速查：

参数	作用	不加会怎样
`--single-transaction`	一致性快照	导出期间锁表，业务卡死
`--master-data=2`	记 binlog 位点	无法做时间点恢复
`--routines`	导存储过程	恢复后函数丢失
`--quick`	不缓存整表	大表吃爆内存
`--where`	按条件导出	—

3.2 备份脚本（可直接用）

#!/usr/bin/env bash
set -euo pipefail
# /opt/scripts/mysql_backup.sh
USER=root
PASS='your_pwd'
DB=app
DIR=/data/backup/mysql
KEEP=14                     # 保留 14 天

mkdir -p "$DIR"
FILE="$DIR/${DB}_$(date +%F_%H%M).sql.gz"

mysqldump -u"$USER" -p"$PASS" \
  --single-transaction --master-data=2 \
  --routines --triggers --events --quick \
  "$DB" | gzip > "$FILE"

# 校验：文件非空且 gzip 完好
if [ ! -s "$FILE" ] || ! gzip -t "$FILE" 2>/dev/null; then
  echo "备份失败：$FILE" >&2; exit 1
fi

echo "$(date '+%F %T') 备份完成：$(du -h "$FILE" | cut -f1)"
find "$DIR" -name "${DB}_*.sql.gz" -mtime +$KEEP -delete

加进 crontab（注意重定向输出，否则邮件堆满 /var/spool/postfix/maildrop 撑爆 inode）：

0 3 * * * /opt/scripts/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

3.3 恢复

gunzip < app_2026-09-23.sql.gz | mysql -u root -p app
# 或
mysql -u root -p app < app_2026-09-23.sql

恢复慢是正常的（要重建索引），可以临时加速（仅恢复时用）：

SET GLOBAL foreign_key_checks = 0;
SET GLOBAL unique_checks = 0;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
-- 恢复完记得改回 1

四、XtraBackup：大库的物理备份

# 安装（Percona XtraBackup 8.0 对应 MySQL 8.0）
yum install -y percona-xtrabackup-80

# 全量备份
xtrabackup --backup --target-dir=/data/backup/full \
  --user=root --password='pwd' --socket=/var/lib/mysql/mysql.sock

# 准备（应用 redo log，恢复前必须做）
xtrabackup --prepare --target-dir=/data/backup/full

# 恢复：停 MySQL，拷回数据目录
systemctl stop mysqld
mv /var/lib/mysql /var/lib/mysql.bak
xtrabackup --copy-back --target-dir=/data/backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

4.1 增量备份（周日全量 + 每天增量）

# 周一增量（基于全量）
xtrabackup --backup --target-dir=/data/backup/inc1 \
  --incremental-basedir=/data/backup/full --user=root --password='pwd'

# 周二增量（基于周一）
xtrabackup --backup --target-dir=/data/backup/inc2 \
  --incremental-basedir=/data/backup/inc1 --user=root --password='pwd'

# 准备：全量先 prepare，再依次 apply 增量（除最后一个外都要加 --redo-only）
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full \
  --incremental-dir=/data/backup/inc1
xtrabackup --prepare --target-dir=/data/backup/full \
  --incremental-dir=/data/backup/inc2      # 最后一个不加 --apply-log-only

增量备份文件在 xtrabackup_checkpoints 里记着自己的 LSN，恢复顺序错了直接失败。

五、时间点恢复（PITR）：误删数据的救命流程

场景：下午 3:20 有人执行了 DELETE FROM orders; 没带 WHERE。凌晨 3 点有全量备份。

流程：全量恢复到临时实例 → 重放 binlog 到 3:19:59 → 导出误删的表 → 导回生产。

5.1 第一步：找到备份时的 binlog 位点

# 备份文件头部（因为加了 --master-data=2）
head -30 app_2026-09-23.sql.gz | gunzip | grep -i "CHANGE MASTER"
# 输出类似：
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=45678;

5.2 第二步：找到误操作的位置

# 把相关时间段的 binlog 转成 SQL 文本
mysqlbinlog --start-position=45678 \
  --stop-datetime="2026-09-23 15:20:00" \
  /var/lib/mysql/mysql-bin.000012 /var/lib/mysql/mysql-bin.000013 > replay.sql

# 定位误删语句的位置（找 DELETE 或 DROP 的 at 编号）
mysqlbinlog --base64-output=decode-rows -v \
  /var/lib/mysql/mysql-bin.000013 | grep -n -B5 "DELETE FROM"

--base64-output=decode-rows -v 是把 ROW 格式的行事件翻译成可读 SQL，没有它你看到的是一堆 base64。

5.3 第三步：重放到出事前

# 方式一：按位置
mysqlbinlog --start-position=45678 --stop-position=89234 \
  /var/lib/mysql/mysql-bin.000012 | mysql -u root -p

# 方式二：按时间（更常用）
mysqlbinlog --start-datetime="2026-09-23 03:00:00" \
  --stop-datetime="2026-09-23 15:19:59" \
  /var/lib/mysql/mysql-bin.000012 /var/lib/mysql/mysql-bin.000013 | mysql -u root -p

多个 binlog 文件要按顺序列出，mysqlbinlog 会连起来处理。

5.4 单表误删的更优解：只导出那张表

不要整个库回滚（会丢掉 3:00 到 15:20 之间所有正常业务数据），正确做法：

# 1. 在另一台机器/实例上恢复全量 + 重放 binlog 到出事前
# 2. 只导出被删的表
mysqldump -u root -p app orders > orders_recover.sql
# 3. 导回生产（先改名验证，再 RENAME 交换）
mysql -u root -p app < orders_recover.sql

生产库上导回前，先建个影子表验证数据条数对不对：

CREATE TABLE orders_check LIKE orders;
-- 导入到 orders_check，核对 COUNT(*) 和时间范围
-- 确认无误后：RENAME TABLE orders TO orders_bad, orders_check TO orders;

六、恢复演练（每季度必须做一次）

# 1. 起一个临时实例（端口 3307），数据目录独立
mkdir -p /tmp/restore && xtrabackup --copy-back --target-dir=/data/backup/full --datadir=/tmp/restore
chown -R mysql:mysql /tmp/restore
mysqld --datadir=/tmp/restore --port=3307 --socket=/tmp/restore.sock --skip-networking=0 &

# 2. 连上去验证
mysql -h 127.0.0.1 -P 3307 -u root -p -e "SHOW DATABASES; SELECT COUNT(*) FROM app.orders;"

# 3. 业务侧抽样核对最近几天的数据

演练要记录两个数字：RTO（恢复耗时）和 RPO（能恢复到哪个时间点）。老板问"最坏情况丢多少数据"时，你答的应该是 RPO，而不是"应该没问题"。

七、备份有效性校验清单

每次备份后自动检查这六项：

#!/usr/bin/env bash
# 备份校验
FILE="$1"
[ -s "$FILE" ]                    || { echo "空文件"; exit 1; }
gzip -t "$FILE" 2>/dev/null       || { echo "压缩包损坏"; exit 1; }
zcat "$FILE" | head -5 | grep -q "MySQL dump" || { echo "不是 dump 文件"; exit 1; }
zcat "$FILE" | tail -3 | grep -q "Dump completed" || { echo "导出未正常结束 ⚠️"; exit 1; }
zcat "$FILE" | grep -q "CREATE TABLE" || { echo "没有建表语句"; exit 1; }
echo "校验通过：$(du -h "$FILE" | cut -f1)"

最后一行必须有 Dump completed，这是判断导出有没有中途断掉的唯一可靠依据。

八、异地与留存策略

策略	建议
3-2-1 原则	3 份副本、2 种介质、1 份异地
本地留存	7-14 天（日常误操作够用）
异地/对象存储	30-90 天（合规 + 机房故障）
备份加密	含用户数据的备份必须加密后上传
权限	备份目录 700，密钥与备份分开放

上传对象存储：

# 备份完同步到 OSS/S3/COS
ossutil64 cp "$FILE" oss://backup-bucket/mysql/ -r
# 或用 rclone（通用）
rclone copy "$FILE" remote:mysql-backup/

九、常见误区

只备份不演练：真出事才发现备份文件损坏、版本不兼容、恢复要 8 小时。
binlog 没开或格式是 STATEMENT：无法做时间点恢复，且 STATEMENT 格式在主从下可能数据不一致。
忘了 --single-transaction：导出锁表，业务中断。
忘了 --master-data：备份文件里没有 binlog 位点，PITR 无从下手。
备份和数据库在同一块盘：盘坏了两边一起没。
误删后直接全库回滚：把误删之后的正常业务数据一起弄丢了，应该单表恢复。
恢复完不核对：恢复完必须 COUNT(*) 和抽样比对，确认数据完整。

十、几条纪律

没有恢复演练的备份不算备份，每季度演练一次并记录 RTO/RPO。
binlog 必开且 format=ROW，保留 ≥ 7 天（最好覆盖一个备份周期以上）。
小库 mysqldump --single-transaction --master-data=2，大库 XtraBackup 全量+增量。
备份文件必须校验（体积、Dump completed、gzip 完整性）。
备份与生产不同盘、不同机、至少一份异地。
误删数据先停写入/锁表，再开始恢复，避免二次破坏；先单表恢复而不是全库回滚。

十一、延伸阅读

👉 相关在线工具：磁盘容量计算 · Linux 命令速查 · 日志分析，免登录、纯前端。