Binlog 核心認知

二進制日志 是 MySQL 的「時光機」，記錄所有數據變更操作。掌握其清理與恢復技能，是 DBA 從「會用」到「敢用」的關鍵跨越。

第一部分：安全清理 Binlog

❌ 危險操作：直接 rm 刪除文件

會導致 mysql-bin.index 與實際文件不一致，MySQL 重啟報錯甚至拒絕啟動。

✅ 正規清理三大法

命令	適用場景	示例
RESET MASTER	重置所有日志，從 000001 重新開始 慎用！會斷開所有從庫同步	RESET MASTER;
PURGE MASTER LOGS TO	刪除指定文件之前的所有日志	PURGE MASTER LOGS TO 'mysql-bin.000010';
PURGE MASTER LOGS BEFORE	刪除指定時間之前的日志	PURGE MASTER LOGS BEFORE '2024-01-15 12:00:00';

🛡️ 長效策略：自動過期配置

[mysqld]
expire_logs_days = 7        # 保留 7 天
max_binlog_size = 100M      # 單文件最大 100MB，防止單文件過大
binlog_expire_logs_seconds = 604800  # MySQL 8.0+ 推薦用秒數

第二部分：Binlog 數據恢復實戰

場景模擬：誤執行 DROP TABLE

-- 1. 準備環境
CREATE DATABASE recover_demo;
USE recover_demo;
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO users VALUES (1,'Alice'),(2,'Bob'),(3,'Cathy');
FLUSH LOGS;  -- 產生新 binlog 文件 mysql-bin.000007

-- 2. 正常業務操作
INSERT INTO users VALUES (4,'David'),(5,'Eva');

-- 3. 災難發生：誤刪表
DROP TABLE users;
FLUSH LOGS;  -- 產生 mysql-bin.000008

恢復步驟

1. 定位關鍵位置：使用 mysqlbinlog 查看日志事件
2. 提取目標區間：從全備恢復點 到 DROP TABLE 之前
3. 重放日志：管道導入 MySQL

實操命令

# 1. 查看日志結構，找到 DROP TABLE 位置
mysqlbinlog --base64-output=DECODE-ROWS -v /var/lib/mysql/mysql-bin.000007 | grep -A 20 "DROP TABLE"

# 2. 方案 A：按位置恢復（精確到字節）
# 假設 DROP TABLE 發生在 position 12345
mysqlbinlog --start-position=0 --stop-position=12345 \
  /var/lib/mysql/mysql-bin.000007 | mysql -u root -p recover_demo

# 3. 方案 B：按時間恢復（更直觀）
mysqlbinlog --start-datetime="2024-01-15 10:00:00" \
  --stop-datetime="2024-01-15 10:05:00" \
  /var/lib/mysql/mysql-bin.000007 | mysql -u root -p recover_demo

# 4. 方案 C：指定數據庫恢復（過濾其他庫噪音）
mysqlbinlog --database=recover_demo \
  /var/lib/mysql/mysql-bin.000007 | mysql -u root -p

進階技巧：行級日志格式下的精準恢復

若 binlog_format=ROW，需加 --base64-output=DECODE-ROWS -v 解析二進制行數據：

mysqlbinlog --base64-output=DECODE-ROWS -v \
  --start-datetime="2024-01-15 10:00:00" \
  /var/lib/mysql/mysql-bin.000007 > /tmp/recover.sql

# 審核 SQL 後執行
mysql -u root -p recover_demo < /tmp/recover.sql

避坑指南

• 恢復前務必停止寫入或在從庫/備份庫操作
• 全量備份 + Binlog 增量 = 完整恢復鏈路，缺一不可
• 定期演練恢復流程，驗證備份有效性
• 重要操作前手動 FLUSH LOGS 切割日志，縮小恢復範圍