MySQL 官方參考手冊第 7 章精讀：備份與恢復完整策略指南

MySQL 官方參考手冊第 7 章精讀：備份與恢復完整策略指南

本文基於 MySQL Reference Manual Chapter 7，系統梳理備份類型分類、四大備份方法、策略制定、mysqldump 進階用法、時間點恢復 (PITR)、MyISAM 維護與自動化建議，構建企業級備份知識體系。

7.1 備份與還原類型分類學

物理備份 vs 邏輯備份

其他分類維度

• 線上 (熱備) vs 離線 (冷備)：熱備不中斷服務，需加鎖保證一致性；冷備停機最簡單
• 本地 vs 遠程：mysqldump 可遠程導出本地存；mysqlhotcopy 僅本地
• 快照備份：LVM/Veritas/VxFS copy-on-write，近瞬間凍結一致性視圖
• 全備 vs 增量：增量依賴 Binary Log (FLUSH LOGS 切換)
• 完全恢復 vs 時間點恢復 (PITR)：全備 + binlog 重放至指定時刻

7.2 四大備份方法詳解

1. MySQL Enterprise Backup (商業版)：熱備 InnoDB、暖備其他引擎、支援壓縮/加密/增量
2. mysqldump + mysqlhotcopy：

   # 邏輯備份標準範例
   mysqldump --single-transaction --flush-logs --master-data=2 --all-databases > full_backup.sql
   # --single-transaction: InnoDB 一致性快照
   # --flush-logs: 切換 binlog，標記增量起點
   # --master-data=2: 註釋記錄 binlog 位置 (CHANGE MASTER TO...)

   mysqlhotcopy：Perl 腳本，FLUSH TABLES + LOCK TABLES + cp/scp，僅支援 MyISAM/ARCHIVE

3. 複製表檔案：FLUSH TABLES tbl_list WITH READ LOCK 後複製 .frm/.MYD/.MYI。InnoDB 不適用 (資料可能在記憶體/共享表空間)
4. 文字檔備份：SELECT * INTO OUTFILE 或 mysqldump --tab

7.3 企業級備份策略範例

# 週日 13:00 全備 (InnoDB 熱備)
mysqldump --single-transaction --flush-logs --master-data=2 --all-databases > backup_sun_1pm.sql

# 週一 13:00 增量備份 (切換 binlog)
mysqladmin flush-logs  # 產生 bin.000007
cp bin.000007 /backup/incr_mon_1pm.bin

# 週二 13:00 增量備份
mysqladmin flush-logs  # 產生 bin.000008
cp bin.000008 /backup/incr_tue_1pm.bin

# 週三 08:00 崩潰恢復流程
# 1. 還原全備
mysql < backup_sun_1pm.sql
# 2. 重放增量 binlog
mysqlbinlog bin.000007 bin.000008 | mysql
# 3. 若有最新 binlog (bin.000009) 繼續重放
mysqlbinlog bin.000009 | mysql

7.4 mysqldump 進階技巧

7.5 時間點恢復 (PITR) 兩種定位方式

# 1. 基於時間
mysqlbinlog --stop-datetime="2024-01-15 10:00:00" bin.000001 | mysql

# 2. 基於 Event 位置 (更精確)
mysqlbinlog --stop-position=368312 bin.000001 | mysql
mysqlbinlog --start-position=368315 bin.000001 | mysql

7.6 MyISAM 表維護與崩潰恢復

• myisamchk：離線檢查/修復/優化 (-r 修復、-q 快速、--safe-recover 安全模式)
• SQL 介面：CHECK/REPAIR/OPTIMIZE/ANALYZE TABLE
• 四階段修復流程：檢查 → 簡單修復 (-r -q) → 困難修復 (重建索引) → 極困難 (需 .frm 備份)
• 定期維護排程：35 0 * * 0 myisamchk --fast --silent /datadir/*/*.MYI

關鍵最佳實踐總結

1. 啟用 Binary Log (log-bin) —— PITR 前提
2. 定期全備 (週/日) + 固定間隔增量 (日/時)
3. InnoDB 必用 --single-transaction 熱備
4. 備份驗證：CHECKSUM TABLE、抽樣查詢、定期演練恢復
5. 異地備份：遠程複製或雲端儲存
6. 監控備份成功/失敗、耗時、檔案大小

建立標準化、自動化、可驗證的備份體系，是資料資產安全的最後防線。