MySQL Binlog 時間點恢復 (PITR) 完整實戰：基於位置與時間的精準恢復

MySQL Binlog 時間點恢復 (PITR) 完整實戰：基於位置與時間的精準恢復

結合**全量備份 + Binlog 重放**，可將資料庫恢復至任意秒。本文以模擬場景 (建庫→寫入→刷新 Binlog→全量備份→誤刪資料→刪庫) 完整演示基於 **Position** 與 **Datetime** 兩種恢復模式。

場景構建步驟 (關鍵節點記錄)

1. 建庫 ailanni、表 ilannitable，插入 1、2 → 刷新 Binlog → 生成 mysql-bin.000001 (Pos 107 後為 1、2)
2. 插入 3、4 → 刷新 Binlog → 生成 mysql-bin.000002 (Pos 304 後為 3、4)
3. 全量備份：mysqldump -uroot -p123456 ailanni > /root/ailanni.sql (此時資料 1,2,3,4)
4. 誤刪 DELETE FROM ilannitable WHERE id=3; → 刷新 Binlog → mysql-bin.000003 (記錄刪除 3)
5. 刪庫 DROP DATABASE ailanni; → 刷新 Binlog → mysql-bin.000004 (記錄 DROP)

各 Binlog 內容可用 mysqlbinlog 查看，關鍵位置標記：

恢復模式一：全量備份還原 (最基礎)

CREATE DATABASE ailanni;
mysql -uroot -p123456 ailanni < /root/ailanni.sql
-- 恢復至備份時刻 (資料 1,2,3,4)

恢復模式二：基於 Position 精準恢復 (推薦)

需求：恢復至**刪除 id=3 之前** (即資料 1,2,4)。
分析：mysql-bin.000003 中刪除 3 的 Position 為 304 (假設)，需重放 **000001 + 000002 + 000003 前 304 位置**。

-- 1. 先還原全量備份 (或建空庫 + 重放 000001+000002)
mysqlbinlog /var/lib/mysql/mysql-bin.000001 | mysql -uroot -p123456
mysqlbinlog /var/lib/mysql/mysql-bin.000002 | mysql -uroot -p123456

-- 2. 重放 000003 至 Pos 304 (不含刪除 3)
mysqlbinlog --stop-position=304 /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p123456

-- 結果：資料 1,2,4 (3 已刪除，4 保留)

恢復模式三：基於 Datetime 恢復

需求：恢復至 **2014-11-17 11:30:43** (刪除 3 之前)。

mysqlbinlog --stop-datetime='2014-11-17 11:30:43' /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p123456

同樣得到資料 1,2,4。

恢復模式四：恢復至特定交易中間 (如插入 3 後、插入 4 前)

需求：資料 1,2,3。
分析：mysql-bin.000002 中插入 3 結束 Position 為 304 (假設)，插入 4 從此開始。

mysqlbinlog /var/lib/mysql/mysql-bin.000001 | mysql -uroot -p123456
mysqlbinlog --stop-position=304 /var/lib/mysql/mysql-bin.000002 | mysql -uroot -p123456

關鍵參數對照表

生產環境 PITR 標準流程

1. 定期全量備份 (每日/每週) + 啟用 Binlog (expire_logs_days=7 以上)。
2. 故障發生：立即停止寫入** (或設 read_only=ON)，防止 Binlog 繼續增長。
3. 找到最近一次全量備份時間點對應的 Binlog 位置 (備份腳本應記錄 SHOW MASTER STATUS)。
4. 還原全量備份 → 重放 Binlog 至故障前 Position/時間。
5. 驗證資料完整性 → 切換應用流量。