MySQL 单机备份实操：mysqldump 参数调优与还原演练

  1.  Home
  2. Text  
    3. Share to：

一、为什么单机 MySQL 的备份总是「看着有、用不上」

我做过一次很不好受的统计：把自己经手过的十几个个人站备份脚本拉出来，用「能不能在另一台空机器上还原成功」这一个标准筛，超过一半是不合格的。备份文件天天生成，crontab 从来没报错，ls -lh 看上去也有几百兆，但真正到了要还原的那天，才发现 mysqldump 在每个库的建表语句里偷偷塞了一段 GTID_PURGED，或者干脆把 DEFINER=root@localhost 一起带进了触发器，结果在一台新建的 MySQL 8.0.36 上执行到第 37 条语句就报 1227，后面几千张表的数据一行都没进去。

这类问题的共同特征是：它不会在备份时暴露，只会在你最不想出问题的时候暴露。所以我把单机 MySQL 的备份做成了三件事，缺一件都不算做完：

  • 产物标准化——同一套命令、同一个目录结构、同一个命名规则，脚本化到不看文档就能跑。
  • 可验证——备份完成后立刻做一次「语法级」校验，每周做一次「真还原」演练，还原到临时实例上跑 CHECKSUM TABLE。
  • 有外部副本——本机备份只解决误删，不解决机器丢。必须有一份离开这台服务器的拷贝，用 rclone 推到对象存储。

下面这套方案我用在一台 4 核 8G、磁盘 160G 的 VPS 上，数据量 6.7GB（其中 90% 是附件表），全量逻辑备份耗时大约 3 分 40 秒，压缩后 780MB，对象存储每月成本不到一块钱。重点是每一步的坑我都写清楚了。

二、备份文件该带什么、不该带什么

先明确 mysqldump 的参数。很多人以为参数越多越保险，实际上有些参数会让备份变得不可移植。我的最终命令是这样：

mysqldump \
  --defaults-extra-file=/root/.my.cnf \
  --single-transaction \
  --quick \
  --routines \
  --triggers \
  --events \
  --default-character-set=utf8mb4 \
  --hex-blob \
  --set-gtid-purged=OFF \
  --skip-add-locks \
  --skip-disable-keys \
  --max-allowed-packet=256M \
  --no-tablespaces \
  --databases zz1984 \
  | zstd -19 -T4 -q -o /backup/mysql/zz1984-$(date +%F-%H%M).sql.zst

逐条解释几个最容易被忽略的：

--set-gtid-purged=OFF。如果目标实例开了 GTID，而 dump 文件里带着源库的 SET @@GLOBAL.GTID_PURGED，还原到已有数据的实例上会直接报错退出，提示 GTID 集合重叠。单机场景没有复制需求，这一行必须关掉。

--no-tablespaces。MySQL 8 上如果不加，执行时会去查 information_schema 的表空间信息，非 root 账号或者权限收紧后会报 PROCESS privilege 不足。5.7 时代没这问题，从 5.7 升到 8 之后很多老脚本就是死在这一行上。

--hex-blob。表里有 BLOB/BINARY 字段（比如存了序列化头像、二进制 token）时，不加这个参数导出的内容在字符集转换过程中可能被改字节。加了这个之后 BLOB 变成十六进制字面量，代价是体积涨 2 倍左右，但还原出来一定一致。

--single-transaction。InnoDB 才有效，开一个可重复读事务保证一致性快照，不锁表。前提是库里不能混着 MyISAM——如果还有 typecho_contents 之类的老表被建成了 MyISAM（Typecho 早期版本在某些默认配置下确实会这样），--single-transaction 对它们无效，备份期间写入会导致逻辑不一致。先查一遍：

SELECT table_name, engine FROM information_schema.tables
WHERE table_schema='zz1984' AND engine <> 'InnoDB';

有输出就先 ALTER TABLE ... ENGINE=InnoDB 迁过来，这张表在几百兆以内的话几分钟能搞定，之后备份才真正一致。

