萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 二進制日誌恢復實戰:基於時間點與位置的精準還原

利用 mysqlbinlog 從二進制日誌精準恢復資料

啟用 log-bin 後,MySQL 會記錄所有資料變更。配合 mysqlbinlog 工具,可實現「時間點恢復」(Point-in-Time Recovery, PITR),將資料庫還原到任意瞬間,是災難恢復的關鍵技能。

前置準備:確認二進制日誌檔案

-- 在 MySQL 客戶端查看
SHOW BINLOG EVENTS\G

-- 或命令列直接查詢
mysql -u root -p -e 'SHOW BINLOG EVENTS\G'

記錄當前 binlog 檔案路徑(通常在 datadir 下,檔名如 bin.123456)。

方法一:指定恢復時間範圍(--start-date / --stop-date)

適用於「知道大約錯誤發生時間」的情境。

# 恢復至 2005-04-20 09:59:59(刪除大表前一秒)
mysqlbinlog --stop-date="2005-04-20 09:59:59" /var/log/mysql/bin.123456 | mysql -u root -p

# 恢復 10:01 之後的操作(跳過錯誤時段)
mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456 | mysql -u root -p

缺點:秒級精度可能不足,若同一秒內有大量交易,難以精準截斷。

方法二:指定恢復位置(--start-position / --stop-position)── 推薦

位置號(log_pos)對應 binlog 事件的精確位移,精度更高,適合高併發場景。

步驟 1:匯出可疑時段的 SQL 供人工審閱

mysqlbinlog --start-date="2005-04-20 09:55:00" --stop-date="2005-04-20 10:05:00" \
    /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

用編輯器開啟 /tmp/mysql_restore.sql,搜尋破壞性語句(如 DROP TABLE、DELETE 無 WHERE),記下其前後的 log_pos 數值。

步驟 2:分段恢復

# 恢復至錯誤發生前(假設停止位置 368312)
mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 | mysql -u root -p

# 跳過錯誤語句,從 368315 繼續恢復至最新
mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 | mysql -u root -p

注意:mysqlbinlog 輸出包含 SET TIMESTAMP=...,恢復後的資料與日誌會保留原始執行時間。

完整災難恢復流程(SOP)

  1. 每日全量備份:mysqldump --single-transaction --flush-logs --master-data=2(取得備份時刻的 binlog 位置)。
  2. 發生事故:停止寫入、確認錯誤時間點。
  3. 還原全量備份:mysql < backup.sql。
  4. 增量恢復:用 mysqlbinlog 從備份位置恢復至事故前一秒。
  5. 驗證資料:抽樣比對關鍵表列數、檢查業務邏輯。
  6. 切換服務:確認無誤後開放寫入。

關鍵參數速查表

參數說明
--start-date / --stop-date以 DATETIME 格式指定時間範圍
--start-position / --stop-position以 log_pos 指定精確位置(推薦)
--database=db_name僅解析指定資料庫的事件
--verbose輸出可讀的 SQL(而非原始事件),方便人工審閱

避坑提醒

  • 恢復期間暫停業務寫入,避免新舊交易衝突。
  • 若 binlog 格式為 ROW,mysqlbinlog -v 才能看到完整 SQL;STATEMENT 格式直接可讀。
  • 大量恢復時建議先關閉 log_bin(SET SESSION sql_log_bin=0),避免產生新 binlog 造成無限迴圈。
copyright © 萬盛學電腦網 all rights reserved