mysqldump 參數完整指南：導出導入大數據庫的最佳實踐

mysqldump 參數完整指南：導出導入大數據庫的最佳實踐

當資料量大到 PHPMyAdmin、MySQL Workbench 等 GUI 工具力不從心時，命令列工具 mysqldump 是唯一可靠選擇。本文系統整理核心參數、常用組合與大數據庫遷移實戰技巧。

基本語法

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases db1 [db2 ...]
mysqldump [OPTIONS] --all-databases

不指定表則導出整庫。執行 mysqldump --help 查看版本支援的完整選項。

關鍵參數詳細解析

大數據庫導出實戰組合

方案 A：InnoDB 生產熱備份（推薦）

mysqldump -u root -p \
  --single-transaction \
  --quick \
  --extended-insert \
  --hex-blob \
  --routines --triggers --events \
  --master-data=2 \
  --flush-logs \
  --default-character-set=utf8mb4 \
  my_database > backup_$(date +%F).sql

方案 B：MyISAM 傳統備份（需鎖表）

mysqldump -u root -p \
  --lock-tables \
  --quick \
  --extended-insert \
  --hex-blob \
  --routines --triggers --events \
  my_database > backup_$(date +%F).sql

方案 C：僅導出結構（Schema 遷移）

mysqldump -u root -p --no-data --routines --triggers --events my_database > schema.sql

方案 D：分表並行導出（極大表加速）

# 先導出結構
mysqldump -u root -p --no-data my_database > schema.sql

# 大表分批導出（配合 WHERE 條件）
mysqldump -u root -p --quick --where="id BETWEEN 1 AND 1000000" my_database big_table > big_table_part1.sql
mysqldump -u root -p --quick --where="id BETWEEN 1000001 AND 2000000" my_database big_table > big_table_part2.sql
# ... 並行執行多個分批

導入優化技巧

1. 關閉自動提交與唯一檢查：

   mysql -u root -p my_database < backup.sql
   # 或在 SQL 檔案開頭加入：
   SET autocommit=0;
   SET unique_checks=0;
   SET foreign_key_checks=0;
   # 檔案結尾加入：
   SET foreign_key_checks=1;
   SET unique_checks=1;
   SET autocommit=1;
   COMMIT;
   

2. 大檔案分批導入：split -l 100000 backup.sql part_ 然後逐個導入
3. 使用 LOAD DATA INFILE 替代 INSERT：導出時加 --tab=/tmp --fields-terminated-by=',' 產生文字檔，導入快 20 倍
4. 調大 max_allowed_packet：客戶端與伺服器同步設大（如 1G）防止大封包中斷

常見問題排查