不要带 --all-databases。除非你确实要整机迁移，否则全库备份会把 mysql（含用户和授权）、sys、performance_schema 一起导出来。还原时 DROP DATABASE mysql 会让目标实例的账号体系被打乱，我在一次演练里就把测试机的 root 密码覆盖成了源库的，之后连不上只能靠 --skip-grant-tables 救。

压缩这里选 zstd 而不是 gzip，原因很简单：同样的 6.7GB 数据，gzip -6 单核要跑 6 分钟，zstd -19 -T4 多线程 2 分 20 秒，产物体积还小 12%。还原时 zstd -d 解压速度是 gzip 的 4 倍多，这对「恢复时间目标」影响很大。如果 CPU 只有 1 核，把 -19 降到 -12，压缩率损失不到 3%，时间省一半。

三、脚本结构：目录约定与保留策略

我见过最混乱的备份目录是 300 多个 backup_20240103.sql.gz 平铺在一起，没人知道哪个是完整的。所以第一步是把目录语义化：

/backup/mysql/
  └── daily/            # 每天的自动备份，保留 7 天
        zz1984-2026-10-19-0310.sql.zst
  └── weekly/           # 每周日从 daily 硬链接过来，保留 8 周
  └── manual/           # 手动改配置/大动作前的一次性快照，永不自动删除
  └── logs/             # 每次运行的输出与耗时，保留 90 天
  └── verify/           # 每周还原演练的报告

weekly 用硬链接（ln 而不是 cp）是刻意的：同一 inode 不占额外空间，删 daily 的时候 weekly 那份还在，磁盘利用率和「多留几份」这两个目标同时达成。但要注意 ln 只对同一文件系统有效，如果 /backup 是单独挂的盘而文件在 /var/lib/mysql，会报 Invalid cross-device link。

完整的备份脚本我写成这样，核心是「先写临时文件、成功后原子改名」，这样即使中途磁盘满了也不会留下一个半截文件被当成有效备份：

#!/bin/bash
set -Eeuo pipefail
TS=$(date +%F-%H%M)
DIR=/backup/mysql/daily
TMP="$DIR/.tmp-$TS.sql.zst"
OUT="$DIR/zz1984-$TS.sql.zst"
LOG=/backup/mysql/logs/backup-$TS.log

cleanup() {
  rc=$?
  [ $rc -ne 0 ] && rm -f "$TMP"
  echo "[$(date -Is)] exit=$rc" >> "$LOG"
  exit $rc
}
trap cleanup EXIT   # 无论成功失败都走清理

exec >> "$LOG"

# 1. 前置检查：空间够不够、MySQL 活不活
df --output=avail -m /backup | tail -1 | awk '{exit ($1 < 20000)}'
mysqladmin ping --silent

# 2. 备份到临时文件
mysqldump --defaults-extra-file=/root/.my.cnf \
  --single-transaction --quick --routines --triggers --events \
  --default-character-set=utf8mb4 --hex-blob \
  --set-gtid-purged=OFF --no-tablespaces --skip-add-locks \
  --max-allowed-packet=256M --databases zz1984 \
  | zstd -19 -T4 -q -o "$TMP"

# 3. 完整性校验：能被 zstd 读完 + 末尾有 dump 完成标记
zstd -t "$TMP"
tail -c 2000 < <(zstd -dc "$TMP") | grep -q 'Dump completed'

# 4. 原子改名，才认为这份备份存在
mv "$TMP" "$OUT"
chmod 600 "$OUT"

# 5. 只保留 7 天
find "$DIR" -name 'zz1984-*.sql.zst' -mtime +7 -delete

第 3 步的 grep 'Dump completed' 是这套脚本最值钱的一行。mysqldump 在最后会写一行 -- Dump completed on 2026-10-19 3:12:07，如果 mysqldump 中途被杀、或者连接断开（比如上游 wait_timeout 到了），文件会被正常写出来但缺这段尾巴。只检查「文件存在且不为空」是抓不到这种情况的。

还有一个隐蔽的坑：set -o pipefail 在这里是必须的。默认管道只返回最后一个命令（zstd）的退出码，mysqldump 崩了 zstd 照样正常结束返回 0，脚本就误以为成功。

