MySQL 備份與恢復完整指南：mysqldump、二進制日誌、單表恢復、時間點恢復 (PITR)

備份策略分層

mysqldump 關鍵參數

mysqldump -u root -p \
  --single-transaction \      # InnoDB 一致性快照，無鎖
  --routines --triggers --events \
  --master-data=2 \           # 輸出 CHANGE MASTER TO 註解 (binlog 位置)
  --hex-blob \                # 二進制欄位十六進制輸出
  --quick \                   # 逐行輸出，不緩衝全表
  --compress \                # 壓縮傳輸
  --databases db1 db2 > backup_$(date +%F).sql

恢復場景與操作

1. 全量邏輯恢復

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

若為 GTID 複寫環境，導入前需 SET @@GLOBAL.gtid_purged='...' 或使用 --set-gtid-purged=OFF。

2. 時間點恢復 (PITR)

1. 找到全量備份對應的 binlog 位置 (mysqldump --master-data=2 註解中)。
2. 重放 binlog 至目標時間：

   mysqlbinlog --start-position=12345 --stop-datetime='2024-01-15 14:30:00' \
     mysql-bin.000123 | mysql -u root -p

3. 單表恢復

從全量備份中提取單表：

# 方法一：sed/awk 提取 (適合小表)
sed -n '/^CREATE TABLE `target_table`/,/^CREATE TABLE `/p' backup.sql > table.sql

# 方法二：載入到臨時實例再 mysqldump 單表 (推薦大表)
# 1. 啟動臨時實例 datadir=/tmp/recovery
# 2. 載入全量備份
# 3. mysqldump --single-transaction temp_db target_table > target_table.sql
# 4. 導入生產庫

4. 物理備份恢復

# xtrabackup 還原
xtrabackup --prepare --target-dir=/backup/full
systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

單表誤刪/誤更新緊急恢復

1. 立即 FLUSH LOGS 產生新 binlog，避免後續寫入干擾。
2. 用 mysqlbinlog 定位誤操作前的位置。
3. PITR 至誤操作前一秒，或提取單表重放。
4. 若開啟 binlog_row_image=FULL，可用 binlog2sql 反解生成回滾 SQL。

備份驗證與演練

• 每月至少一次完整恢復演練，記錄 RTO、驗證資料完整性 (CHECKSUM TABLE、pt-table-checksum)。
• 備份檔案校驗和 (SHA256) 存異地，防範勒索軟體。
• 監控備份任務成功/失敗、檔案大小異常變化。