MySQL 利用 Binlog 進行資料恢復實戰指南

MySQL 利用 Binlog 進行資料恢復實戰指南

一、什麼是 Binlog？

Binlog（二進位日誌）記錄所有對資料發生或潛在發生更改的 SQL 語句，以二進位形式儲存於磁碟。預設位於資料目錄，檔名格式為 hostname-bin.xxxxxx，每次重啟 MySQL 會生成新檔案。

啟用 Binlog 配置

編輯 my.cnf：

[mysqld]
log_bin = mysql-bin
server_id = 1

重啟後驗證：

mysql> SHOW VARIABLES LIKE '%log_bin%';

二、Binlog 三種格式深度對比

無法被 Statement 格式複制的函數： LOAD_FILE()、UUID()、USER()、FOUND_ROWS()、SYSDATE()（除非啟用 --sysdate-is-now）。

三、線上實戰：硬體故障後的資料搶救

場景描述

DB 主機硬體故障宕機，重啟失敗導致資料損壞，mysqldump 備份也失敗。所幸之前已啟用 Binlog，可從 Binlog 提取資料。

恢復步驟

1. 將 Binlog 檔案傳輸至正常運行的機器。
2. 使用 mysqlbinlog 解析並導入：

/app/mysql/bin/mysqlbinlog bin-log.xxx | /app/mysql/bin/mysql

查詢特定時間區間的 SQL

Row 格式下，mysqlbinlog 輸出的 SQL 為 Base64 編碼，需加參數解碼：

/opt/mysql/bin/mysqlbinlog \
  --base64-output=decode-rows -v \
  --start-date='2014-09-16 14:00:00' \
  --stop-date='2014-09-16 14:20:00' \
  /opt/mysql/log/mysql-bin.000017 \
  > /opt/mysql_bak/mysqlbinlogsql_restore_2014091614.sql

四、關鍵參數說明

• --base64-output=decode-rows：解碼 Row 格式的 Base64 內容。
• -v：詳細模式，重構 SQL 語句。
• --start-date / --stop-date：按時間範圍過濾。
• --start-position / --stop-position：按位置點過濾（更精確）。
• --database：僅解析指定資料庫的事件。

五、實務建議

1. 生產環境強制啟用 Binlog，並設定合理的 expire_logs_days 避免磁碟撐爆。
2. 主從架構下，從庫同樣應開啟 Binlog（log_slave_updates=ON），便於級聯恢復。
3. 定期演練：每季進行一次基於 Binlog 的時間點恢復演練（PITR），驗證備份可用性。
4. 大事務拆分：避免超大事務導致 Binlog 膨脹、主從延遲。