mysqldump 備份與還原：從基礎到生產級實踐

系統整理 mysqldump 核心參數、備份策略、還原方法、壓縮傳輸、跨版本遷移等實戰技巧。

備份工具對比

工具	類型	適用引擎	特點
mysqldump	邏輯備份	所有	跨版本、可編輯、較慢
mysqlhotcopy	物理備份	MyISAM	快、已棄用
xtrabackup	物理熱備	InnoDB	不鎖表、速度快、增量備份
binlog	增量備份	所有	時間點恢復 (PITR)
文件拷貝	物理冷備	MyISAM	需停機、版本強綁定

核心備份命令詳解

基本備份

mysqldump -h host -u user -p db_name > backup.sql

帶 DROP TABLE 的備份 (覆蓋式還原)

mysqldump --add-drop-table -u user -p db_name > backup.sql

壓縮備份 (節省空間/傳輸)

mysqldump -h host -u user -p db_name | gzip > backup.sql.gz

備份指定表

mysqldump -h host -u user -p db_name table1 table2 > tables.sql

同時備份多庫

mysqldump -h host -u user -p --databases db1 db2 db3 > multi.sql

只備份結構 (無數據)

mysqldump --no-data --databases db1 db2 > structure.sql

備份所有資料庫

mysqldump --all-databases > all.sql

生產環境推薦參數組合

# MyISAM 表
mysqldump -u user -p \
  --default-character-set=utf8mb4 \
  --opt \
  --extended-insert=false \
  --triggers -R --hex-blob \
  -x db_name > backup.sql

# InnoDB 表 (熱備份，不鎖表)
mysqldump -u user -p \
  --default-character-set=utf8mb4 \
  --opt \
  --extended-insert=false \
  --triggers -R --hex-blob \
  --single-transaction \
  db_name > backup.sql

參數深度解析

參數	含義	建議
--opt	快捷組合：add-drop-table, add-locks, create-options, disable-keys, extended-insert, lock-tables, quick, set-charset	預設開啟，可用 --skip-opt 禁用
--quick / -q	流式讀取，不緩存結果集	大表必加
--single-transaction	事務一致性視圖 (InnoDB)	線上備份必加
--lock-all-tables / -x	全局讀鎖，所有表一致性	MyISAM/混合引擎
--disable-keys	INSERT 間禁用索引，導入後重建	MyISAM 大幅加速導入
--extended-insert	多行 VALUES 批次 INSERT	預設開啟，加快導入
--hex-blob	二進制欄位十六進制導出	含 BLOB/BINARY 必加
--routines / -R	導出存儲過程/函數	完整備份加上
--master-data=2	註釋記錄 binlog 位置	搭建 Slave 必加

還原/導入方法

命令行導入

mysql -h host -u user -p db_name < backup.sql

壓縮文件導入

gunzip < backup.sql.gz | mysql -u user -p db_name

客戶端 SOURCE 導入

mysql -u user -p
USE db_name;
SOURCE /path/backup.sql;

跨伺服器直接遷移

mysqldump -u user -p -h source_host db_name \
  --single-transaction --quick --routines --triggers \
  | mysql -u user -p -h target_host -C db_name

常見問題與解決

備份驗證腳本範例

#!/bin/bash
# backup_verify.sh
DB="mydb"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%F_%H%M)
FILE="${BACKUP_DIR}/${DB}_${DATE}.sql.gz"

# 1. 備份
mysqldump -u backup -p'pass' --single-transaction --quick --routines --triggers --hex-blob --default-character-set=utf8mb4 $DB | gzip > $FILE

# 2. 驗證文件非空且可解壓
if [[ -s $FILE ]] && gzip -t $FILE; then
    echo "[$(date)] Backup OK: $FILE"
else
    echo "[$(date)] Backup FAILED: $FILE" >&2
    exit 1
fi

# 3. 刪除 7 天前備份
find $BACKUP_DIR -name "${DB}_*.sql.gz" -mtime +7 -delete

建立標準化備份流程、定期演練還原，是資料安全的基石。