四、别让 cron 在错误的时区、错误的环境里跑

我接手过一个站，备份脚本逻辑完全正确，但每天的文件都是凌晨 11 点生成。原因很简单：服务器系统时区是 UTC，而作者以为是本地时间，写的是 10 3 * * *，实际上跑在北京时间 11:00——正好是访问小高峰，dump 产生的 IO 把站点拖到时快时慢。

timedatectl                     # 看 System time zone
timedatectl set-timezone Asia/Shanghai

改完 /etc/timezone 之后 cron 才会跟着变，但已经启动的 cron 进程不会重读，systemctl restart cron 一下最保险。

第二个坑是 cron 的 PATH。cron 里的 PATH=/usr/bin:/bin，如果 zstd 装在 /usr/local/bin（自己编译的），脚本单独跑没问题、放进 cron 就报 command not found，而错误只发到本地 mail，邮箱没配的话等于石沉大海。所以我的做法是在脚本第一行就 export PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin，并且把所有输出重定向到日志文件，绝不依赖 mail：

PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
10 3 * * * /backup/mysql/bin/dump.sh

如果环境里有 systemd，我更推荐把备份做成 *.timer。原因有三个：OnCalendar=*-*-* 03:10:00 语法可读、Persistent=true 能在机器关机错过时间窗后补跑一次、systemctl list-timers 一眼看到上次和下次执行时间。cron 的「错过就不补」在 VPS 被重启的场景下会静默丢一天备份，而你往往要等到还原时才发现缺的那天正是出事那天。

五、备份必须离开这台机器：rclone 到对象存储

备份和数据库放在同一块磁盘上，只能覆盖「误删、误改」两种故障。磁盘坏、机房断电、被服务商停机的场景下，本地备份和源数据一起消失。这一步我用 rclone 同步到 S3 兼容的对象存储（Backblaze B2、Cloudflare R2、阿里云 OSS 的 S3 端点都行）：

# 一次性配置：交互式输入 key/secret，写进加密的配置文件
rclone config create oss s3 \
  provider=Cloudflare \
  endpoint=https://xxxx.r2.cloudflarestorage.com \
  access_key_id=xxx secret_access_key=xxx \
  region=auto acl=private \
  --config /root/.config/rclone/rclone.conf

# 同步（不是 copy），把本机已删除的也同步删掉，避免远端无限膨胀
rclone sync /backup/mysql/daily/ oss:zz1984-backup/daily/ \
  --transfers 2 --checkers 4 --bwlimit 8M \
  --exclude '.tmp-*' \
  --log-file /backup/mysql/logs/rclone-$(date +%F).log --log-level INFO

几个细节值得单独说：

  • --exclude '.tmp-*' 一定要加，否则正在写的半截文件会被同步上去，远端出现不完整备份。
  • --bwlimit 8M 限制带宽。个人站 VPS 通常只有 100Mbps 甚至共享 1Gbps 但有月流量限额，780MB 一次全速跑完可能吃掉当天大部分流量，白天跑会直接影响站点访问。
  • rclone sync 会删除远端多余文件。如果你在远端手放了一份 manual/ 快照，务必分目录同步，不要让 sync 把 manual/ 一起清了。我见过一次事故就是这么来的：sync 了整个 backup 目录，把手工保留的半年前的迁移前快照删掉了。

对象存储这边还要单独配一条生命周期规则，让 daily/ 前缀下的文件 30 天后自动转低频、90 天后删除。云厂商的版本控制建议开启但不建议无限期保留——删库跑路那种场景下版本控制是救命稻草，可如果被恶意写入了大量垃圾文件，版本控制会让账单直接失控。

六、每周一次的还原演练：只有能还原的才叫备份

这是我坚持最久的一件事，也是最能救命的。做法是在同一台机器上起一个第二个 MySQL 实例（端口 3307，独立 datadir），把最新备份还原进去，跑几张表的校验和比对：

# 1. 起一个临时实例（最省事的方式是用容器，但与宿主隔离更干净）
docker run -d --name mysqltest \
  -e MYSQL_ROOT_PASSWORD=verify2026 \
  -v /backup/mysql/verify/data:/var/lib/mysql \
  -p 127.0.0.1:3307:3306 mysql:8.0 \
  --innodb-buffer-pool-size=512M

