MySQL 增量備份與恢復實戰：基於 Binlog 的精確時間點恢復 (PITR)

MySQL 增量備份與恢復實戰：基於 Binlog 的精確時間點恢復 (PITR)

核心原理

增量備份 = 完全備份 + Binlog 日誌。Binlog 記錄所有 DDL/DML 變更，重放 Binlog 即可恢復到任意時間點。

前置條件

# my.cnf 必須啟用
[mysqld]
log_bin = mysql-bin
server_id = 1
binlog_format = ROW  # 推薦 ROW 格式

實戰步驟演示

1. 完全備份（記錄 Binlog 位置）

# 插入正常業務數據
INSERT INTO pak.t_user ...;

# 刷新日誌，產生新 Binlog 文件
FLUSH LOGS;  -- 此時 mysql-bin.000002 封存完全備份後至今的變更

# 再插入兩條
INSERT INTO pak.t_user ...;

# 誤操作：刪除表
DROP TABLE pak.t_user;  -- 記錄在 mysql-bin.000003

3. 恢復流程

mysql -u root -p < pak_full_2024-01-15.sql

Step 2：恢復中間完整 Binlog (mysql-bin.000002)

# 定位誤操作位置
mysqlbinlog /var/lib/mysql/mysql-bin.000003 | grep -B5 -A5 'DROP TABLE'

# 輸出示例：
# #141204 15:07:05 server id 1 end_log_pos 775 Xid = 376 COMMIT
# # at 775
# #141204 15:08:04 server id 1 end_log_pos 882 Query ... DROP TABLE `t_user`
# # at 882

# 方案 A：按 Position 停止（推薦，精確）
mysqlbinlog /var/lib/mysql/mysql-bin.000003 --stop-position=775 | mysql -u root -p

# 方案 B：按時間停止
mysqlbinlog /var/lib/mysql/mysql-bin.000003 --stop-datetime="2014-12-04 15:08:00" | mysql -u root -p

Step 4：驗證並重新開啟 Binlog

參數用途
--start-position從指定 Position 開始重放
--stop-position在指定 Position 停止（不含）
--start-datetime從指定時間開始
--stop-datetime在指定時間停止
--database僅重放指定庫（ROW 格式需配合 --verbose）
--verboseROW 格式顯示為可讀 SQL


常見坑點：mysqlbinlog 讀取 my.cnf 報錯
/usr/local/mysql/bin/mysqlbinlog: unknown variable 'default-character-set=utf8'
原因： mysqlbinlog 讀取 [client] 區段的 default-character-set 但不識別。
解決：

註釋 [client] 下的該行
或改用 loose-default-character-set = utf8（未知選項會警告但繼續）


生產環境建議

完全備份：每日/每周（視數據量）
Binlog 保留：expire_logs_days = 7 或更長
異地備份：Binlog 實時同步至備份機房
定期演練：每季度進行一次 PITR 恢復演練，驗證備份可用性