萬盛學電腦網

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

MySQL 二進制日誌 (binlog) 時間點恢復 (PITR) 完整操作指南

前置條件

  • 啟用 log_bin(my.cnf 設定 log-bin=mysql-bin)。
  • 備份基準點:全量備份(mysqldump --single-transaction --master-data=2)。

確認當前 binlog 檔案

SHOW BINLOG EVENTS\G
-- 或命令列
mysql -uroot -p -e "SHOW BINLOG EVENTS\G"

方式一:指定時間區間恢復

假設 10:00 誤刪大表,備份為昨晚 23:00。

# 1. 還原全量備份
mysql -uroot -p < full_backup_2300.sql

# 2. 重放 23:00 到 09:59:59 的 binlog
mysqlbinlog --stop-datetime="2023-10-01 09:59:59" /var/log/mysql/mysql-bin.000123 | mysql -uroot -p

# 3. 跳過錯誤陳述式,重放 10:01 之後
mysqlbinlog --start-datetime="2023-10-01 10:01:00" /var/log/mysql/mysql-bin.000123 | mysql -uroot -p

方式二:指定位置恢復(更精準)

  1. 導出可疑時段 binlog 至文字檔分析:
    mysqlbinlog --start-datetime="2023-10-01 09:55:00" --stop-datetime="2023-10-01 10:05:00" \
      /var/log/mysql/mysql-bin.000123 > /tmp/review.sql
    
  2. 用編輯器找到誤刪語句(如 DROP TABLE),記下其 log_pos(如 368312)。
  3. 恢復至誤刪前:
    mysqlbinlog --stop-position=368312 /var/log/mysql/mysql-bin.000123 | mysql -uroot -p
    
  4. 恢復誤刪後:
    mysqlbinlog --start-position=368315 /var/log/mysql/mysql-bin.000123 | mysql -uroot -p
    

關鍵提醒

  • mysqlbinlog 輸出含 SET TIMESTAMP,恢復資料保留原執行時間。
  • 大量 binlog 建議先 gunzip 解壓再管道導入。
  • 生產環境務必在測試庫驗證恢復腳本後再上線操作。
copyright © 萬盛學電腦網 all rights reserved