備份工具選型矩陣

工具	層級	引擎支援	熱備份	跨版本	速度	適用場景
mysqldump	邏輯 (SQL)	全引擎	支援 (單一交易)	優	中	全量/增量、跨版本遷移、結構遷移、小型資料庫
mysqlhotcopy	實體 (檔案)	僅 MyISAM	準熱備 (鎖表)	差	極快	大型 MyISAM 庫、同機器快速備份
SQL 語法 (BACKUP/SELECT INTO OUTFILE)	邏輯/實體混合	MyISAM 為主	部分需鎖表	差	快	特定表導出、文字報表
二進位日誌 (Binlog)	增量 (事件)	全引擎	完全熱備	版本相關	極快	時間點恢復 (PITR)、主從同步、增量備份
實體檔案複製 (cp/rsync/xtrabackup)	實體	全引擎	需工具支援	差	極快	大型 InnoDB 庫、PXC 節點佈署、雲端快照

mysqldump 核心參數精講

字元集與相容性

--default-character-set=utf8mb4          # 必須指定，防止亂碼
--compatible=mysql40|postgresql|oracle   # 跨資料庫遷移時使用

鎖定與一致性

• --single-transaction：InnoDB 專用，啟動 REPEATABLE READ 唯讀交易，不鎖表，保證一致性視圖。**生產熱備首選**。
• --lock-all-tables (-x)：全域讀鎖，阻塞所有寫入，適用 MyISAM 混合庫。
• --lock-tables：逐庫鎖表，MyISAM 適用，InnoDB 不推薦。

效能與輸出優化

• --quick (-q)：串流輸出，不緩存結果集，**大表必加**。
• --extended-insert：多行合併 INSERT，預設開啟，大幅縮小檔案、加快還原。
• --disable-keys：匯入時暫停非唯一索引更新，僅 MyISAM 有效。
• --hex-blob：二進位欄位 (BLOB/BINARY) 以十六進位輸出，避免編碼損壞。
• --routines -R、--triggers：包含預存程序/觸發器。
• --no-data (-d)、--no-create-info (-t)：僅結構/僅資料。

生產環境標準備份命令範本

# InnoDB 熱備 (推薦)
mysqldump -u backup_user -p'StrongPass' \
  --host=localhost --port=3306 \
  --default-character-set=utf8mb4 \
  --single-transaction --quick --routines --triggers --hex-blob \
  --master-data=2 \                  # 記錄 Binlog 位置，支援 PITR
  --databases db1 db2 > /backup/$(date +%F)_full.sql

# MyISAM 混合庫
mysqldump -u backup_user -p'StrongPass' \
  --lock-all-tables --flush-logs \
  --default-character-set=utf8mb4 --quick --routines --triggers \
  --databases db1 db2 > /backup/$(date +%F)_full.sql

mysqlhotcopy 極速備份 (僅 MyISAM、類 Unix)

# 單庫
mysqlhotcopy -u backup_user -p'Pass' -h localhost db_name /backup/

# 多庫 + 正則
mysqlhotcopy -u backup_user -p'Pass' -h localhost db_prefix_.* /backup/

# 恢復：直接複製回目錄並修正權限
cp -rf /backup/db_name /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/db_name

前置條件：備份帳號需 SELECT、RELOAD、LOCK TABLES 權限，且 OS 層面可讀取 datadir。

SQL 語法備份與還原

SELECT INTO OUTFILE (導出文字檔)

SELECT * FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n';

※ 需 FILE 權限，檔案寫入伺服器端，不可覆蓋既有檔案。

LOAD DATA INFILE (導入文字檔)

LOAD DATA INFILE '/tmp/orders.csv'
INTO TABLE orders
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

BACKUP TABLE / RESTORE TABLE (僅 MyISAM、已棄用)

MySQL 5.1+ 標記棄用，請改用 mysqldump 或實體備份。

二進位日誌增量備份與 PITR

1. 啟用 log_bin、expire_logs_days=7、binlog_format=ROW。
2. 全量備份後記錄 Binlog 位置：SHOW MASTER STATUS;
3. 增量備份：mysqlbinlog --start-position=XXX --stop-position=YYY mysql-bin.000001 > inc.sql
4. 恢復流程：全量還原 → mysqlbinlog 重放增量 → 時間點恢復完成。

實體備份神器：Percona XtraBackup

• 開源、免費、支援 InnoDB 熱備份、增量備份、壓縮、加密、平行流、部分備份。
• 不鎖表 (僅短暫鎖定系統表)，對生產影響極小。
• 恢復需 --prepare 套用重做日誌，再 --copy-back。

# 全量備份
xtrabackup --backup --target-dir=/backup/full --user=backup_user --password='Pass'

# 增量備份
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full

# 恢復準備
xtrabackup --prepare --apply-log-only --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/inc1 --incremental-basedir=/backup/full

# 還原
xtrabackup --copy-back --target-dir=/backup/inc1
chown -R mysql:mysql /var/lib/mysql

備份驗證與演練鐵律

1. 定期隨機抽樣恢復演練：每季至少一次全量恢復至測試環境，驗證資料完整性。
2. CHECKSUM TABLE 對比源端與目標端一致性。
3. 監控備份作業狀態、檔案大小、耗時，異常即時告警。
4. 異地備份：Binlog/備份檔同步至異地儲存 (S3、NFS、異地機房)。