MySQL 数据库备份实战：从 mysqldump 到异地同步的完整方案

数据是 VPS 上最珍贵的资产，系统可以重装，代码可以重新拉取，数据库一旦丢失就是灾难。本文从零搭建一套 MySQL/MariaDB 备份体系：本地定时备份、压缩加密、保留策略、远端同步、恢复演练，覆盖完整链路并给出常见排错方法。

一、备份前的准备：确认数据库连接与目录

先确认 mysqldump 可用并规划备份目录。建议把备份与数据分盘存储，避免同一磁盘故障导致"备份和源数据一起丢失"。

which mysqldump
# 创建备份目录并设置权限
sudo mkdir -p /backup/mysql
sudo chown mysql:mysql /backup/mysql

出于安全考虑，不建议把数据库密码明文写进脚本文件并赋予过宽权限。推荐使用 my.cnf 的 client 段存放凭据：

sudo vim /etc/mysql/backup.cnf

[client]
user=backup_user
password=StrongPass2026
host=127.0.0.1

sudo chmod 600 /etc/mysql/backup.cnf

并创建最小权限的备份账号，只授予 SELECT 和 LOCK TABLES：

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPass2026';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

二、编写备份脚本

下面是一个功能完整的备份脚本，包含全量备份、gzip 压缩、按天分目录、7 天自动清理：

#!/bin/bash
# /usr/local/bin/mysql_backup.sh
set -euo pipefail

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
KEEP_DAYS=7

# 所有要备份的数据库（空格分隔），也可用 --all-databases
DBS="mydb1 mydb2"
CNF="/etc/mysql/backup.cnf"

mkdir -p "$BACKUP_DIR/$DATE"

for db in $DBS; do
    echo "[$(date +%F\ %T)] backing up: $db"
    mysqldump --defaults-extra-file="$CNF" \
        --single-transaction \
        --routines --triggers --events \
        --set-gtid-purged=OFF \
        "$db" | gzip > "$BACKUP_DIR/$DATE/${db}.sql.gz"
done

# 记录备份元信息，便于后续校验
echo "finished at $(date +%F\ %T)" > "$BACKUP_DIR/$DATE/INFO"

# 清理超过保留天数的旧备份
find "$BACKUP_DIR" -maxdepth 1 -type d -mtime +"$KEEP_DAYS" -exec rm -rf {} \;

echo "backup done. latest: $BACKUP_DIR/$DATE"

关键参数说明：

--single-transaction：InnoDB 下使用事务快照备份，不加锁，不阻塞线上读写，是生产环境标配。

--routines --triggers --events：把存储过程、触发器、事件调度器一并导出，否则恢复后功能缺失。

--set-gtid-purged=OFF：避免 GTID 环境下恢复时报 "GTID_PURGED" 相关错误，单机场景下通常需要关闭。

set -euo pipefail：任何一步失败立即退出，避免"看似成功实则只备份了一半"的假象。

三、定时执行与保留策略

用 crontab 调度，建议错开业务高峰并错开整点：

# 每天凌晨 3:30 全量备份
30 3 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

# 每周日凌晨 4:00 额外做一份完整归档
0 4 * * 0 /usr/local/bin/mysql_backup.sh

如果单库数据量很大（几十 GB 以上），全量每天跑会消耗大量时间和磁盘，可采用"每周全量 + 每天增量"策略。启用 binlog 后，增量备份就是对 binlog 的持续归档：

# 确认 binlog 已开启
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

# 在 my.cnf 中开启并设定保留时长
[mysqld]
log_bin=/var/log/mysql/mysql-bin
binlog_expire_logs_seconds=604800
max_binlog_size=100M

需要生成增量时，用 mysqlbinlog 按时间点导出：

mysqlbinlog --start-datetime="2026-10-16 03:30:00" \
    --stop-datetime="2026-10-16 23:59:59" \
    /var/log/mysql/mysql-bin.000023 > /backup/mysql/inc_20261016.sql

四、异地同步：备份的备份

本地备份只能防"手滑删库"，防不了"机房失火"。把备份同步到异地对数据安全是质的提升。方案一：rsync 到另一台服务器：

rsync -avz --delete /backup/mysql/ backup@remote-host:/backup/mysql/
# 加入定时任务每日执行即可

方案二：上传到对象存储（OSS/COS/S3）。以 s3cmd 为例：

# 安装并配置 AccessKey
sudo apt install -y s3cmd
s3cmd --configure

# 增量同步到 bucket
s3cmd sync --delete /backup/mysql/ s3://my-vps-backup/mysql/
# 设置对象存储的生命周期策略，自动转低频并最终删除

方案三：如果是腾讯云/阿里云的 VPS，直接用云厂商的快照功能做整机磁盘快照，和逻辑备份互补。

五、备份的校验与恢复演练

没有验证过的备份等于没有备份。脚本中建议追加校验步骤，例如每份备份生成 md5 并测试解压：

# 校验备份文件完整性
gzip -t "$BACKUP_DIR/$DATE/${db}.sql.gz" && echo "gzip OK"

# 抽查内容是否包含关键表
zcat "$BACKUP_DIR/$DATE/mydb1.sql.gz" | grep -q "CREATE TABLE \`users\`" && echo "table OK"

定期在测试实例上做一次真实恢复演练：

# 建空库并恢复
mysql --defaults-extra-file=/etc/mysql/backup.cnf -e "CREATE DATABASE mydb1_restore"
zcat /backup/mysql/20261016_033000/mydb1.sql.gz | \
    mysql --defaults-extra-file=/etc/mysql/backup.cnf mydb1_restore

# 对比行数是否与线上一致
mysql --defaults-extra-file=/etc/mysql/backup.cnf \
    -e "SELECT COUNT(*) FROM mydb1.users" \
    -e "SELECT COUNT(*) FROM mydb1_restore.users"

六、常见故障排错

1. mysqldump: Access denied：备份账号权限不足，检查是否授予了 SELECT 与 LOCK TABLES。

2. 备份文件巨大且耗时：确认 --single-transaction 生效（InnoDB），MyISAM 表仍需锁表；也可改为 per-db 并行备份：

echo "$DBS" | xargs -P 4 -I {} sh -c 'mysqldump ... {} | gzip > ...'

3. mysqldump: Couldn't execute 'FLUSH TABLES'：账号没有 RELOAD 权限，把 FLUSH TABLES 需要的 RELOAD 权限授给备份账号，或改用 --single-transaction 并去掉 --master-data 相关参数。

4. 恢复时报 GTID 错误：导出时加 --set-gtid-purged=OFF，或恢复前在目标库执行 RESET MASTER。

5. 磁盘被备份塞满：检查 find 清理是否匹配了正确目录层级（maxdepth 与目录结构要一致），并留意 binlog 是否也在同一分区。

七、总结

一套合格的备份方案应当满足：自动化（cron 调度）、可验证（gzip -t 与恢复演练）、有异地副本（rsync/对象存储）、保留期合理（KEEP_DAYS 按业务需求定）。建议每月至少做一次完整恢复演练，真正出事时才能胸有成竹地 30 分钟内拉起服务。