2. 确定恢复流程：
– 准备恢复环境：确保恢复环境的可用性
– 选择备份文件：选择合适的备份文件进行恢复
– 执行恢复操作：按照恢复流程执行恢复操作
– 验证恢复结果：验证恢复是否成功

3. 确定恢复时间目标（RTO）：
– 根据业务需求，确定可接受的恢复时间
– 小型数据库：1-2小时
– 中型数据库：2-4小时
– 大型数据库：4-8小时

4. 制定恢复计划：
– 详细记录恢复步骤和操作
– 明确各步骤的负责人和时间要求
– 制定恢复演练计划，定期进行恢复演练

5. 配置恢复监控：
– 监控恢复过程中的异常情况
– 及时发现和处理恢复过程中的问题

6. 制定恢复策略文档：
– 记录恢复策略的详细信息
– 包括恢复类型、流程、时间目标等
– 确保相关人员熟悉恢复策略

2. 确定灾备方案：
– 主从复制：主库和从库之间的复制
– 双主复制：两个主库之间的相互复制
– 集群：如InnoDB Cluster

3. 确定RTO和RPO：
– 恢复时间目标（RTO）：从灾难发生到系统恢复的时间
– 恢复点目标（RPO）：灾难发生后，数据可能丢失的最大时间范围

4. 制定灾备计划：
– 详细记录灾备方案和操作步骤
– 明确各步骤的负责人和时间要求
– 制定灾备演练计划，定期进行灾备演练

5. 配置灾备监控：
– 监控灾备系统的运行状态
– 及时发现和处理灾备系统的问题

6. 制定灾备策略文档：
– 记录灾备策略的详细信息
– 包括灾备级别、方案、RTO和RPO等
– 确保相关人员熟悉灾备策略

# 灾备策略选择因素
– 业务重要性：核心业务需要更高级别的灾备
– 数据量：数据量大的系统需要更可靠的灾备方案
– 预算：根据预算选择合适的灾备方案
– 地理因素：考虑自然灾害等因素的影响
– 合规要求：某些行业有特定的灾备要求

# 步骤2：配置备份存储
# 本地存储：挂载本地磁盘
# 网络存储：挂载NFS或SAN
# 云存储：配置AWS S3、阿里云OSS等

# 步骤3：创建备份脚本
# 完全备份脚本
# vi /mysql/scripts/backup_full.sh
#!/bin/bash
# backup_full.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

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

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

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

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

# 检查备份文件大小
du -h $BACKUP_DIR/full/full_backup_$DATE.sql.gz >> /mysql/logs/backup.log

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

# 增量备份脚本
# vi /mysql/scripts/backup_incr.sh
#!/bin/bash
# backup_incr.sh

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

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

# 执行增量备份（基于二进制日志）
mysqlbinlog –raw –read-from-remote-server –host=localhost –user=$DB_USER –password=$DB_PASS –result-file=$BACKUP_DIR/incremental/binlog_$DATE /mysql/logs/binlog.*

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

# 删除3天前的增量备份文件
find $BACKUP_DIR/incremental -name “binlog_*” -mtime +3 -delete

# 步骤4：设置执行权限
chmod +x /mysql/scripts/backup_full.sh /mysql/scripts/backup_incr.sh

# 步骤5：添加cron任务
# crontab -e
0 2 * * * /mysql/scripts/backup_full.sh
0 * * * * /mysql/scripts/backup_incr.sh

# 步骤6：配置备份监控
# vi /mysql/scripts/monitor_backup.sh
#!/bin/bash
# monitor_backup.sh

LOG_FILE=”/mysql/logs/backup_monitor.log”

