MySQL 二进制日志与增量备份

二进制日志（binlog）记录数据库变更操作，是增量备份和精确恢复的核心。

二进制日志基础

binlog 配置

-- 查看binlog状态
SHOW VARIABLES LIKE 'log_bin';

-- 开启binlog（my.cnf配置）
[mysqld]
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M

-- 查看binlog文件列表
SHOW BINARY LOGS;

-- 查看当前binlog位置
SHOW MASTER STATUS;

binlog 格式

-- 设置binlog格式
SET GLOBAL binlog_format = 'ROW';

查看 binlog 内容

# 使用 mysqlbinlog 工具查看
mysqlbinlog mysql-bin.000001

# 查看指定时间范围
mysqlbinlog --start-datetime="2024-01-15 10:00:00" \
            --stop-datetime="2024-01-15 12:00:00" \
            mysql-bin.000001

# 查看指定位置范围
mysqlbinlog --start-position=1000 --stop-position=2000 \
            mysql-bin.000001

# 解析为SQL语句（ROW格式）
mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001

增量备份策略

完整备份 + binlog增量

# 1. 完全备份（记录binlog位置）
mysqldump -u root -p --single-transaction --master-data=2 \
  --flush-logs mydb > mydb_full.sql

# 备份文件中包含注释：
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003',
-- MASTER_LOG_POS=154;

# 2. 定期刷新binlog（创建新binlog文件）
FLUSH LOGS;

# 3. 备份旧binlog文件
cp mysql-bin.000002 /backup/

# 4. binlog自动过期删除（配置expire_logs_days）

Point-in-Time 恢复

# 场景：误删数据，恢复到误操作前

# 1. 恢复完全备份
mysql -u root -p mydb < mydb_full.sql

# 2. 应用binlog到误操作前的时间点
mysqlbinlog --start-position=154 \
            --stop-datetime="2024-01-15 11:30:00" \
            mysql-bin.000003 mysql-bin.000004 | mysql -u root -p

# 3. 数据恢复到指定时间点

恢复误删操作示例

-- 假设误删操作发生在 2024-01-15 11:35:00
DELETE FROM users WHERE id < 100;  -- 误操作

-- 恢复步骤
-- 1. 立即停止新写入或锁定表
FLUSH TABLES WITH READ LOCK;

-- 2. 恢复完全备份
mysql -u root -p mydb < mydb_full.sql

-- 3. 应用binlog到误操作前（stop-datetime设为11:35之前）
mysqlbinlog --stop-datetime="2024-01-15 11:34:59" \
            mysql-bin.* | mysql -u root -p

binlog 管理

-- 查看binlog事件
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

-- 删除指定binlog
PURGE BINARY LOGS TO 'mysql-bin.000005';

-- 删除指定时间前的binlog
PURGE BINARY LOGS BEFORE '2024-01-20 00:00:00';

-- 手动刷新binlog
FLUSH LOGS;

-- 重置binlog（删除所有）
RESET MASTER;

自动备份脚本

#!/bin/bash
# binlog_backup.sh - 每小时备份binlog

BACKUP_DIR="/backup/binlog"
DATE=$(date +%Y%m%d_%H%M%S)

# 刷新binlog创建新文件
mysql -u root -p'password' -e "FLUSH LOGS"

# 备份除当前活跃binlog外的所有binlog
CURRENT_BINLOG=$(mysql -u root -p'password' -e "SHOW MASTER STATUS" \
  | awk 'NR==2 {print $1}')

for binlog in $(ls /var/lib/mysql/mysql-bin.* | grep -v $CURRENT_BINLOG); do
  cp $binlog $BACKUP_DIR/
done

# 压缩备份
gzip $BACKUP_DIR/mysql-bin.*.$DATE.gz

binlog是实现增量备份、主从复制、数据恢复的核心，生产环境必须开启。

要点总结

• binlog记录所有数据变更，ROW格式最精确
• 完全备份 + binlog实现增量备份
• Point-in-Time恢复可精确恢复到任意时间点
• mysqlbinlog工具解析和应用binlog
• 配置expire_logs_days自动清理过期binlog