MySQL 数据库备份与恢复：Binlog 日志与定时备份脚本

一、Binlog 日志核心原理

1. 基本作用：记录所有修改数据库的操作（增删改），用于：
   • 数据恢复：基于时间点/位置恢复
   • 主从复制：同步数据变更
2. 记录模式：
   • STATEMENT：记录原始 SQL 语句
   • ROW：记录行数据变化（默认）
   • MIXED：混合模式

二、Binlog 配置步骤

1. 修改 MySQL 配置文件（my.cnf）：

[mysqld]
server-id = 1
log-bin = /var/log/mysql/mysql-bin  # binlog 存储路径
expire_logs_days = 7                # 自动清理旧日志
binlog_format = ROW                 # 推荐使用 ROW 模式

1. 重启服务生效：

sudo systemctl restart mysql

三、定时备份脚本实现

#!/bin/bash
# 全量备份 + binlog 增量备份脚本
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d)

# 1. 全量备份
mysqldump -u root -p'密码' --all-databases --single-transaction --flush-logs --master-data=2 > ${BACKUP_DIR}/full_${DATE}.sql

# 2. 获取当前 binlog 位置
BINLOG_FILE=$(mysql -u root -p'密码' -e "SHOW MASTER STATUS" | awk 'NR==2 {print $1}')
BINLOG_POS=$(mysql -u root -p'密码' -e "SHOW MASTER STATUS" | awk 'NR==2 {print $2}')

# 3. 记录位置信息
echo "${DATE} ${BINLOG_FILE} ${BINLOG_POS}" >> ${BACKUP_DIR}/binlog_index.txt

# 4. 清理旧备份（保留 30 天）
find ${BACKUP_DIR} -name "*.sql" -mtime +30 -exec rm {} \;

四、数据恢复流程

1. 恢复全量备份：

mysql -u root -p'密码' < /backup/mysql/full_20231001.sql

1. 增量恢复（通过 binlog）：

# 根据记录的 binlog 位置恢复
mysqlbinlog --start-position=154 /var/log/mysql/mysql-bin.000002 | mysql -u root -p'密码'

# 按时间点恢复（恢复 10:00-12:00 的数据）
mysqlbinlog --start-datetime="2023-10-01 10:00:00" --stop-datetime="2023-10-01 12:00:00" /var/log/mysql/mysql-bin.* | mysql -u root -p'密码'

五、关键注意事项

1. 备份验证：
   • 定期执行 SHOW BINARY LOGS 确认日志完整性
   • 使用 mysqlbinlog -v 检查日志内容
2. 安全措施：
   • 备份文件设置权限：chmod 600 /backup/mysql/*
   • 异地存储备份（如 S3、NFS）
3. 监控建议：
   • 监控磁盘空间：du -sh /var/log/mysql/
   • 设置备份失败告警（通过脚本返回值）

最佳实践：每日全量备份 + 实时 binlog，恢复时可实现秒级 RPO（恢复点目标）。建议每周在测试环境验证恢复流程。