啟用 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)。
適用於「知道大約錯誤發生時間」的情境。
# 恢復至 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
缺點:秒級精度可能不足,若同一秒內有大量交易,難以精準截斷。
位置號(log_pos)對應 binlog 事件的精確位移,精度更高,適合高併發場景。
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 數值。
# 恢復至錯誤發生前(假設停止位置 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=...,恢復後的資料與日誌會保留原始執行時間。
mysqldump --single-transaction --flush-logs --master-data=2(取得備份時刻的 binlog 位置)。mysql < backup.sql。mysqlbinlog 從備份位置恢復至事故前一秒。| 參數 | 說明 |
|---|---|
--start-date / --stop-date | 以 DATETIME 格式指定時間範圍 |
--start-position / --stop-position | 以 log_pos 指定精確位置(推薦) |
--database=db_name | 僅解析指定資料庫的事件 |
--verbose | 輸出可讀的 SQL(而非原始事件),方便人工審閱 |
ROW,mysqlbinlog -v 才能看到完整 SQL;STATEMENT 格式直接可讀。log_bin(SET SESSION sql_log_bin=0),避免產生新 binlog 造成無限迴圈。