針對 MySQL 5.0.22，系統比較 5 大免費備份路徑，深入 mysqldump 參數矩陣、mysqlhotcopy 限制、SQL 語法備份缺陷、binlog 增量與 PITR、物理拷貝一致性、InnoDB 崩潰恢復機制，並給出生產級備份策略與災難恢復 SOP。
一、5 大備份路徑對比
| 工具/方法 | 類型 | 優點 | 缺點/限制 | 適用引擎 |
|---|
| mysqldump | 邏輯 | 跨版本/架構、可壓縮、可篩選、支援 --single-transaction 熱備 | 大庫慢、鎖表影響寫入 (MyISAM) | 全引擎 |
| mysqlhotcopy | 物理 | 極快 (FLUSH+LOCK+cp/scp)、支援正則多庫 | 僅 MyISAM、僅 Unix/NetWare、需本機執行、需 SELECT/RELOAD/檔案讀取權限 | MyISAM |
| BACKUP TABLE / SELECT INTO OUTFILE | SQL語法 | 線上、可自定義格式 | BACKUP 不備份索引 (恢復慢)、SELECT INTO 需 FILE 權限、檔案不可覆蓋 | MyISAM |
| 二進制日誌 | 增量 | 增量備份、PITR、從庫分攤壓力、mysqlbinlog 可篩選庫/時間 | 需啟用 binlog、恢復需全量基礎、DDL 可能中斷 | 全引擎 |
| 直接拷貝檔案 | 物理 | 最快、最簡單 | 需 FLUSH TABLES WITH READ LOCK 停寫、InnoDB 需含 ib_logfile*、跨架構風險 | 全引擎 (MyISAM 跨平台安全) |
二、mysqldump 核心參數矩陣
| 參數 | 作用 | 建議場景 |
|---|
| --opt | 預設開啟，綜合優化組合 | 通用 |
| --single-transaction | InnoDB 一致性熱備 (無鎖) | InnoDB 生產庫必加 |
| --master-data=1 | 輸出 CHANGE MASTER TO 含 binlog 位置 | 建立從庫、PITR 基準 |
| --hex-blob | 二進制欄位十六進制輸出 | 含 BLOB/BINARY 欄位必加 |
| --routines / --triggers | 包含儲存過程/觸發器 | 完整遷移必加 |
| --extended-insert=false | 關閉多值 INSERT (便於閱讀/修改) | 調試/手工修改 SQL 時 |
| --quick | 串流輸出不緩存結果集 | 大表必加 |
| --compress | 網路傳輸壓縮 | 跨主機備份 |
MyISAM 完整備份
mysqldump -uuser -ppass --default-character-set=utf8 \
--opt --extended-insert=false --triggers -R --hex-blob -x dbname > dbname.sql
InnoDB 熱備
mysqldump -uuser -ppass --default-character-set=utf8 \
--opt --extended-insert=false --triggers -R --hex-blob \
--single-transaction dbname > dbname.sql
三、mysqlhotcopy 實操
# 單庫
mysqlhotcopy -h localhost -u user -p pass dbname /backup
# 多庫
mysqlhotcopy -h localhost -u user -p pass db1 db2 db3 /backup
# 正則
mysqlhotcopy -h localhost -u user -p pass dbname./prefix_/ /backup
# 還原
cp -rf /backup/dbname /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/dbname
四、binlog 增量備份與 PITR
- 啟用：
server-id=1; log-bin=binlog; log-bin-index=binlog.index
- 切換：
FLUSH LOGS; 產生新 binlog，舊檔即為增量
- 檢視：
mysqlbinlog --database=dbname --start-datetime=... --stop-datetime=... binlog.xxx
- 恢復：
mysqlbinlog binlog.xxx | mysql -uuser -ppass dbname
- 從庫備份：同時備份
master.info、relay-log.info
五、InnoDB 災難恢復機制
- Fuzzy Checkpoint：定期將緩衝池髒頁刷盤，記錄 LSN 於 ib_logfile*
- 啟動時自動 Redo：從 Checkpoint LSN 重放 redo log
- 強制恢復模式：
innodb_force_recovery=1..6 (1=SRV_FORCE_IGNORE_CORRUPT ... 6=SRV_FORCE_NO_LOG_REDO)
- 配置
innodb_force_recovery=4 可讓損壞實例啟動導出資料
六、資料維護指令
| 引擎 | 指令 | 作用 |
|---|
| MyISAM | CHECK TABLE / REPAIR TABLE | 檢查/修復損壞 |
| MyISAM | OPTIMIZE TABLE | 碎片整理、空間回收 |
| MyISAM | ANALYZE TABLE | 更新統計資訊 |
| InnoDB | ALTER TABLE tbl ENGINE=InnoDB; | Null 操作實則重建表，整理碎片 |
七、生產級備份策略
- 中等業務：首次全量 → 每日增量 (binlog) → 每週全量
- 核心業務：每日全量 (從庫) → 每小時增量 → 異地備份
- 架構：主從複制，從庫執行備份 (零影響主庫)
- 驗證：每季演練一次完整還原至測試環境
八、災難恢復 SOP
- 確認範圍：單表/單庫/全例項/存儲故障
- 嘗試修復：MyISAM → REPAIR/myisamchk；InnoDB → innodb_force_recovery
- 選擇基準：最近可用全量備份
- 重放增量：按序應用 binlog 至故障前時間點
- 驗證：CHECKSUM TABLE、關鍵業務對帳、應用煙霧測試
- 切換：DNS/Proxy 切流量、監控錯誤率
- 複盤：根因分析、補丁修正、策略優化
備份價值在於可恢復；未演練的備份只是心理安慰。