# 检查最新的完全备份文件
LATEST_FULL_BACKUP=$(ls -t /backup/mysql/full/*.sql.gz 2>/dev/null | head -1)
if [ -z “$LATEST_FULL_BACKUP” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] No full backup files found!” >> $LOG_FILE
# 发送告警邮件
mail -s “Backup Alert: No full backup files found” admin@example.com < $LOG_FILE else # 检查完全备份文件的时间 BACKUP_TIME=$(stat -c %Y $LATEST_FULL_BACKUP) CURRENT_TIME=$(date +%s) TIME_DIFF=$((CURRENT_TIME - BACKUP_TIME)) if [ $TIME_DIFF -gt 86400 ]; then echo "[$(date +%Y-%m-%d%H:%M:%S)] Full backup is overdue! Last backup: $LATEST_FULL_BACKUP" >> $LOG_FILE
# 发送告警邮件
mail -s “Backup Alert: Full backup is overdue” admin@example.com < $LOG_FILE fi fi # 检查最新的增量备份文件 LATEST_INCR_BACKUP=$(ls -t /backup/mysql/incremental/binlog_* 2>/dev/null | head -1)
if [ -z “$LATEST_INCR_BACKUP” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] No incremental backup files found!” >> $LOG_FILE
# 发送告警邮件
mail -s “Backup Alert: No incremental backup files found” admin@example.com < $LOG_FILE else # 检查增量备份文件的时间 BACKUP_TIME=$(stat -c %Y $LATEST_INCR_BACKUP) CURRENT_TIME=$(date +%s) TIME_DIFF=$((CURRENT_TIME - BACKUP_TIME)) if [ $TIME_DIFF -gt 3600 ]; then echo "[$(date +%Y-%m-%d%H:%M:%S)] Incremental backup is overdue! Last backup: $LATEST_INCR_BACKUP" >> $LOG_FILE
# 发送告警邮件
mail -s “Backup Alert: Incremental backup is overdue” admin@example.com < $LOG_FILE fi fi # 设置执行权限 chmod +x /mysql/scripts/monitor_backup.sh # 添加cron任务 # crontab -e 0 * * * * /mysql/scripts/monitor_backup.sh

# 清空数据目录
rm -rf /mysql/data/*

# 步骤2：选择备份文件
# 完全备份文件：/backup/mysql/full/full_backup_20260401_020000.sql.gz
# 增量备份文件：/backup/mysql/incremental/binlog_20260401_100000

# 步骤3：执行完全恢复
# 解压备份文件
gunzip -c /backup/mysql/full/full_backup_20260401_020000.sql.gz > /tmp/full_backup.sql

# 恢复备份
mysql –user=fgedu_admin –password=StrongPassword123! < /tmp/full_backup.sql # 步骤4：执行增量恢复 # 应用二进制日志 mysqlbinlog /backup/mysql/incremental/binlog_20260401_100000 | mysql --user=fgedu_admin --password=StrongPassword123! # 步骤5：启动MySQL服务 systemctl start mysqld # 步骤6：验证恢复结果 mysql -u fgedu_admin -pStrongPassword123! -e "SHOW DATABASES;" mysql -u fgedu_admin -pStrongPassword123! -e "SELECT COUNT(*) FROM fgedudb.fgedu_users;" # 步骤7：记录恢复信息 echo "[$(date +%Y-%m-%d%H:%M:%S)] Recovery completed from backup: /backup/mysql/full/full_backup_20260401_020000.sql.gz" >> /mysql/logs/recovery.log

# 恢复脚本
# vi /mysql/scripts/recover.sh
#!/bin/bash
# recover.sh

BACKUP_DIR=”/backup/mysql”
DB_USER=”fgedu_admin”
DB_PASS=”StrongPassword123!”

# 准备恢复环境
systemctl stop mysqld
rm -rf /mysql/data/*

# 选择最新的完全备份文件
LATEST_FULL_BACKUP=$(ls -t $BACKUP_DIR/full/*.sql.gz 2>/dev/null | head -1)
if [ -z “$LATEST_FULL_BACKUP” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] No full backup files found!” >> /mysql/logs/recovery.log
exit 1
fi

# 执行完全恢复
echo “[$(date +%Y-%m-%d%H:%M:%S)] Starting recovery from backup: $LATEST_FULL_BACKUP” >> /mysql/logs/recovery.log
gunzip -c $LATEST_FULL_BACKUP > /tmp/full_backup.sql
mysql –user=$DB_USER –password=$DB_PASS < /tmp/full_backup.sql # 执行增量恢复 LATEST_INCR_BACKUP=$(ls -t $BACKUP_DIR/incremental/binlog_* 2>/dev/null | head -1)
if [ ! -z “$LATEST_INCR_BACKUP” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] Applying incremental backup: $LATEST_INCR_BACKUP” >> /mysql/logs/recovery.log
mysqlbinlog $LATEST_INCR_BACKUP | mysql –user=$DB_USER –password=$DB_PASS
fi

# 启动MySQL服务
systemctl start mysqld

# 验证恢复结果
echo “[$(date +%Y-%m-%d%H:%M:%S)] Recovery completed” >> /mysql/logs/recovery.log
mysql -u $DB_USER -p$DB_PASS -e “SHOW DATABASES;” >> /mysql/logs/recovery.log

# 设置执行权限
chmod +x /mysql/scripts/recover.sh

# 重启MySQL服务
systemctl restart mysqld

# 创建复制用户
mysql> CREATE USER ‘repl’@’192.168.1.%’ IDENTIFIED BY ‘ReplPassword123!’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’192.168.1.%’;
mysql> FLUSH PRIVILEGES;

# 获取主库状态
mysql> SHOW MASTER STATUS;
+—————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+—————+———-+————–+——————+——————-+
| binlog.000001 | 123 | | | |
+—————+———-+————–+——————+——————-+

# 从库配置
# vi /etc/my.cnf
[mysqld]
server-id = 2
relay-log = /mysql/logs/relay-bin

# 重启MySQL服务
systemctl restart mysqld

# 配置从库复制
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.100′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’ReplPassword123!’,
-> MASTER_LOG_FILE=’binlog.000001′,
-> MASTER_LOG_POS=123;

# 启动复制
mysql> START SLAVE;

# 检查复制状态
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 123
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 123
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 123
Relay_Log_Space: 123
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 12345678-1234-1234-1234-1234567890ab
Master_Info_File: /mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

# 步骤2：配置灾备监控
# vi /mysql/scripts/monitor_replication.sh
#!/bin/bash
# monitor_replication.sh

LOG_FILE=”/mysql/logs/replication_monitor.log”

# 检查复制状态
SLAVE_STATUS=$(mysql -u fgedu_admin -pStrongPassword123! -e “SHOW SLAVE STATUS\G” | grep -E “Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master”)

if echo “$SLAVE_STATUS” | grep -q “Slave_IO_Running: No”; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] Slave IO is not running!” >> $LOG_FILE
# 发送告警邮件
mail -s “Replication Alert: Slave IO is not running” admin@example.com < $LOG_FILE fi if echo "$SLAVE_STATUS" | grep -q "Slave_SQL_Running: No"; then echo "[$(date +%Y-%m-%d%H:%M:%S)] Slave SQL is not running!" >> $LOG_FILE
# 发送告警邮件
mail -s “Replication Alert: Slave SQL is not running” admin@example.com < $LOG_FILE fi SECONDS_BEHIND_MASTER=$(echo "$SLAVE_STATUS" | grep "Seconds_Behind_Master" | awk '{print $2}') if [ "$SECONDS_BEHIND_MASTER" -gt 300 ]; then echo "[$(date +%Y-%m-%d%H:%M:%S)] Slave is behind master by $SECONDS_BEHIND_MASTER seconds!" >> $LOG_FILE
# 发送告警邮件
mail -s “Replication Alert: Slave is behind master” admin@example.com < $LOG_FILE fi # 设置执行权限 chmod +x /mysql/scripts/monitor_replication.sh # 添加cron任务 # crontab -e */5 * * * * /mysql/scripts/monitor_replication.sh # 步骤3：配置灾备切换脚本 # vi /mysql/scripts/failover.sh #!/bin/bash # failover.sh # 停止从库复制 mysql -u fgedu_admin -pStrongPassword123! -e "STOP SLAVE;" # 提升从库为主库 mysql -u fgedu_admin -pStrongPassword123! -e "RESET SLAVE ALL;" mysql -u fgedu_admin -pStrongPassword123! -e "RESET MASTER;" # 配置新的主库 # vi /etc/my.cnf [mysqld] server-id = 2 log-bin = /mysql/logs/binlog binlog-format = ROW # 重启MySQL服务 systemctl restart mysqld # 记录切换信息 echo "[$(date +%Y-%m-%d%H:%M:%S)] Failover completed: slave promoted to master" >> /mysql/logs/failover.log

# 设置执行权限
chmod +x /mysql/scripts/failover.sh

# 步骤1：配置备份策略
# 完全备份：每天凌晨2点执行
# 增量备份：每小时执行
# 备份工具：mysqldump
# 备份存储：本地磁盘

# 步骤2：创建备份脚本
# 完全备份脚本
# vi /mysql/scripts/backup_small_full.sh
#!/bin/bash
# backup_small_full.sh

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

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

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

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

# 检查备份文件大小
du -h $BACKUP_DIR/full/full_backup_$DATE.sql.gz >> /mysql/logs/backup.log

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

# 增量备份脚本
# vi /mysql/scripts/backup_small_incr.sh
#!/bin/bash
# backup_small_incr.sh

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

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

# 执行增量备份（基于二进制日志）
mysqlbinlog –raw –read-from-remote-server –host=localhost –user=$DB_USER –password=$DB_PASS –result-file=$BACKUP_DIR/incremental/binlog_$DATE /mysql/logs/binlog.*

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

# 删除3天前的增量备份文件
find $BACKUP_DIR/incremental -name “binlog_*” -mtime +3 -delete

# 步骤3：设置执行权限
chmod +x /mysql/scripts/backup_small_full.sh /mysql/scripts/backup_small_incr.sh

# 步骤4：添加cron任务
# crontab -e
0 2 * * * /mysql/scripts/backup_small_full.sh
0 * * * * /mysql/scripts/backup_small_incr.sh

# 步骤5：验证备份执行情况
ls -l /backup/mysql/full/
-rw-r–r– 1 root root 2.5G Apr 1 02:00 full_backup_20260401_020000.sql.gz

ls -l /backup/mysql/incremental/
-rw-r–r– 1 root root 50M Apr 1 10:00 binlog_20260401_100000
-rw-r–r– 1 root root 60M Apr 1 11:00 binlog_20260401_110000

# 步骤6：恢复测试
# 执行恢复测试
# vi /mysql/scripts/test_recovery.sh
#!/bin/bash
# test_recovery.sh

BACKUP_DIR=”/backup/mysql”
DB_USER=”fgedu_admin”
DB_PASS=”StrongPassword123!”

# 创建测试数据库
mysql -u $DB_USER -p$DB_PASS -e “CREATE DATABASE test_recovery;”
mysql -u $DB_USER -p$DB_PASS -e “CREATE TABLE test_recovery.test_table (id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR(100));”
mysql -u $DB_USER -p$DB_PASS -e “INSERT INTO test_recovery.test_table (data) VALUES (‘test data’);”

# 备份测试数据库
mysqldump –user=$DB_USER –password=$DB_PASS test_recovery | gzip -5 > $BACKUP_DIR/test_recovery.sql.gz

# 删除测试数据库
mysql -u $DB_USER -p$DB_PASS -e “DROP DATABASE test_recovery;”

# 恢复测试数据库
gunzip -c $BACKUP_DIR/test_recovery.sql.gz | mysql –user=$DB_USER –password=$DB_PASS

# 验证恢复结果
mysql -u $DB_USER -p$DB_PASS -e “SELECT * FROM test_recovery.test_table;”
+—-+———-+
| id | data |
+—-+———-+
| 1 | test data |
+—-+———-+

# 清理测试文件
rm $BACKUP_DIR/test_recovery.sql.gz
mysql -u $DB_USER -p$DB_PASS -e “DROP DATABASE test_recovery;”

# 执行恢复测试
bash /mysql/scripts/test_recovery.sh

# 步骤1：配置备份策略
# 完全备份：每周日凌晨2点执行
# 差异备份：每天凌晨2点执行（除周日）
# 增量备份：每小时执行
# 备份工具：xtrabackup
# 备份存储：NFS

# 步骤2：配置NFS存储
# 挂载NFS共享
echo “192.168.1.100:/export/mysql/backup /backup/mysql nfs defaults 0 0″ >> /etc/fstab
mount -a

# 步骤3：创建备份脚本
# 完全备份脚本
# vi /mysql/scripts/backup_medium_full.sh
#!/bin/bash
# backup_medium_full.sh

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

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

# 执行完全备份
extrabackup –backup –target-dir=$BACKUP_DIR/full/full_$DATE –user=$DB_USER –password=$DB_PASS

# 记录备份信息
echo “[$(date +%Y-%m-%d%H:%M:%S)] Full backup completed: $BACKUP_DIR/full/full_$DATE” >> /mysql/logs/backup.log

# 检查备份文件大小
du -h $BACKUP_DIR/full/full_$DATE >> /mysql/logs/backup.log

# 删除4周前的完全备份文件
find $BACKUP_DIR/full -name “full_*” -mtime +28 -exec rm -rf {} \;

# 差异备份脚本
# vi /mysql/scripts/backup_medium_diff.sh
#!/bin/bash
# backup_medium_diff.sh

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

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

# 查找最新的完全备份
LATEST_FULL_BACKUP=$(ls -t $BACKUP_DIR/full/ | head -1)

# 执行差异备份
extrabackup –backup –target-dir=$BACKUP_DIR/diff/diff_$DATE –incremental-basedir=$BACKUP_DIR/full/$LATEST_FULL_BACKUP –user=$DB_USER –password=$DB_PASS

# 记录备份信息
echo “[$(date +%Y-%m-%d%H:%M:%S)] Differential backup completed: $BACKUP_DIR/diff/diff_$DATE” >> /mysql/logs/backup.log

# 检查备份文件大小
du -h $BACKUP_DIR/diff/diff_$DATE >> /mysql/logs/backup.log

# 删除7天前的差异备份文件
find $BACKUP_DIR/diff -name “diff_*” -mtime +7 -exec rm -rf {} \;

# 增量备份脚本
# vi /mysql/scripts/backup_medium_incr.sh
#!/bin/bash
# backup_medium_incr.sh

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

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

# 执行增量备份（基于二进制日志）
mysqlbinlog –raw –read-from-remote-server –host=localhost –user=$DB_USER –password=$DB_PASS –result-file=$BACKUP_DIR/incremental/binlog_$DATE /mysql/logs/binlog.*

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

# 删除3天前的增量备份文件
find $BACKUP_DIR/incremental -name “binlog_*” -mtime +3 -delete

# 步骤4：设置执行权限
chmod +x /mysql/scripts/backup_medium_full.sh /mysql/scripts/backup_medium_diff.sh /mysql/scripts/backup_medium_incr.sh

# 步骤5：添加cron任务
# crontab -e
0 2 * * 0 /mysql/scripts/backup_medium_full.sh
0 2 * * 1-6 /mysql/scripts/backup_medium_diff.sh
0 * * * * /mysql/scripts/backup_medium_incr.sh

# 步骤6：验证备份执行情况
ls -l /backup/mysql/full/
drwxr-xr-x 2 root root 4096 Apr 5 02:00 full_20260405_020000

ls -l /backup/mysql/diff/
drwxr-xr-x 2 root root 4096 Apr 1 02:00 diff_20260401_020000

ls -l /backup/mysql/incremental/
-rw-r–r– 1 root root 200M Apr 1 10:00 binlog_20260401_100000
-rw-r–r– 1 root root 250M Apr 1 11:00 binlog_20260401_110000

# 步骤1：配置备份策略
# 完全备份：每月1日凌晨2点执行
# 差异备份：每周日凌晨2点执行（除每月1日）
# 增量备份：每小时执行
# 备份工具：MySQL Enterprise Backup
# 备份存储：云存储（AWS S3）

# 步骤2：配置云存储
# 安装AWS CLI
pip install awscli

# 配置AWS凭证
aws configure

# 创建S3存储桶
aws s3 mb s3://mysql-backup-bucket

# 步骤3：创建备份脚本
# 完全备份脚本
# vi /mysql/scripts/backup_large_full.sh
#!/bin/bash
# backup_large_full.sh

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

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

# 执行完全备份
mysqlbackup –user=$DB_USER –password=$DB_PASS –backup-dir=$BACKUP_DIR/full_$DATE backup

# 压缩备份文件
tar -czf $BACKUP_DIR/full_$DATE.tar.gz $BACKUP_DIR/full_$DATE

# 上传到S3
aws s3 cp $BACKUP_DIR/full_$DATE.tar.gz s3://$S3_BUCKET/full/

# 记录备份信息
echo “[$(date +%Y-%m-%d%H:%M:%S)] Full backup completed: s3://$S3_BUCKET/full/full_$DATE.tar.gz” >> /mysql/logs/backup.log

# 检查备份文件大小
du -h $BACKUP_DIR/full_$DATE.tar.gz >> /mysql/logs/backup.log

# 清理本地备份文件
rm -rf $BACKUP_DIR/full_$DATE $BACKUP_DIR/full_$DATE.tar.gz

# 差异备份脚本
# vi /mysql/scripts/backup_large_diff.sh
#!/bin/bash
# backup_large_diff.sh

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

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

# 查找最新的完全备份（从S3下载）
LATEST_FULL_BACKUP=$(aws s3 ls s3://$S3_BUCKET/full/ | sort -r | head -1 | awk ‘{print $4}’ | sed ‘s/.tar.gz//’)
aws s3 cp s3://$S3_BUCKET/full/$LATEST_FULL_BACKUP.tar.gz $BACKUP_DIR/
tar -xzf $BACKUP_DIR/$LATEST_FULL_BACKUP.tar.gz -C $BACKUP_DIR/

# 执行差异备份
mysqlbackup –user=$DB_USER –password=$DB_PASS –backup-dir=$BACKUP_DIR/diff_$DATE –incremental-basedir=$BACKUP_DIR/$LATEST_FULL_BACKUP backup

# 压缩备份文件
tar -czf $BACKUP_DIR/diff_$DATE.tar.gz $BACKUP_DIR/diff_$DATE

# 上传到S3
aws s3 cp $BACKUP_DIR/diff_$DATE.tar.gz s3://$S3_BUCKET/diff/

# 记录备份信息
echo “[$(date +%Y-%m-%d%H:%M:%S)] Differential backup completed: s3://$S3_BUCKET/diff/diff_$DATE.tar.gz” >> /mysql/logs/backup.log

# 检查备份文件大小
du -h $BACKUP_DIR/diff_$DATE.tar.gz >> /mysql/logs/backup.log

# 清理本地备份文件
rm -rf $BACKUP_DIR/$LATEST_FULL_BACKUP $BACKUP_DIR/$LATEST_FULL_BACKUP.tar.gz $BACKUP_DIR/diff_$DATE $BACKUP_DIR/diff_$DATE.tar.gz

# 增量备份脚本
# vi /mysql/scripts/backup_large_incr.sh
#!/bin/bash
# backup_large_incr.sh

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

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

# 执行增量备份（基于二进制日志）
mysqlbinlog –raw –read-from-remote-server –host=localhost –user=$DB_USER –password=$DB_PASS –result-file=$BACKUP_DIR/binlog_$DATE /mysql/logs/binlog.*

# 压缩备份文件
tar -czf $BACKUP_DIR/binlog_$DATE.tar.gz $BACKUP_DIR/binlog_$DATE*

# 上传到S3
aws s3 cp $BACKUP_DIR/binlog_$DATE.tar.gz s3://$S3_BUCKET/incremental/

# 记录备份信息
echo “[$(date +%Y-%m-%d%H:%M:%S)] Incremental backup completed: s3://$S3_BUCKET/incremental/binlog_$DATE.tar.gz” >> /mysql/logs/backup.log

# 清理本地备份文件
rm -rf $BACKUP_DIR/binlog_$DATE* $BACKUP_DIR/binlog_$DATE.tar.gz

# 步骤4：设置执行权限
chmod +x /mysql/scripts/backup_large_full.sh /mysql/scripts/backup_large_diff.sh /mysql/scripts/backup_large_incr.sh

# 步骤5：添加cron任务
# crontab -e
0 2 1 * * /mysql/scripts/backup_large_full.sh
0 2 * * 0 /mysql/scripts/backup_large_diff.sh
0 * * * * /mysql/scripts/backup_large_incr.sh

# 步骤6：验证备份执行情况
# 查看S3存储桶中的文件
aws s3 ls s3://mysql-backup-bucket/full/
2026-04-01 02:00:00 100G full_20260401_020000.tar.gz

aws s3 ls s3://mysql-backup-bucket/diff/
2026-04-07 02:00:00 20G diff_20260407_020000.tar.gz

aws s3 ls s3://mysql-backup-bucket/incremental/
2026-04-01 10:00:00 5G binlog_20260401_100000.tar.gz
2026-04-01 11:00:00 6G binlog_20260401_110000.tar.gz

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

# 记录开始时间
echo “[$(date +%Y-%m-%d%H:%M:%S)] Backup automation started” >> $LOG_FILE

# 创建备份目录
mkdir -p $BACKUP_DIR/full $BACKUP_DIR/incremental $BACKUP_DIR/diff

# 检查备份类型
case “$1” in
“full”)
# 执行完全备份
echo “[$(date +%Y-%m-%d%H:%M:%S)] Starting full backup” >> $LOG_FILE
mysqldump –user=$DB_USER –password=$DB_PASS –single-transaction –flush-logs –master-data=2 –all-databases | gzip -5 > $BACKUP_DIR/full/full_backup_$DATE.sql.gz
if [ $? -eq 0 ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] Full backup completed successfully” >> $LOG_FILE
# 检查备份文件大小
du -h $BACKUP_DIR/full/full_backup_$DATE.sql.gz >> $LOG_FILE
# 删除7天前的完全备份文件
find $BACKUP_DIR/full -name “full_backup_*.sql.gz” -mtime +7 -delete
else
echo “[$(date +%Y-%m-%d%H:%M:%S)] Full backup failed” >> $LOG_FILE
# 发送告警邮件
mail -s “Backup Alert: Full backup failed” admin@example.com < $LOG_FILE fi ;; "incremental") # 执行增量备份 echo "[$(date +%Y-%m-%d%H:%M:%S)] Starting incremental backup" >> $LOG_FILE
mysqlbinlog –raw –read-from-remote-server –host=localhost –user=$DB_USER –password=$DB_PASS –result-file=$BACKUP_DIR/incremental/binlog_$DATE /mysql/logs/binlog.*
if [ $? -eq 0 ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] Incremental backup completed successfully” >> $LOG_FILE
# 删除3天前的增量备份文件
find $BACKUP_DIR/incremental -name “binlog_*” -mtime +3 -delete
else
echo “[$(date +%Y-%m-%d%H:%M:%S)] Incremental backup failed” >> $LOG_FILE
# 发送告警邮件
mail -s “Backup Alert: Incremental backup failed” admin@example.com < $LOG_FILE fi ;; "diff") # 执行差异备份 echo "[$(date +%Y-%m-%d%H:%M:%S)] Starting differential backup" >> $LOG_FILE
# 查找最新的完全备份
LATEST_FULL_BACKUP=$(ls -t $BACKUP_DIR/full/*.sql.gz 2>/dev/null | head -1)
if [ -z “$LATEST_FULL_BACKUP” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] No full backup found, cannot perform differential backup” >> $LOG_FILE
# 发送告警邮件
mail -s “Backup Alert: No full backup found” admin@example.com < $LOG_FILE exit 1 fi # 提取完全备份的binlog位置 BINLOG_POS=$(grep "CHANGE MASTER TO" $LATEST_FULL_BACKUP | head -1 | awk -F'=' '{print $3}' | awk '{print $1}') BINLOG_FILE=$(grep "CHANGE MASTER TO" $LATEST_FULL_BACKUP | head -1 | awk -F'=' '{print $2}' | awk '{print $1}') # 执行差异备份（基于binlog） mysqlbinlog --start-position=$BINLOG_POS $BINLOG_FILE /mysql/logs/binlog.* | gzip -5 > $BACKUP_DIR/diff/diff_backup_$DATE.sql.gz
if [ $? -eq 0 ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] Differential backup completed successfully” >> $LOG_FILE
# 检查备份文件大小
du -h $BACKUP_DIR/diff/diff_backup_$DATE.sql.gz >> $LOG_FILE
# 删除7天前的差异备份文件
find $BACKUP_DIR/diff -name “diff_backup_*.sql.gz” -mtime +7 -delete
else
echo “[$(date +%Y-%m-%d%H:%M:%S)] Differential backup failed” >> $LOG_FILE
# 发送告警邮件
mail -s “Backup Alert: Differential backup failed” admin@example.com < $LOG_FILE fi ;; *) echo "[$(date +%Y-%m-%d%H:%M:%S)] Invalid backup type. Usage: $0 {full|incremental|diff}" >> $LOG_FILE
exit 1
;;
esac

# 记录结束时间
echo “[$(date +%Y-%m-%d%H:%M:%S)] Backup automation completed” >> $LOG_FILE

# 步骤2：设置执行权限
chmod +x /mysql/scripts/backup_automation.sh

# 步骤3：添加cron任务
# crontab -e
0 2 * * * /mysql/scripts/backup_automation.sh full
0 * * * * /mysql/scripts/backup_automation.sh incremental
0 3 * * 1-6 /mysql/scripts/backup_automation.sh diff

# 步骤4：创建恢复自动化脚本
# vi /mysql/scripts/recovery_automation.sh
#!/bin/bash
# recovery_automation.sh

BACKUP_DIR=”/backup/mysql”
DB_USER=”fgedu_admin”
DB_PASS=”StrongPassword123!”
LOG_FILE=”/mysql/logs/recovery_automation.log”

# 记录开始时间
echo “[$(date +%Y-%m-%d%H:%M:%S)] Recovery automation started” >> $LOG_FILE

# 准备恢复环境
echo “[$(date +%Y-%m-%d%H:%M:%S)] Preparing recovery environment” >> $LOG_FILE
systemctl stop mysqld
rm -rf /mysql/data/*

# 选择最新的完全备份文件
LATEST_FULL_BACKUP=$(ls -t $BACKUP_DIR/full/*.sql.gz 2>/dev/null | head -1)
if [ -z “$LATEST_FULL_BACKUP” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] No full backup files found!” >> $LOG_FILE
# 发送告警邮件
mail -s “Recovery Alert: No full backup files found” admin@example.com < $LOG_FILE exit 1 fi # 执行完全恢复 echo "[$(date +%Y-%m-%d%H:%M:%S)] Starting recovery from backup: $LATEST_FULL_BACKUP" >> $LOG_FILE
gunzip -c $LATEST_FULL_BACKUP > /tmp/full_backup.sql
mysql –user=$DB_USER –password=$DB_PASS < /tmp/full_backup.sql if [ $? -eq 0 ]; then echo "[$(date +%Y-%m-%d%H:%M:%S)] Full recovery completed successfully" >> $LOG_FILE
else
echo “[$(date +%Y-%m-%d%H:%M:%S)] Full recovery failed” >> $LOG_FILE
# 发送告警邮件
mail -s “Recovery Alert: Full recovery failed” admin@example.com < $LOG_FILE exit 1 fi # 执行差异恢复 LATEST_DIFF_BACKUP=$(ls -t $BACKUP_DIR/diff/*.sql.gz 2>/dev/null | head -1)
if [ ! -z “$LATEST_DIFF_BACKUP” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] Applying differential backup: $LATEST_DIFF_BACKUP” >> $LOG_FILE
gunzip -c $LATEST_DIFF_BACKUP | mysql –user=$DB_USER –password=$DB_PASS
if [ $? -eq 0 ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] Differential recovery completed successfully” >> $LOG_FILE
else
echo “[$(date +%Y-%m-%d%H:%M:%S)] Differential recovery failed” >> $LOG_FILE
# 发送告警邮件
mail -s “Recovery Alert: Differential recovery failed” admin@example.com < $LOG_FILE fi fi # 执行增量恢复 LATEST_INCR_BACKUP=$(ls -t $BACKUP_DIR/incremental/binlog_* 2>/dev/null | head -1)
if [ ! -z “$LATEST_INCR_BACKUP” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] Applying incremental backup: $LATEST_INCR_BACKUP” >> $LOG_FILE
mysqlbinlog $LATEST_INCR_BACKUP | mysql –user=$DB_USER –password=$DB_PASS
if [ $? -eq 0 ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] Incremental recovery completed successfully” >> $LOG_FILE
else
echo “[$(date +%Y-%m-%d%H:%M:%S)] Incremental recovery failed” >> $LOG_FILE
# 发送告警邮件
mail -s “Recovery Alert: Incremental recovery failed” admin@example.com < $LOG_FILE fi fi # 启动MySQL服务 echo "[$(date +%Y-%m-%d%H:%M:%S)] Starting MySQL service" >> $LOG_FILE
systemctl start mysqld

# 验证恢复结果
echo “[$(date +%Y-%m-%d%H:%M:%S)] Verifying recovery result” >> $LOG_FILE
mysql -u $DB_USER -p$DB_PASS -e “SHOW DATABASES;” >> $LOG_FILE
mysql -u $DB_USER -p$DB_PASS -e “SELECT COUNT(*) FROM fgedudb.fgedu_users;” >> $LOG_FILE

# 记录结束时间
echo “[$(date +%Y-%m-%d%H:%M:%S)] Recovery automation completed” >> $LOG_FILE

# 步骤5：设置执行权限
chmod +x /mysql/scripts/recovery_automation.sh

# 步骤6：测试自动化脚本
# 执行完全备份
bash /mysql/scripts/backup_automation.sh full

# 执行增量备份
bash /mysql/scripts/backup_automation.sh incremental

# 执行差异备份
bash /mysql/scripts/backup_automation.sh diff

# 查看日志
tail -f /mysql/logs/backup_automation.log