MySQL 備份恢復實戰 (五)：二進制日誌時間點恢復保證資料一致性

MySQL 備份系列完結篇：完全備份 + 二進制日誌實現時間點恢復 (PITR)。實戰模擬凌晨備份 → 業務寫入 → 故障 → 完全備份恢復 → Binlog 精確時間點前滾恢復全流程。

一、核心架構圖解

完全備份 (基準點) + Binlog (增量變更) = 任意時間點恢復能力

二、實戰演練完整記錄

環境驗證

mysql> USE larrydb;
mysql> SELECT * FROM class;  -- 2 rows
mysql> SELECT * FROM stu;    -- 2 rows

步驟 1：完全備份 (基準點)

mysqldump -uroot -p123456 --databases larrydb > larrydb.sql

步驟 2：清空 Binlog (新基準點)

mysql> RESET MASTER;  -- 僅保留 mysql-bin.000001

步驟 3：模擬業務寫入 (增量變更)

INSERT INTO class VALUES (3, 'Devel');
UPDATE class SET cname='dab' WHERE cid=2;
DELETE FROM stu WHERE cid=2;
UPDATE stu SET sname='larry007' WHERE sid=1;
-- 時間: 19:37:45 提交

步驟 4：模擬災難 - 刪除資料庫

rm -rf /usr/local/mysql/data/larrydb/
mysql> DROP DATABASE larrydb;

步驟 5：恢復完全備份

mysql -uroot -p123456 < larrydb.sql
-- 驗證: 恢復至備份時刻狀態 (2 rows each)

步驟 6：Binlog 時間點前滾恢復

# 方案 A: 指定停止時間 (精確到秒)
mysqlbinlog --stop-datetime "2013-09-10 19:37:45" mysql-bin.000001 | mysql -uroot -p123456
-- 結果: 恢復至 19:37:45 前 (stu=1 row, class=3 rows)

mysqlbinlog --stop-datetime "2013-09-10 19:37:46" mysql-bin.000001 | mysql -uroot -p123456
-- 結果: 包含 19:37:45 提交事務 (stu=larry007, class=3 rows)

# 方案 B: 指定位置點 (更精確)
mysqlbinlog --stop-position=7240 mysql-bin.000001 | mysql -uroot -p123456

關鍵規律

恢復時間點 = 故障發生時間 + 1 秒 (確保包含故障前最後提交的事務)

三、生產環境 PITR 標準流程

1. 定期完全備份 (每日/每週) + 即時 Binlog 異地同步
2. 故障發生 → 停止寫入 → 記錄故障時間
3. 測試環境恢復完全備份
4. 應用 Binlog 至故障時間 + 1 秒
5. 驗證資料完整性 → 切換生產流量
6. ⚠️ 嚴禁在生產庫直接操作，必須在測試庫驗證後再遷移

四、Binlog 管理最佳實踐

[mysqld]
log-bin = mysql-bin
expire_logs_days = 7
max_binlog_size = 512M
sync_binlog = 1              # 金融業務建議
binlog_format = ROW          # 最安全格式