网站数据库如何定时备份到另一台服务器？自动化脚本实战

一台服务器上同时跑着 Web 和 MySQL，备份脚本每天把 .sql.gz 写在 /var/backup 里，看起来一切正常。直到某天磁盘主控故障，整块盘不可读——生产数据和"备份"一起消失。这是单机备份最典型的死法：它防的是误操作，防不了硬件故障和机房事故。

把数据库定时备份到另一台服务器，是成本最低、见效最快的一步改进。本文给出一套可直接复制使用的完整方案：备份专用账号 → ssh-keygen 免密 → mysqldump 一致性导出 → rsync 异地同步 → sha256sum 校验 → crontab 定时 → 保留周期清理 → 恢复演练。整套脚本不到 100 行，但覆盖了生产可用的全部要素。

一、架构设计与前置约定

先约定角色与路径，后续脚本都以此为准：

• 源端（Web/DB 服务器）：10.0.0.5，运行 MySQL 8.0，数据目录 /var/lib/mysql。
• 备份端（异地服务器）：10.0.1.9，接收备份，目录 /backup/mysql/。
• 备份专用账号：backup@10.0.0.%，只授予备份所需的最小权限。
• 备份专用系统账号：源端 backup，备份端 bkrecv。
• 传输方式：SSH 密钥认证 + rsync over SSH，禁止密码登录。

遵循 3-2-1 备份原则：生产数据 + 源端本地副本 + 异地服务器副本，共 3 份；本地磁盘与异地服务器为 2 种介质；异地即 1 份异地副本。若条件允许，再加一份对象存储作为离线/不可变副本。

二、第一步：创建备份专用账号（最小权限）

绝对不要用 root 跑备份。备份账号只需要读数据和获取一致性位点，不需要写权限。

# 在 MySQL 中创建备份专用账号
mysql -u root -p <<'SQL'
CREATE USER 'backup'@'10.0.0.%' IDENTIFIED BY 'Str0ng-Pass-Here';
GRANT SELECT, RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'10.0.0.%';
FLUSH PRIVILEGES;
SQL

# 验证权限（应能读、不能写）
mysql -u backup -p -h 127.0.0.1 -e "SHOW DATABASES;"
mysql -u backup -p -h 127.0.0.1 -e "CREATE DATABASE hack;"   # 应报权限错误

权限说明：SELECT 用于读表数据，RELOAD 与 LOCK TABLES 用于 FLUSH TABLES，PROCESS 用于查看当前会话，REPLICATION CLIENT 用于执行 SHOW MASTER STATUS 取得 binlog 位点。注意这里没有 SUPER、没有 DROP、没有 INSERT——备份账号泄露也不至于被删库。

2.1 用配置文件免交互，避免密码进命令行

# /home/backup/.my.cnf  (权限必须是 600)
[client]
user=backup
password=Str0ng-Pass-Here
host=127.0.0.1

[mysqldump]
single-transaction=TRUE
routines=TRUE
triggers=TRUE
events=TRUE
default-character-set=utf8mb4

chmod 600 /home/backup/.my.cnf
chown backup:backup /home/backup/.my.cnf

把密码写进脚本或命令行，会被 ps 和 shell history 捕获。用 ~/.my.cnf 是最简单也最容易被忽略的正确做法，注意文件权限必须是 600，否则 MySQL 客户端会忽略该配置。

三、第二步：ssh-keygen 免密与SSH 加固

# 源端生成专用密钥（不使用默认 id_rsa，避免混用）
sudo -u backup ssh-keygen -t ed25519 -f /home/backup/.ssh/id_ed25519_bk -N '' -C 'mysql-backup'

# 把公钥传到备份端（首次需要一次密码）
sudo -u backup ssh-copy-id -i /home/backup/.ssh/id_ed25519_bk.pub bkrecv@10.0.1.9

# 验证免密连通
sudo -u backup ssh -i /home/backup/.ssh/id_ed25519_bk bkrecv@10.0.1.9 'echo OK; df -h /backup'

备份端的 ~/.ssh/authorized_keys 里，强烈建议加前缀限制，把密钥的能力锁死——这是很多人会漏掉的一步：

# 备份端 /home/bkrecv/.ssh/authorized_keys 的一行内容
command="/usr/local/bin/rrsync -wo /backup/mysql/",no-agent-forwarding,no-port-forwarding,no-pty,no-user-rc,restrict ssh-ed25519 AAAA... mysql-backup

rrsync 是 rsync 自带的受限包装脚本（通常在 /usr/share/rsync/rrsync 或随 rsync 包提供），-wo 表示只读、只写（write-only），配合 restrict 与 command=，即便密钥泄露，攻击者也只能在 /backup/mysql/ 目录内写文件，拿不到 shell。判断标准：配置完成后，用该密钥尝试 ssh bkrecv@10.0.1.9 应该被拒绝登录，而 rsync 正常。

四、第三步：备份脚本本体

