VPS数据库自动备份与恢复方案

---

数据库备份是VPS运维中最重要的工作之一。没有备份，一次误操作或硬件故障就可能导致所有数据永久丢失。本文介绍一套完整的MySQL数据库自动备份方案，包括本地备份、定时任务、数据恢复和远程异地备份。

Tip： 备份策略的核心原则：定期备份 + 异地存储 + 定期验证恢复。仅在本机备份等于没有备份。

一、mysqldump基础备份

mysqldump是MySQL自带的逻辑备份工具，适用于中小型数据库：

# 备份单个数据库
mysqldump -u root -p myapp_db > /backup/myapp_db_$(date +%Y%m%d).sql

# 备份所有数据库
mysqldump -u root -p --all-databases > /backup/all_db_$(date +%Y%m%d).sql

# 备份并压缩（推荐，节省磁盘空间）
mysqldump -u root -p myapp_db | gzip > /backup/myapp_db_$(date +%Y%m%d).sql.gz

# 备份指定表
mysqldump -u root -p myapp_db users orders > /backup/tables_$(date +%Y%m%d).sql

# 仅备份表结构（不含数据）
mysqldump -u root -p --no-data myapp_db > /backup/schema_$(date +%Y%m%d).sql

常用的mysqldump参数：

mysqldump -u root -p \
  --single-transaction \    # InnoDB一致性备份，不锁表
  --routines \              # 包含存储过程和函数
  --triggers \              # 包含触发器
  --events \                # 包含事件
  --quick \                 # 大表逐行读取，减少内存占用
  --flush-logs \            # 备份前刷新二进制日志
  myapp_db | gzip > /backup/myapp_db_$(date +%Y%m%d).sql.gz

二、创建备份专用用户

不要使用root进行日常备份，创建一个权限最小化的备份用户：

mysql -u root -p

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupPass123!';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, PROCESS, RELOAD ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

将密码保存在配置文件中，避免在命令行暴露：

# 创建密码文件
cat > /root/.my.cnf << 'EOF'
[mysqldump]
user=backup_user
password=BackupPass123!
EOF

# 设置严格权限
chmod 600 /root/.my.cnf

# 之后备份无需输入密码
mysqldump --single-transaction myapp_db | gzip > /backup/myapp_db.sql.gz

三、自动备份脚本

编写一个完整的自动备份脚本：

cat > /usr/local/bin/mysql-backup.sh << 'SCRIPT'
#!/bin/bash
# MySQL自动备份脚本

# 配置
BACKUP_DIR="/backup/mysql"
RETENTION_DAYS=7
DATABASES="myapp_db wordpress_db"
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/mysql-backup.log"

# 创建备份目录
mkdir -p $BACKUP_DIR

echo "[$DATE] 开始备份..." >> $LOG_FILE

for DB in $DATABASES; do
  FILENAME="${BACKUP_DIR}/${DB}_${DATE}.sql.gz"

  mysqldump --single-transaction --routines --triggers \
    --quick $DB 2>> $LOG_FILE | gzip > $FILENAME

  if [ $? -eq 0 ]; then
    SIZE=$(du -h $FILENAME | cut -f1)
    echo "[$DATE] $DB 备份成功: $FILENAME ($SIZE)" >> $LOG_FILE
  else
    echo "[$DATE] $DB 备份失败!" >> $LOG_FILE
  fi
done

# 清理过期备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "[$DATE] 已清理${RETENTION_DAYS}天前的备份" >> $LOG_FILE
echo "---" >> $LOG_FILE
SCRIPT

chmod +x /usr/local/bin/mysql-backup.sh

四、配置crontab定时任务

使用crontab设置定时自动备份：

# 编辑crontab
crontab -e

# 每天凌晨3点执行备份
0 3 * * * /usr/local/bin/mysql-backup.sh

# 每6小时备份一次（高频更新站点）
0 */6 * * * /usr/local/bin/mysql-backup.sh

# 每周日凌晨2点做一次全量备份
0 2 * * 0 mysqldump --all-databases --single-transaction | gzip > /backup/mysql/full_$(date +\%Y\%m\%d).sql.gz

