MySQL备份与恢复

前面忘记讲数据库备份与恢复了，这里补上。

我们的VPS可能会硬盘损坏需要重装OS，或者运营商跑路了，所以需要定时备份数据库，以防不测。

备份

使用mysqldump导出数据库的SQL语句，因为SQL文件是文本文件，压缩比很高，用bzip2压缩一下。

mysqldump参数说明：

• --all-databases
  : 备份所有数据库
• --single-transaction
  : 在一个事务中完成备份，保证数据一致性
• --quick
  : 一行一行读取数据，减少内存使用
• --lock-tables=false
  : 不锁表，减少对业务的影响

# 完整备份
mysqldump -u root -p --all-databases | bzip2 > backup.sql.bz2

# 单库备份
mysqldump -u root -p database_name | bzip2 > database_backup.sql.bz2

# 单表备份
mysqldump -u root -p database_name table_name | bzip2 > table_backup.sql.bz2

恢复

bzcat backup.sql.bz2 | mysql -u root -p

定时备份

这里使用cron服务定时执行命令。

1. 创建备份脚本

将以下内容保存为 /usr/local/bin/mysql_backup.sh
。这个脚本做了如下操作：

• 备份数据库到BACKUP_DIR
• 清除RETENTION_DAYS天以前的备份
• 将备份文件同步到远程服务器REMOTE_HOST

#!/bin/bash

# 备份配置
BACKUP_DIR="/backup/mysql"     # 备份文件存放目录
DATE=$(date +%Y%m%d_%H%M%S)   # 备份文件名中的时间戳
RETENTION_DAYS=7              # 备份文件保留天数
MYSQL_USER="root"             # MySQL用户名
MYSQL_PASSWORD="password"      # MySQL密码
REMOTE_USER="user"            # 远程服务器用户名
REMOTE_HOST="remote.backup.server" # 远程服务器地址
REMOTE_DIR="/backup/mysql"    # 远程服务器备份目录

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

# 检查目录是否创建成功
if [ ! -d "$BACKUP_DIR" ]; then
    echo "Error: Failed to create backup directory" >> "$BACKUP_DIR/backup.log"
    exit 1
fi

# 执行备份并使用bzip2压缩
mysqldump -u $MYSQL_USER -p"$MYSQL_PASSWORD" \
    --all-databases \
    --single-transaction \
    --quick \
    --lock-tables=false \
    | bzip2 > "$BACKUP_DIR/backup_$DATE.sql.bz2"


# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "Backup completed successfully at $(date)" >> "$BACKUP_DIR/backup.log"
else
    echo "Backup failed at $(date)" >> "$BACKUP_DIR/backup.log"
    exit 1
fi

# 清理旧备份
find $BACKUP_DIR -name "backup_*.sql.bz2" -mtime +$RETENTION_DAYS -delete

# 将备份文件同步到远程服务器
rsync -av $BACKUP_DIR $REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR

# 检查剩余空间
SPACE=$(df -h "$BACKUP_DIR" | awk 'NR==2 {print $4}')
echo "Remaining space: $SPACE" >> "$BACKUP_DIR/backup.log"

1. 设置脚本权限

chmod +x /usr/local/bin/mysql_backup.sh

1. 添加定时任务

# 编辑crontab
crontab -e

# 添加以下内容（每天凌晨3点执行备份）
0 3 * * * /usr/local/bin/mysql_backup.sh

1. 启用cron服务

systemctl enable --now crond

1. 查看备份日志

tail -f /backup/mysql/backup.log