MySQL Binlog 二進制日志深度解析與運維實戰

Binlog 核心概念

• 定義：記錄所有修改數據或潛在修改數據的 SQL 語句（如無匹配行的 DELETE）
• 核心用途：
  1. 時間點恢復：全備 + Binlog = 任意時刻恢復
  2. 主從復制：主庫 Binlog 發送給從庫重放
• 性能影響：開啟約降低 1% 性能，可接受

啟用與配置

[mysqld]
log-bin = /data/mysql/binlog/mysql-bin  # 建議獨立磁盤
max_binlog_size = 500M                  # 單文件上限，超過自動切換
expire_logs_days = 7                    # 自動清理天數
sync_binlog = 1                         # 每次提交強制刷盤，最安全但最慢

文件命名規則：mysql-bin.000001、mysql-bin.000002... 伴隨 mysql-bin.index 索引文件。

切換觸發條件

1. 服務重啟
2. FLUSH LOGS 手動切換
3. 文件達到 max_binlog_size
4. 執行 mysqladmin flush-logs

核心運維操作

1. Binlog 解析查看

# 查看指定文件
mysqlbinlog --base64-output=decode-rows -v /data/mysql/binlog/mysql-bin.000001

# 導出 SQL 文件
mysqlbinlog /data/mysql/binlog/mysql-bin.000001 > /tmp/binlog.sql

# 指定時間範圍導出
mysqlbinlog --start-datetime="2024-01-15 10:00:00" --stop-datetime="2024-01-15 12:00:00" \
  /data/mysql/binlog/mysql-bin.000001 > /tmp/incr.sql

2. 安全清理舊 Binlog

-- 按文件名清理（保留指定文件及之後）
PURGE BINARY LOGS TO 'mysql-bin.000010';

-- 按時間清理（保留指定時間之後）
PURGE BINARY LOGS BEFORE '2024-01-15 00:00:00';

-- 核彈級：重置所有 Binlog（慎用！主從環境會中斷復制）
RESET MASTER;

黃金法則：清理前確認所有從庫已同步至目標位置（SHOW SLAVE STATUS\G 查看 Relay_Master_Log_File）。

3. 部分庫記錄控制

binlog-do-db = business_db      -- 僅記錄此庫
binlog-ignore-db = test,sys     -- 排除此庫
# 注意：需在 SQL 前執行 USE db_name，否則不生效

數據一致性保障

InnoDB + sync_binlog=1 + innodb_flush_log_at_trx_commit=1 = 雙 1 模式，最高可靠性。

故障場景應對

• Binlog 損壞/缺失：報錯 The binary log ... is shorter than expected，需從主庫全量重建從庫
• 磁盤空間不足：緊急 PURGE BINARY LOGS BEFORE ... 或擴容磁盤
• 主從延遲大：檢查 Seconds_Behind_Master，優化從庫 SQL 線程、啟用並行復制

掌握 Binlog 全生命周期管理，是 MySQL 高可用架構的基石。