验证crontab是否生效：

# 查看当前定时任务
crontab -l

# 手动执行脚本测试
/usr/local/bin/mysql-backup.sh

# 查看备份日志
tail -20 /var/log/mysql-backup.log

# 查看备份文件
ls -lh /backup/mysql/

五、数据恢复

备份的价值在于能够成功恢复。以下是各种恢复场景：

# 恢复整个数据库（从未压缩的SQL文件）
mysql -u root -p myapp_db < /backup/mysql/myapp_db_20260328.sql

# 恢复压缩的备份
gunzip < /backup/mysql/myapp_db_20260328.sql.gz | mysql -u root -p myapp_db

# 恢复到新数据库（用于测试恢复）
mysql -u root -p -e "CREATE DATABASE myapp_db_test;"
gunzip < /backup/mysql/myapp_db_20260328.sql.gz | mysql -u root -p myapp_db_test

# 恢复单张表（从全量备份中提取）
# 方法1：使用sed提取
sed -n '/^-- Table structure for table `users`/,/^-- Table structure for table/p' backup.sql > users_table.sql
mysql -u root -p myapp_db < users_table.sql

# 方法2：使用mysql命令选择性恢复
mysql -u root -p --one-database myapp_db < all_databases_backup.sql

Tip： 建议每月至少做一次恢复演练，确认备份文件可以正常恢复。可以恢复到临时数据库进行验证。

六、远程异地备份

本地备份不能防御硬件故障和机房事故，必须将备份同步到远程服务器：

# 方法1：使用rsync同步到远程服务器
rsync -avz --delete /backup/mysql/ user@remote-server:/backup/mysql-from-bwg/

# 方法2：使用scp传输
scp /backup/mysql/myapp_db_$(date +%Y%m%d)*.sql.gz user@remote-server:/backup/

# 方法3：备份完成后直接通过SSH管道传输到远程
mysqldump --single-transaction myapp_db | gzip | ssh user@remote-server "cat > /backup/myapp_db_$(date +%Y%m%d).sql.gz"

在备份脚本中添加远程同步（使用SSH密钥免密登录）：

# 在mysql-backup.sh末尾添加远程同步
# 同步到远程服务器（需要先配置SSH密钥登录）
rsync -avz --delete $BACKUP_DIR/ user@backup-server:/backup/mysql-bwg/ 2>> $LOG_FILE
if [ $? -eq 0 ]; then
  echo "[$DATE] 远程同步成功" >> $LOG_FILE
else
  echo "[$DATE] 远程同步失败!" >> $LOG_FILE
fi

SSH密钥配置请参考SSH密钥登录教程。

七、二进制日志备份（增量备份）

对于数据更新频繁的数据库，仅靠每日全量备份可能丢失最多24小时的数据。结合二进制日志可以实现增量恢复：

# 确认二进制日志已开启
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin';"

# 在my.cnf中开启（如果未开启）
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_expire_logs_seconds = 604800
max_binlog_size = 100M

# 查看当前二进制日志
mysql -u root -p -e "SHOW BINARY LOGS;"

# 使用二进制日志恢复到指定时间点
# 先恢复全量备份
gunzip < /backup/mysql/myapp_db_20260328.sql.gz | mysql -u root -p myapp_db

# 再应用二进制日志中的增量变更
mysqlbinlog --start-datetime="2026-03-28 03:00:00" \
  --stop-datetime="2026-03-28 12:00:00" \
  /var/log/mysql/mysql-bin.000001 | mysql -u root -p

八、备份验证与监控

在备份脚本中加入完整性检查：

# 检查备份文件是否正常（能被gunzip解压）
gunzip -t /backup/mysql/myapp_db_20260328.sql.gz
if [ $? -ne 0 ]; then
  echo "备份文件损坏！" >> $LOG_FILE
fi

# 检查SQL文件末尾是否有完成标记
gunzip < /backup/mysql/myapp_db_20260328.sql.gz | tail -1
# 正常备份的最后一行应该是 "-- Dump completed"

完整的VPS备份方案（包括文件备份）请参考VPS自动备份：脚本+定时+异地。

Tip： 更多教程请查看新手教程。