#!/bin/bash
# /usr/local/bin/db_backup.sh  —— 网站数据库定时异地备份
set -euo pipefail
export PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin

BK_USER_HOME=/home/backup
SSH_KEY=$BK_USER_HOME/.ssh/id_ed25519_bk
REMOTE=bkrecv@10.0.1.9
REMOTE_DIR=/backup/mysql
LOCAL_DIR=/var/backup/mysql
DATE=$(date +%F-%H%M)
LOG=/var/log/db_backup.log

log() { echo "[$(date '+%F %T')] $*" | tee -a "$LOG"; }

# 单一实例锁，防止上一次未结束时重叠执行
exec 9>/var/lock/db_backup.lock
flock -n 9 || { log "ERROR: another backup is running"; exit 1; }

mkdir -p "$LOCAL_DIR"
log "=== backup start, tag=$DATE ==="

# 1) 一致性逻辑备份（全库）
DUMP=$LOCAL_DIR/all-$DATE.sql.gz
mysqldump --defaults-file=$BK_USER_HOME/.my.cnf \
          --single-transaction \
          --master-data=2 \
          --routines \
          --triggers \
          --events \
          --hex-blob \
          --all-databases \
        | gzip -6 > "$DUMP"

# 2) 记录的 binlog 位点单独抽出，便于 PITR
zcat "$DUMP" | grep -m1 -i "CHANGE MASTER" > "$LOCAL_DIR/binlog-pos-$DATE.txt" || true

# 3) 本地校验值
( cd "$LOCAL_DIR" && sha256sum "all-$DATE.sql.gz" > "all-$DATE.sql.gz.sha256" )

# 4) 异地同步（限流，避免挤占业务带宽）
rsync -az --bwlimit=20480 \
      -e "ssh -i $SSH_KEY -o StrictHostKeyChecking=no -o BatchMode=yes" \
      "$LOCAL_DIR"/ "$REMOTE:$REMOTE_DIR/"

# 5) 异地端回读校验（验证传输完整性）
ssh -i "$SSH_KEY" -o BatchMode=yes "$REMOTE" \
    "cd $REMOTE_DIR && sha256sum -c all-$DATE.sql.gz.sha256" | tee -a "$LOG"

log "=== backup done, size=$(du -h "$DUMP" | cut -f1) ==="

4.1 关键参数解释

五、第四步：备份端的保留策略与多版本管理

备份端收到的是每日一个 .sql.gz。保留策略用 find 实现，简单可靠：

#!/bin/bash
# /usr/local/bin/backup_rotate.sh  —— 在备份端执行
set -euo pipefail
export PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
BK=/backup/mysql
LOG=/var/log/backup_rotate.log

echo "[$(date '+%F %T')] rotate start" >> "$LOG"

# 先 dry-run 打印，确认无误后再真正删除
find "$BK" -name "all-*.sql.gz" -mtime +30 -print >> "$LOG"
find "$BK" -name "all-*.sql.gz" -mtime +30 -delete

# 每月 1 日的备份标记为长期保留（月备）
find "$BK" -name "all-*-01-*.sql.gz" -exec touch -a {} \; 2>/dev/null || true

echo "[$(date '+%F %T')] rotate done, usage=$(df -h $BK | tail -1)" >> "$LOG"

更省空间的做法是保留 binlog 而非每日全量：每周一次全量 + 持续归档 binlog，这样 RPO 可以压到分钟级，而存储成本远低于每日全量。binlog 归档同样用 rsync 推送：

# 归档 binlog：只推送尚未同步的文件
rsync -az --bwlimit=10240 \
      -e "ssh -i /home/backup/.ssh/id_ed25519_bk" \
      /var/lib/mysql/mysql-bin.* bkrecv@10.0.1.9:/backup/binlog/

# 定期清理超过保留期的 binlog（在 MySQL 内执行，不要直接 rm）
mysql -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 14 DAY);"

六、第五步：crontab 定时与监控

# /etc/cron.d/db-backup
SHELL=/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
MAILTO=ops@example.com

# 每天 02:10 备份（避开整点与云厂商快照窗口）
10 2 * * * backup /usr/local/bin/db_backup.sh >> /var/log/db_backup.log 2>&1

# 每天 03:30 备份端清理过期备份
30 3 * * * bkrecv /usr/local/bin/backup_rotate.sh >> /var/log/backup_rotate.log 2>&1

# 每周一 05:00 做一次恢复演练（隔离实例）
0 5 * * 1 bkrecv /usr/local/bin/backup_restore_test.sh >> /var/log/restore_test.log 2>&1

6.1 脚本必须检查退出码并告警

# 简易告警：备份失败或文件异常偏小时通知
SIZE=$(stat -c %s /backup/mysql/all-$(date +%F)*.sql.gz 2>/dev/null | head -1)
if [ -z "${SIZE:-}" ] || [ "$SIZE" -lt 1048576 ]; then
  echo "BACKUP ALERT: size=$SIZE" | mail -s "MySQL backup abnormal" ops@example.com
