MySQL备份与还原

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

备份

使用mysqldump导出SQL语句，因为SQL文件是文本文件，压缩比很高。

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

定时备份

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 -pBACKUP_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
# 清理旧备份
findBACKUP_DIR -name "backup_*.sql.bz2" -mtime +RETENTION_DAYS -delete
# 将备份文件同步到远程服务器
rsync -av /backup/mysql/ user@remote:/backup/mysql/
# 检查剩余空间
SPACE=(df -h "BACKUP_DIR" | awk 'NR==2 {print4}')
echo "Remaining space: SPACE" >> "BACKUP_DIR/backup.log"

2. 设置脚本权限

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

3. 添加定时任务

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

4. 启用cron服务

systemctl enable --now crond

5. 查看备份日志

tail -f /backup/mysql/backup.log