# 非核心业务数据库
– 完全备份：每周日凌晨2:00
– 增量备份：每天凌晨2:00
– 差异备份：不使用

# 查看xtrabackup版本
# xtrabackup –version
xtrabackup: recognized server arguments: –datadir=/mysql/data –tmpdir=/tmp
xtrabackup version 8.4.0 based on MySQL server 8.4.0 Linux (x86_64) (revision id: 12345678)

BACKUP_DIR=”/backup/mysql/full”
DATE=$(date +%Y%m%d_%H%M%S)
DB_USER=”fgedu_admin”
DB_PASS=”StrongPassword123!”
DB_NAME=”fgedudb”

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

# 执行完全备份
mysqldump –user=$DB_USER –password=$DB_PASS –single-transaction –flush-logs –master-data=2 –all-databases > $BACKUP_DIR/full_backup_$DATE.sql

# 压缩备份文件
gzip $BACKUP_DIR/full_backup_$DATE.sql

# 删除7天前的备份文件
find $BACKUP_DIR -name “full_backup_*.sql.gz” -mtime +7 -delete

# 记录备份日志
echo “[$(date +%Y-%m-%d%H:%M:%S)] Full backup completed: $BACKUP_DIR/full_backup_$DATE.sql.gz” >> /mysql/logs/backup.log

BACKUP_DIR=”/backup/mysql/incremental”
DATE=$(date +%Y%m%d_%H%M%S)
DB_USER=”fgedu_admin”
DB_PASS=”StrongPassword123!”

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

# 获取当前二进制日志文件
BINLOG_FILE=$(mysql –user=$DB_USER –password=$DB_PASS -e “SHOW MASTER STATUS” | grep -v File | awk ‘{print $1}’)
BINLOG_POS=$(mysql –user=$DB_USER –password=$DB_PASS -e “SHOW MASTER STATUS” | grep -v Position | awk ‘{print $2}’)

# 复制二进制日志文件
cp /mysql/data/$BINLOG_FILE $BACKUP_DIR/binlog_$DATE.bin

# 记录备份日志
echo “[$(date +%Y-%m-%d%H:%M:%S)] Incremental backup completed: $BACKUP_DIR/binlog_$DATE.bin, Position: $BINLOG_POS” >> /mysql/logs/backup.log

BACKUP_LOG=”/mysql/logs/backup.log”
ALERT_EMAIL=”admin@fgedu.net.cn”

# 检查最近24小时的备份是否成功
RECENT_BACKUP=$(grep “Full backup completed” $BACKUP_LOG | tail -1 | awk ‘{print $1, $2}’)
RECENT_BACKUP_DATE=$(echo $RECENT_BACKUP | sed ‘s/\[//;s/\]//’)

# 计算时间差
NOW=$(date +%s)
BACKUP_TIME=$(date -d “$RECENT_BACKUP_DATE” +%s)
TIME_DIFF=$((NOW – BACKUP_TIME))

# 如果超过24小时没有备份，发送警报
if [ $TIME_DIFF -gt 86400 ]; then
echo “MySQL backup failed: No full backup in the last 24 hours” | mail -s “MySQL Backup Alert” $ALERT_EMAIL
fi

## 1. 备份类型
– 完全备份：每天凌晨2:00，使用xtrabackup
– 增量备份：每小时一次，使用二进制日志
– 差异备份：每天中午12:00，使用xtrabackup

## 2. 备份存储
– 本地存储：SSD存储，保留7天
– 异地存储：磁带库，保留30天
– 云存储：对象存储，保留90天

## 3. 备份验证
– 每天验证完全备份的完整性
– 每周进行一次恢复演练
– 每月进行一次完整的灾难恢复测试

## 4. 备份自动化
– 使用crontab定时执行备份脚本
– 使用监控工具监控备份状态
– 出现异常时发送警报

# 完全备份：每天凌晨2:00
0 2 * * * /mysql/scripts/full_backup.sh

# 增量备份：每小时一次
0 * * * * /mysql/scripts/incremental_backup.sh

# 差异备份：每天中午12:00
0 12 * * * /mysql/scripts/differential_backup.sh

# 备份监控：每小时一次
0 * * * * /mysql/scripts/backup_monitor.sh

# 备份验证：每天凌晨3:00
0 3 * * * /mysql/scripts/backup_verify.sh

BACKUP_DIR=”/backup/mysql/full”
TEST_DIR=”/mysql/test_recovery”
DATE=$(date +%Y%m%d)

# 创建测试目录
mkdir -p $TEST_DIR

# 获取最新的完全备份
LATEST_BACKUP=$(ls -la $BACKUP_DIR | grep sql.gz | tail -1 | awk ‘{print $9}’)

# 解压备份文件
gunzip -c $BACKUP_DIR/$LATEST_BACKUP > $TEST_DIR/backup.sql

# 恢复到测试数据库
mysql –user=fgedu_admin –password=StrongPassword123! –database=fgedudb_test < $TEST_DIR/backup.sql # 验证恢复结果 TABLE_COUNT=$(mysql --user=fgedu_admin --password=StrongPassword123! --database=fgedudb_test -e "SHOW TABLES" | wc -l) if [ $TABLE_COUNT -gt 0 ]; then echo "[$(date +%Y-%m-%d%H:%M:%S)] Recovery test successful: $TABLE_COUNT tables recovered" >> /mysql/logs/recovery_test.log
else
echo “[$(date +%Y-%m-%d%H:%M:%S)] Recovery test failed” >> /mysql/logs/recovery_test.log
fi

# 清理测试目录
rm -rf $TEST_DIR