# 恢复策略示例
– 完全备份：每天凌晨2:00
– 增量备份：每小时一次
– 二进制日志保留：14天
– 恢复测试：每周一次
– 恢复演练：每月一次

# 测试启用二进制日志前后的性能
# 启用前
# mysqlslap –user=fgedu_admin –password=StrongPassword123! –host=192.168.1.100 –port=3306
–concurrency=100 –iterations=10 –query=”INSERT INTO fgedudb.fgedu_test VALUES (NULL, ‘test’)”

Benchmark
Average number of seconds to run all queries: 0.125 seconds
Minimum number of seconds to run all queries: 0.105 seconds
Maximum number of seconds to run all queries: 0.156 seconds
Number of clients running queries: 100
Average number of queries per client: 1

# 启用后
# mysqlslap –user=fgedu_admin –password=StrongPassword123! –host=192.168.1.100 –port=3306
–concurrency=100 –iterations=10 –query=”INSERT INTO fgedudb.fgedu_test VALUES (NULL, ‘test’)”

Benchmark
Average number of seconds to run all queries: 0.156 seconds
Minimum number of seconds to run all queries: 0.135 seconds
Maximum number of seconds to run all queries: 0.187 seconds
Number of clients running queries: 100
Average number of queries per client: 1

# 编辑MySQL配置文件
# vi /etc/my.cnf
[mysqld]
# 启用二进制日志
log_bin = /mysql/logs/binlog
# 二进制日志格式
binlog_format = ROW
# 二进制日志过期时间（天）
expire_logs_days = 14
# 二进制日志大小限制（MB）
max_binlog_size = 1024M
# 服务器ID
server_id = 1
# 启用二进制日志校验
binlog_checksum = CRC32
# 启用二进制日志事务压缩
binlog_transaction_compression = ON

# 重启MySQL服务
# systemctl restart mysqld

# 验证二进制日志配置
mysql> SHOW VARIABLES LIKE ‘log_bin%’;
+———————————+—————————–+
| Variable_name | Value |
+———————————+—————————–+
| log_bin | ON |
| log_bin_basename | /mysql/logs/binlog |
| log_bin_index | /mysql/logs/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+———————————+—————————–+
5 rows in set (0.00 sec)

# 编写备份脚本，包含二进制日志位置记录
# vi /mysql/scripts/backup_with_binlog.sh
#!/bin/bash
# backup_with_binlog.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

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

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

# 获取当前二进制日志位置
BINLOG_POS=$(mysql –user=$DB_USER –password=$DB_PASS -e “SHOW MASTER STATUS” | grep -v File)

# 执行完全备份，包含二进制日志位置
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

# 记录备份信息，包括二进制日志位置
echo “[$(date +%Y-%m-%d%H:%M:%S)] Full backup completed: $BACKUP_DIR/full_backup_$DATE.sql.gz” >>
/mysql/logs/backup.log
echo “Binary log position: $BINLOG_POS” >> /mysql/logs/backup.log

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

# 恢复前准备工作
1. 确认备份文件的完整性
2. 收集所有需要的二进制日志文件
3. 确定恢复的时间点或位置
4. 停止应用对数据库的写入操作
5. 创建恢复测试环境（可选）
6. 制定恢复计划和回滚方案

# 步骤1：恢复完全备份
# gzip -d /backup/mysql/full/full_backup_20260401_020000.sql.gz
# mysql –user=fgedu_admin –password=StrongPassword123! < /backup/mysql/full/full_backup_20260401_020000.sql # 步骤2：查看备份文件中的二进制日志位置 # head -50 /backup/mysql/full/full_backup_20260401_020000.sql -- MySQL dump 10.13 Distrib 8.4.0, for Linux (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 8.4.0 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000002' , MASTER_LOG_POS=123456; # 步骤3：应用二进制日志到指定时间点 # mysqlbinlog --start-position=123456 --stop-datetime="2026-04-01 10:00:00" /mysql/logs/binlog.000002 /mysql/logs/binlog.000003 | mysql --user=fgedu_admin --password=StrongPassword123! # 步骤4：验证恢复结果 mysql>
SELECT * FROM fgedudb.fgedu_users LIMIT 10;
+—-+——+——————+————-+
| id | name | id_card | phone |
+—-+——+——————+————-+
| 1 | 张三 | 110101199001011234 | 13812345678 |
| 2 | 李四 | 310101199102022345 | 13987654321 |
| 3 | 王五 | 440101199203033456 | 13765432109 |
+—-+——+——————+————-+
3 rows in set (0.00 sec)

# 步骤1：恢复完全备份
# gzip -d /backup/mysql/full/full_backup_20260401_020000.sql.gz
# mysql –user=fgedu_admin –password=StrongPassword123! < /backup/mysql/full/full_backup_20260401_020000.sql # 步骤2：查看备份文件中的二进制日志位置 # head -50 /backup/mysql/full/full_backup_20260401_020000.sql -- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000002' , MASTER_LOG_POS=123456; # 步骤3：查看二进制日志文件，确定恢复的结束位置 # mysqlbinlog /mysql/logs/binlog.000002 | grep -n "DROP TABLE" 1234:DROP TABLE IF EXISTS `fgedu_users`; # 步骤4：应用二进制日志到指定位置 # mysqlbinlog --start-position=123456 --stop-position=1234 /mysql/logs/binlog.000002 | mysql --user=fgedu_admin --password=StrongPassword123! # 步骤5：验证恢复结果 mysql>
SHOW TABLES;
+——————-+
| Tables_in_fgedudb |
+——————-+
| fgedu_users |
| fgedu_orders |
+——————-+
2 rows in set (0.00 sec)

# 问题1：二进制日志文件损坏
# 解决方案：使用mysqlbinlog工具检查二进制日志文件
# mysqlbinlog –verify-binlog-checksum /mysql/logs/binlog.000002

# 问题2：恢复位置错误
# 解决方案：使用mysqlbinlog工具查看二进制日志内容，确定正确的恢复位置
# mysqlbinlog –start-position=123456 –stop-position=234567 /mysql/logs/binlog.000002 > /tmp/binlog.sql
# vi /tmp/binlog.sql

# 问题3：恢复过程中出现错误
# 解决方案：查看错误日志，分析错误原因
# tail -f /mysql/logs/error.log

# 问题4：二进制日志文件丢失
# 解决方案：从备份中恢复二进制日志文件，或使用最近的完全备份进行恢复