前言

Binlog (Binary Log) 記錄所有數據變更事件，是 MySQL 主從同步、時間點恢復 (PITR)、審計追蹤的核心基礎設施。本文涵蓋 Binlog 開啟配置、常用查看命令、完整 PITR 恢復演練流程與安全清理策略。

一、開啟 Binlog 配置

編輯 /etc/my.cnf [mysqld]

log-bin = mysql-bin          # 啟用 Binlog，指定基礎文件名
binlog_format = ROW          # 推薦 ROW 格式，避免 STATEMENT 不一致
server-id = 1                # 主庫唯一 ID
expire_logs_days = 7         # 自動清理 7 天前 Binlog
max_binlog_size = 100M       # 單文件最大體積，防止過大
sync_binlog = 1              # 每次提交刷盤，最高持久化（性能折衷可設 100-1000）
binlog_rows_query_log_events = ON  # 記錄原始 SQL（5.6+），便於審計

重啟生效：systemctl restart mysqld。

二、Binlog 常用管理命令

命令	作用
SHOW BINARY LOGS;	列出所有 Binlog 文件及大小
SHOW MASTER STATUS;	顯示當前寫入文件及 Position
SHOW BINLOG EVENTS [IN 'mysql-bin.000001'] [FROM pos] [LIMIT n];	解析 Binlog 事件（慎用於大文件）
FLUSH LOGS;	切換新 Binlog 文件（舊文件封存）
RESET MASTER;	**危險**：刪除所有 Binlog，重置索引文件，主從將中斷
PURGE BINARY LOGS TO 'mysql-bin.000010';	安全刪除指定文件之前的所有 Binlog（需確認從庫已同步）
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';	按時間點安全清理

三、mysqlbinlog 命令行工具詳解

基本語法

mysqlbinlog [options] log_file1 log_file2 ... > output.sql

核心選項

選項	說明
--database=db_name	僅解析指定庫的事件（需 ROW 格式配合 --verbose）
--start-datetime / --stop-datetime	按時間範圍過濾
--start-position / --stop-position	按位置範圍過濾
--verbose / -v	ROW 格式下重構 SQL（BINLOG_EVENT 查看）
--base64-output=DECODE-ROWS	解碼 ROW 事件為可讀 SQL
--no-defaults	忽略配置文件，避免連接參數干擾
--result-file=	輸出到文件而非 stdout

四、完整 PITR (Point-In-Time Recovery) 實戰演練

場景

• 全量備份：每日 02:00 mysqldump --single-transaction --master-data=2 --all-databases > full_20240115.sql
• 誤操作時間：2024-01-15 09:30 误 DROP TABLE t1
• 目標：恢復到 09:29 狀態

步驟

1. 確認全量備份文件中的 Binlog 位置：

   head -30 full_20240115.sql
   -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=272;
   

2. 恢復全量備份：

   
   mysql -u root -p < full_20240115.sql
   

3. 從 Binlog 重放增量到誤操作前：

   
   # 1. 定位 Binlog 文件：mysql-bin.000006 (從備份文件獲取)
   # 2. 重放從 Position 272 到 09:30 前
   mysqlbinlog --no-defaults \
     --start-position=272 \
     --stop-datetime="2024-01-15 09:29:59" \
     /var/lib/mysql/mysql-bin.000006 \
     | mysql -u root -p
   

4. 驗證數據：SELECT COUNT(*) FROM t1; 應包含誤刪前的數據。

多 Binlog 文件重放

mysqlbinlog --no-defaults \
  --start-position=272 \
  --stop-datetime="2024-01-15 09:29:59" \
  /var/lib/mysql/mysql-bin.000006 \
  /var/lib/mysql/mysql-bin.000007 \
  | mysql -u root -p

**順序不可亂**，按文件編號遞增管道傳給 mysql 客戶端。

五、安全清理 Binlog 策略

• **主從環境**：執行 PURGE BINARY LOGS TO 'mysql-bin.XXXXX'; 前，務必在所有從庫執行 SHOW SLAVE STATUS\G 確認 Master_Log_File ≥ 目標文件。
• **自動清理**：設置 expire_logs_days=7（或 5.6+ binlog_expire_logs_seconds），MySQL 定期自動清理。
• **備份前切換**：FLUSH LOGS; 生成新文件，備份舊文件至異地存儲，再 PURGE。
• **絕不手動 rm 文件**：會導致 mysql-bin.index 與實際文件不一致，引發啟動失敗或主從報錯。

六、防誤刪除參數 -U (--safe-updates) 回顧

啟動 MySQL 客戶端加 -U 或配置 [mysql] safe-updates，強制 DELETE/UPDATE 必須帶 WHERE 且使用索引，防止全表誤刪。僅影響交互式客戶端。

總結

Binlog 是 MySQL 數據安全的「時光機」。**標準化配置 + 定期全量備份 + Binlog 異地歸檔 + 定期 PITR 演練**，構建 RPO=秒級、RTO=分鐘級的恢復能力。運維鐵律：**永遠用 PURGE/expire_logs_days 清理，絕不手動刪文件；恢復時嚴格按順序重放多文件**。