MySQL 備份神器 mysqldump 參數詳解與最佳實踐
mysqldump 是 MySQL 官方提供的邏輯備份工具，支援全庫、單表、結構、資料、壓縮、遠端等多種場景。掌握核心參數，能應對 99% 的備份遷移需求。
一、基礎語法
mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases
二、核心參數分類速查表
| 分類 | 參數 | 短選項 | 說明 | 建議場景 |
|---|
| 連線認證 | --host | -h | 主機名/IP | 遠端備份 |
| --user | -u | 用戶名 | |
| --password | -p | 密碼（不建議明文，留空交互輸入） | |
| --port | -P | 端口，預設 3306 | |
| 輸出控制 | --no-data | -d | 僅導出結構 (DDL) | 結構遷移、Schema 比對 |
| --no-create-info | -t | 僅導出資料 (INSERT) | 資料同步、測試資料製造 |
| --add-drop-table | | 每表前加 DROP TABLE IF EXISTS | 重複導入安全 |
| --add-drop-database | | 庫級 DROP DATABASE IF EXISTS | 全庫重建 |
| --complete-insert | -c | INSERT 含欄位名 | 跨版本/異構導入 |
| --extended-insert | -e | 多行合併 INSERT（預設開啟） | 大表加速，關閉利於斷點續傳 |
| 一致性與鎖 | --single-transaction | | InnoDB 熱備份，啟動事務快照 | InnoDB 生產必加 |
| --lock-all-tables | -x | 全庫鎖定 (FLUSH TABLES WITH READ LOCK) | MyISAM/混合引擎 |
| --lock-tables | -l | 逐庫鎖定 (LOCK TABLES READ) | 單庫一致性 |
| --flush-logs | -F | 備份前切換 Binlog | 配合 PITR 恢復 |
| --master-data | | =1 註釋 CHANGE MASTER; =2 註釋並含位置 | 建立從庫 |
| --dump-slave | | 從庫備份時記錄主庫位置 | 從庫做備份源 |
| 資料處理 | --hex-blob | | 二進位欄位十六進位導出 | 含 BLOB/BINARY/GEOMETRY 必加 |
| --quick | -q | 逐行讀取不緩存結果集 | 大表必加，防 OOM |
| --where | -w | WHERE 條件過濾資料 | 增量/分片導出 |
| --ignore-table | | 忽略指定表 | 排除大日誌/臨時表 |
| --replace | | 用 REPLACE INTO 替代 INSERT | 主鍵衝突覆蓋導入 |
| 存儲程序/事件 | --routines | -R | 導出預存程序/函數 | 完整遷移 |
| --triggers | | 導出觸發器（預設開啟） | |
| --events | -E | 導出 Event Scheduler 事件 | |
| --set-gtid-purged | | AUTO/OFF/ON，控制 GTID 資訊輸出 | GTID 複制環境 |
三、常用組合範例
1. 生產環境全量熱備份 (InnoDB)
mysqldump -h127.0.0.1 -uroot -p --single-transaction \
--master-data=2 --flush-logs --hex-blob --routines --triggers --events \
--quick --add-drop-table --extended-insert \
--databases db1 db2 > full_$(date +%F).sql
2. 僅導出結構（Schema 遷移）
mysqldump -uroot -p --no-data --add-drop-table --routines --triggers --events db_name > schema.sql
3. 大表分片導出（配合 where 條件）
mysqldump -uroot -p --quick --where="created_at >= '2024-01-01'" db_name big_table > part1.sql
4. 遠端壓縮傳輸導入
mysqldump -h src_host -uroot -p --single-transaction --quick db_name | gzip | ssh target_host "gunzip | mysql -uroot -p db_name"
5. 導出為 CSV/TSV（便於導入其他系統）
mysqldump -uroot -p --tab=/tmp/export --fields-terminated-by=, --fields-enclosed-by=\" --lines-terminated-by=\n db_name tbl_name
四、還原/導入最佳實踐
1. 直接導入 SQL 檔
mysql -uroot -p db_name < backup.sql
2. 進入 MySQL 再 source（適合大檔案、需交互）
mysql -uroot -p db_name
mysql> source /path/to/backup.sql
3. 關閉外鍵/唯一檢查加速導入
mysql -uroot -p db_name -e "SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0; SET AUTOCOMMIT=0;"
mysql -uroot -p db_name < backup.sql
mysql -uroot -p db_name -e "SET FOREIGN_KEY_CHECKS=1; SET UNIQUE_CHECKS=1; SET AUTOCOMMIT=1; COMMIT;"
4. 並行導入工具：myloader (mydumper 配套)
myloader -h 127.0.0.1 -u root -p -d /export_dir -t 8
五、常見坑點與解決
| 問題 | 原因 | 解決 |
|---|
| ERROR 1153: packet too large | max_allowed_packet 過小 | 調大 mysqld 與 client 端同參數 |
| 導出卡住/極慢 | 未加 --quick，結果集全載入記憶體 | 大表必加 --quick |
| 導入報 Duplicate entry | 目標表已有資料 | 加 --add-drop-table 或導入前 TRUNCATE |
| 函數導入報 1418 錯誤 | binlog 開啟且函數未聲明 DETERMINISTIC | SET GLOBAL log_bin_trust_function_creators=1 |
| 字符集亂碼 | 導出/導入字符集不一致 | 指定 --default-character-set=utf8mb4 |
| GTID 資訊干擾新叢集 | --set-gtid-purged=AUTO 輸出 GTID | 指定 --set-gtid-purged=OFF |
六、自動化備份腳本範例
#!/bin/bash
# /usr/local/bin/mysql_backup.sh
BACKUP_DIR="/backup/mysql/$(date +%F)"
mkdir -p "$BACKUP_DIR"
mysqldump -uroot -p${MYSQL_PWD} --single-transaction --master-data=2 \
--flush-logs --hex-blob --routines --triggers --events \
--quick --add-drop-database --all-databases | gzip > "$BACKUP_DIR/full.sql.gz"
find /backup/mysql -type f -mtime +7 -name '*.gz' -delete
# crontab: 0 2 * * * /usr/local/bin/mysql_backup.sh
七、進階替代方案
- Percona XtraBackup：物理熱備份，速度快、支援增量、不阻塞寫入，適合 TB 級
- mydumper/myloader：多執行緒邏輯備份/還原，比 mysqldump 快 3~10 倍
- MySQL Shell Dump/Load：MySQL 8.0+ 官方並行工具，支援 OCI Object Storage、S3 直傳