fi

# 检查最近一次备份距今是否超过 26 小时（防 cron 静默不执行）
NEWEST=$(find /backup/mysql -name "all-*.sql.gz" -mmin -1560 | wc -l)
[ "$NEWEST" -eq 0 ] && echo "BACKUP STALE" | mail -s "backup overdue" ops@example.com

判断标准：只看"文件存在"是不够的。必须同时检查三件事——文件存在、大小合理（与昨日相比波动不超过 50%）、校验值通过。三项都过才算备份成功。

七、第六步：定期做真实恢复演练

#!/bin/bash
# /usr/local/bin/backup_restore_test.sh —— 在备份端，隔离实例上恢复验证
set -euo pipefail
LATEST=$(ls -t /backup/mysql/all-*.sql.gz | head -1)
DIR=/restore/test; PORT=3307

rm -rf "$DIR"; mkdir -p "$DIR"
zcat "$LATEST" | mysql --defaults-file=/home/bkrecv/.my.cnf --port=$PORT --socket=/tmp/test.sock <<<"CREATE DATABASE IF NOT EXISTS restorecheck;"
zcat "$LATEST" | mysql --defaults-file=/home/bkrecv/.my.cnf --port=$PORT --socket=/tmp/test.sock

# 抽样校验
mysql --port=$PORT --socket=/tmp/test.sock -e "SELECT COUNT(*) FROM appdb.orders;"
mysql --port=$PORT --socket=/tmp/test.sock -e "SELECT MAX(created_at) FROM appdb.orders;"
echo "RESTORE TEST OK: $LATEST" >> /var/log/restore_test.log
rm -rf "$DIR"

演练频率建议：L1 系统每月一次，L0 核心系统每两周一次。每次记录实际恢复耗时并与 RTO 目标对比，不达标就回到设计阶段调整。演练的价值在于它能发现那些日常检查永远发现不了的问题：比如备份账号的权限在某次安全加固后被回收，导致导出的存储过程全部为空；比如某个表的字符集在半年前被改成 utf8mb4，而备份脚本还写死着 latin1，恢复后中文全是乱码。这些问题只有在真实导入之后才会暴露，靠看文件大小和校验值是查不出来的。

八、上线部署检查清单

整套方案部署完成后，按下面的顺序逐项验证一遍，任何一项不通过都不能宣布上线。这份清单同时也是半年后做复盘时的核查依据。

1. 备份专用账号 backup 已创建，权限仅为 SELECT, RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT，且验证过无法执行写操作。
2. /home/backup/.my.cnf 权限为 600，属主为 backup，且 mysqldump 免交互可正常执行。
3. SSH 专用密钥已生成并分发，备份端 authorized_keys 已加 command= 与 restrict 前缀，验证该密钥无法登录 shell。
4. 手动执行一次 db_backup.sh，确认本地与异地都生成了 .sql.gz，且体积与全库数据量相符。
5. 执行 sha256sum -c 在异地端回读校验，确认传输未损坏。
6. 检查 binlog-pos-*.txt 中记录了 binlog 位点，确认可用于 PITR。
7. 配置 crontab，等待至少两个完整周期，确认定时任务确实执行且日志无报错。
8. 人为制造一次并发（连续手动执行两次脚本），确认 flock 生效、第二次直接退出而非重叠。
9. 执行一次完整的恢复演练：在隔离实例上导入，抽样校验行数与最新时间。
10. 把"备份失败""备份停滞""体积异常"三类告警接入通知渠道，并各触发一次验证告警可达。

第十项最容易被跳过，但它决定了前面九项的价值：如果备份失败了却没有人收到通知，那么整套方案在真正需要它的那一天依然是失效的。判断标准很简单——随便挑一个工作日的凌晨，把备份端的目录临时改名让同步失败一次，看告警是否真的把人叫醒了。没叫醒就要重新配通知链路。

小结

网站数据库定时备份到另一台服务器，落地要素是：备份专用账号最小权限、~/.my.cnf 免交互、ssh-keygen 专用密钥 + rrsync 受限、mysqldump --single-transaction --master-data=2 --routines --triggers 一致性导出、rsync --bwlimit 限流推送、sha256sum 双重校验、crontab 配合 flock 防重叠、保留周期清理、以及定期真实恢复演练。

避坑清单

• 不要把备份留在生产机同一块磁盘，硬件故障会一起带走。
• cron 的 PATH 极简，脚本内必须显式 export PATH，否则 mysqldump 找不到。
• 必须加 flock，长任务重叠是"假成功"的头号来源。
• --routines --triggers --events 三件套缺一不可，否则恢复后业务报错。
• 备份账号不要给 SUPER/DROP 权限，泄露即等于被删库。
• SSH 公钥要加 command= 与 restrict 前缀，避免密钥被用作跳板。
• sha256sum 要在源端生成、异地端回读校验，只做一遍等于没做。
• 至少每月真实恢复一次，未演练的备份只能算"文件副本"。