# 2. 等它 ready，然后还原
until docker exec mysqltest mysqladmin -uroot -pverify2026 ping --silent; do sleep 2; done
zstd -dc /backup/mysql/daily/$(ls -t /backup/mysql/daily | head -1) \
  | docker exec -i mysqltest mysql -uroot -pverify2026

# 3. 行数比对：源库和目标库各算一次
mysql -N -B --defaults-extra-file=/root/.my.cnf -e "
  SELECT table_name, table_rows FROM information_schema.tables
  WHERE table_schema='zz1984' ORDER BY table_name;" > /tmp/src.tsv
mysql -N -B -h127.0.0.1 -P3307 -uroot -pverify2026 -e "
  SELECT table_name, table_rows FROM information_schema.tables
  WHERE table_schema='zz1984' ORDER BY table_name;" > /tmp/dst.tsv

注意 information_schema.table_rows 对 InnoDB 是估算值，两边差个百分之几完全正常，不能当判据。真正可靠的是对关键表做精确计数：

CHECKSUM TABLE typecho_contents, typecho_comments, typecho_metas;

CHECKSUM TABLE 的结果如果两边一致，说明这张表的数据和索引结构都完全一致，这比对比行数强得多。我在一次演练里就是靠它发现的：某个不该有数据的表在备份里多出了 2 万行，追下去发现是有人在备份窗口里跑了一次批量导入，而那张表恰好是 MyISAM，不受 --single-transaction 保护。

演练完一定要 docker rm -f mysqltest 并删掉 /backup/mysql/verify/data。留着不删，下次演练时容器会因为 datadir 里已有数据而走进「初始化跳过」分支，结果就是你在验证一份很旧的、甚至上次残留的数据，得出「备份正常」的错误结论。这个坑我给两个朋友指出过，他们都在演练脚本里漏了清理。

七、出事了怎么办：还原顺序与几个真实教训

假设现在被人误执行了 DROP TABLE typecho_contents，正确顺序是：

  1. 先停写入。把 Nginx 切到维护页，或者直接 systemctl stop php8.2-fpm，别让应用继续往残缺的表里插数据。
  2. 确认备份窗口内丢了什么。手里有最近的每日备份（凌晨 3:10），现在是下午 4 点，那么丢失的是这 13 小时的评论和文章。看 SHOW MASTER STATUS 记下当前 binlog 位置，再翻 binlog 找最后一条写入的时间点。
  3. 先导全量，再重放 binlog。不要试图只从 binlog 里捞出那张表——表结构都丢了。mysqlbinlog --start-datetime=... --stop-datetime=... 把增量重放上去，注意加 --skip-gtids 避免和已还原的 GTID 冲突。
  4. 逐表核对。重放后 CHECKSUM TABLE 对比演练库，确认内容和「停写的那一刻」一致。
  5. 恢复写入并回写静态缓存。PHP 站别忘了清对象缓存与页面缓存，否则前台还会继续吐旧数据；rm -rf /var/cache/nginx/* 加 systemctl reload nginx。

最后说三个我认为最值得记住的教训。第一，备份脚本和站点代码放在同一个 git 仓库里，改一次就要跑一次演练，不要等半年后才验证；第二，永远保留一份「改动前」的手工快照，大版本升级、换表引擎、批量改数据之前先 dump 一份进 manual/，这是唯一能回到「昨天那个能跑的版本」的手段；第三，把备份的成功与否推到手机，用 ntfy 或 Telegram bot 发一条消息，只是「每天跑完发一句 OK」这种最简单的监控，就能在脚本静默失效的第二天发现问题，而不是在三个月后。

备份这件事的投入产出比非常畸形：写脚本两小时，之后每次演练十五分钟，一旦出事能省下的可能是整站三年内容。相比之下，为了省这十五分钟而赌「应该不会出事」，怎么算都不划算。

Last modification：October 19th, 2026 at 09:23 pm
© 允许规范转载

相关文章

Leave a Comment