Binlog 是 MySQL 複製、時間點恢復（PITR）、審計追蹤的基石。本文從定義、配置、工具、清理策略到不一致處理，構建完整知識體系。

核心定義與作用

• 記錄內容：所有修改資料的 DML（INSERT/UPDATE/DELETE）、DDL（CREATE/ALTER/DROP），不含 SELECT
• 潛在修改：即使 DELETE WHERE 1=0 無匹配行也會記錄
• 核心用途：
  1. 時間點恢復：全備 + Binlog 重放 = 任意時刻恢復
  2. 主從複製：主庫 Binlog → 從庫 Relay Log → SQL Thread 重放
  3. 審計追蹤：誰在何時做了什麼修改
• 效能影響：啟用約 1% 效能損耗，換取完整可恢復性

啟用與配置詳解

[mysqld]
log-bin = /var/lib/mysql/binlog/mysql-bin    # 指定基礎路徑與前綴
binlog_format = ROW                          # 格式：ROW/STATEMENT/MIXED
max_binlog_size = 1G                         # 單檔最大值，達到則輪轉
expire_logs_days = 7                         # 自動清理天數（MySQL 8.0 用 binlog_expire_logs_seconds）
sync_binlog = 1                              # 每次提交 fsync，零資料遺失

檔案命名規則

• 基礎名：mysql-bin → 實際檔案：mysql-bin.000001, mysql-bin.000002...
• 索引檔：mysql-bin.index 記錄所有 Binlog 檔案列表
• 擴展名會被忽略：log-bin=bin.log 實際生成 bin.000001

輪轉觸發條件（四大機制）

1. 服務重啟（FLUSH LOGS 隱性觸發）
2. 執行 FLUSH LOGS 指令
3. 檔案達到 max_binlog_size
4. 執行 mysqladmin flush-logs

核心工具：mysqlbinlog 解碼利器

# 基本用法
mysqlbinlog [options] binlog_file > output.sql

# 實用參數
--start-datetime="2023-01-01 00:00:00"  # 起始時間
--stop-datetime="2023-01-02 00:00:00"   # 結束時間
--start-position=12345                   # 起始位點
--stop-position=67890                    # 結束位點
--database=testdb                        # 只解析指定庫
--verbose                                # 顯示 ROW 格式為伪 SQL
--base64-output=DECODE-ROWS              # 解碼 ROW 格式二進位資料

Binlog 清理策略（三種方式）

1. PURGE BINARY LOGS（精準控制）

-- 刪除指定檔案之前的所有日誌（指定檔案保留）
PURGE BINARY LOGS TO 'mysql-bin.000004';

-- 刪除指定時間之前的所有日誌
PURGE BINARY LOGS BEFORE '2023-09-22 00:00:00';

⚠️ 若從庫正在讀取目標日誌，執行失敗報錯；從庫離線時清理會導致從庫無法續傳。

2. RESET MASTER（核彈級重置）

RESET MASTER;

刪除所有 Binlog，重置索引檔，新檔從 000001 開始。嚴禁生產主庫執行！

3. 自動過期（推薦生產配置）

[mysqld]
binlog_expire_logs_seconds = 604800  -- 7 天，MySQL 8.0+
# 或舊版
expire_logs_days = 7

Binlog 格式深度對比

格式	記錄內容	優點	缺點	適用場景
STATEMENT	原始 SQL	體積小、易讀	非確定性函數(UID/NOW)導致主從不一致	簡單業務、舊版相容
ROW	行級變更（前/後映像）	絕對一致性、支援所有場景	體積大、不可直接閱讀	生產環境標配（預設 8.0+）
MIXED	自動切換	平衡體積與安全	行為不可預測、調試困難	過渡期使用

Binlog 不準確/損壞處理流程

1. 檢測：主庫啟動時比對 InnoDB 事務 ID 與 Binlog 位點
2. 報錯：The binary log ... is shorter than expected
3. 根因：sync_binlog!=1 + 崩潰/斷電，或磁碟故障導致寫入丟失
4. 處置：必須從主庫快照重建從庫，不可嘗試修補 Binlog

進階配置：過濾與多租戶

-- 僅記錄特定庫
binlog-do-db = sales

-- 排除特定庫（其餘都記錄）
binlog-ignore-db = test

⚠️ 陷阱：未執行 USE dbname 時，跨庫操作不受過濾控制
-- 建議：應用層強制 USE dbname，或改用 replicate-do-table 從庫端過濾

Binlog 是 MySQL 的「黑盒飛行記錄儀」，配置得當則是災難恢復的救生艇，配置失當則是效能與一致性的隱形殺手。深度掌握其運作機制，是高級 DBA 的分水嶺。