6.4.5 二进制日志管理与恢复演练

二进制日志管理与恢复演练#

1. 二进制日志定位与生命周期#

  • 作用：记录所有对数据有影响的变更，用于主从复制与基于时间点恢复（PITR）。
  • 关键参数：log_bin、binlog_format、binlog_row_image、binlog_expire_logs_seconds、sync_binlog。
  • 文件命名与索引：mysql-bin.000001，索引文件 mysql-bin.index 记录可用日志序列。

原理草图（binlog 生成与使用）：

文章图片

生命周期观察示例：

-- 查看 binlog 文件列表与大小
SHOW BINARY LOGS;
-- 查看当前写入位置
SHOW MASTER STATUS;

2. 开启与检查（含配置示例与命令解释）#

最小可用配置示例（/etc/my.cnf）：

[mysqld]
server_id=1
log_bin=/var/lib/mysql/mysql-bin
binlog_format=ROW
binlog_row_image=FULL
sync_binlog=1
binlog_expire_logs_seconds=604800

重启并检查：

# systemd 环境
systemctl restart mysqld
systemctl status mysqld

验证是否生效：

SHOW VARIABLES LIKE 'log_bin%';
SHOW VARIABLES LIKE 'binlog_format';
SHOW MASTER STATUS;

命令说明：
- log_bin：启用并指定 binlog 文件前缀路径。
- binlog_format=ROW：行级记录，恢复准确。
- binlog_row_image=FULL：行事件更完整，审计更精确。
- sync_binlog=1：每次提交刷盘，安全性高，性能略低。

3. 日志轮转与清理策略（含演练命令）#

自动过期：

SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

手动清理（清理到指定文件）：

PURGE BINARY LOGS TO 'mysql-bin.000123';

手动清理（按时间）：

PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';

清理前检查复制状态（避免从库缺日志）：

SHOW SLAVE STATUS\G

关注字段：
- Relay_Master_Log_File：从库需要的主库日志
- Exec_Master_Log_Pos：从库已执行的位置

4. 查看与解析（含可执行示例）#

解析某个 binlog 并输出可读事件：

mysqlbinlog --base64-output=DECODE-ROWS -v /var/lib/mysql/mysql-bin.000123 | less

按时间范围导出：

mysqlbinlog \
  --start-datetime="2024-06-01 10:00:00" \
  --stop-datetime="2024-06-01 10:30:00" \
  /var/lib/mysql/mysql-bin.000123 > /tmp/part.sql

按位置导出（适合精确恢复）：

mysqlbinlog \
  --start-position=120 \
  --stop-position=980 \
  /var/lib/mysql/mysql-bin.000123 > /tmp/pos.sql

预期效果：
- /tmp/part.sql 可读 SQL 或行事件（ROW）。
- 用于恢复、审计或误操作定位。

5. 基于时间点恢复（PITR）流程（完整演练）#

前提：已存在全量备份（如 mysqldump），并保存 binlog。

步骤 1：恢复全量备份到临时实例

# 假设备份文件为 /backup/full.sql
mysql -uroot -p < /backup/full.sql

步骤 2：提取备份时间点之后的 binlog

mysqlbinlog \
  --start-datetime="2024-06-01 00:00:00" \
  --stop-datetime="2024-06-01 12:00:00" \
  /var/lib/mysql/mysql-bin.000123 \
  /var/lib/mysql/mysql-bin.000124 \
  > /tmp/recover.sql

步骤 3：重放日志

mysql -uroot -p < /tmp/recover.sql

步骤 4：验证数据

-- 示例：验证关键表的记录数
SELECT COUNT(*) FROM app.orders;

6. 误操作回滚演练（含定位与回放）#

场景：误执行 DELETE FROM app.users WHERE id<100;

定位误操作时间窗口

mysqlbinlog --base64-output=DECODE-ROWS -v \
  --start-datetime="2024-06-01 09:00:00" \
  --stop-datetime="2024-06-01 09:30:00" \
  /var/lib/mysql/mysql-bin.000123 | grep -n "DELETE"

导出误操作前的安全窗口

mysqlbinlog \
  --start-datetime="2024-06-01 09:00:00" \
  --stop-datetime="2024-06-01 09:10:00" \
  /var/lib/mysql/mysql-bin.000123 > /tmp/safe.sql

在临时库回放并验证

mysql -uroot -p -e "CREATE DATABASE recover_test;"
mysql -uroot -p recover_test < /backup/full.sql
mysql -uroot -p recover_test < /tmp/safe.sql

回写数据（示例）

INSERT INTO app.users SELECT * FROM recover_test.app.users WHERE id<100;

注意：
- ROW 格式可准确复现行变更。
- STATEMENT 可能受非确定性函数影响（如 NOW()）。

7. 与复制链路的协同（状态检查示例）#

主从延迟与 relay log 监控：

SHOW SLAVE STATUS\G

重点字段说明：
- Seconds_Behind_Master：延迟秒数
- Relay_Log_Space：relay log 占用空间

复制异常排查（示例）：

SHOW SLAVE STATUS\G
-- 关注 Last_IO_Error / Last_SQL_Error

8. 审计与合规（留存与导出）#

导出敏感操作到审计存储：

mysqlbinlog --base64-output=DECODE-ROWS -v \
  --start-datetime="2024-06-01 00:00:00" \
  --stop-datetime="2024-06-01 23:59:59" \
  /var/lib/mysql/mysql-bin.000123 \
  | grep -E "DELETE|DROP|TRUNCATE" > /audit/binlog_sensitive_20240601.txt

推荐：
- 归档到 WORM 或对象存储
- 加密与访问控制（如 chmod 600）

9. 常见故障与排查（含命令）#

1）binlog 损坏

mysqlbinlog /var/lib/mysql/mysql-bin.000123
# 报错如 "Event too small" 或 "Could not read entry"

处理方式：
- 跳过损坏段，定位可读取的最后位置：

mysqlbinlog --start-position=4 /var/lib/mysql/mysql-bin.000123 > /tmp/ok.sql

2）日志缺失导致复制中断

SHOW SLAVE STATUS\G

可能报错：
- Could not find first log file name in binary log index file

解决：
- 从主库重新获取缺失的日志或重建从库。

3）写入性能下降

SHOW VARIABLES LIKE 'sync_binlog';
SHOW VARIABLES LIKE 'binlog_group_commit_sync_delay';

建议：
- 评估 sync_binlog=1 与磁盘性能
- 适当调整 binlog_group_commit_sync_delay

练习题（带操作指引）#

  1. 开启 binlog 并验证
    - 修改 /etc/my.cnf 启用 log_bin 和 server_id
    - 执行 SHOW MASTER STATUS; 验证生成

  2. 模拟误删除并恢复
    - 执行 DELETE FROM test.t WHERE id<5;
    - 使用 mysqlbinlog 定位并回放到误操作前

  3. 按时间导出审计日志
    - 导出当天 10:00-11:00 的 binlog
    - 筛选 DROP 语句写入审计文件