您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL mysqldump 數據導出導入完整指南與最佳實踐

編輯：MySQL 資料庫相關知識

mysqldump 核心參數詳解、常用場景模板、大數據優化策略、跨實例遷移實戰

mysqldump 是 MySQL 官方邏輯備份工具，生成可執行 SQL 文本，支持全量/增量、結構/數據、壓傳輸、跨版本遷移。本文系統整理核心參數、高頻用例、性能優化與避坑指南。

一、基本語法

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

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

二、核心參數分類詳解

參數	短選項	作用	推薦場景
`--opt`	-	綁定 `--quick --add-drop-table --add-locks --extended-insert --lock-tables`，最快導出/導入組合	常規備份首選
`--quick`	-q	不緩存結果集，直接流式輸出，避免大表 OOM	大表必加
`--extended-insert`	-e	多行 INSERT 語法，大幅縮小文件、加速導入	必加
`--single-transaction`	-	InnoDB 熱備核心：啟動事務一致性讀，無鎖導出	InnoDB 生產庫必加
`--master-data=2`	-	輸出 CHANGE MASTER 註釋（含 binlog 坐標），從庫搭建必備	搭建從庫
`--routines --triggers --events`	-R --triggers --events	導出存儲過程、觸發器、事件	完整遷移必加
`--no-data`	-d	僅導出結構	結構同步
`--no-create-info`	-t	僅導出數據	數據同步
`--where`	-w	導出符合條件的行（如 `-w "id>1000"`）	增量/分片導出
`--compress`	-C	客戶端-服務端壓縮傳輸	跨網段備份
`--hex-blob`	-	二進制列用十六進制導出，避免字符集問題	含 BLOB/BINARY 列

三、高頻用例模板

1. 生產庫標準全量備份 (InnoDB)

mysqldump -u$user -p$pass -h$host -P$port \
  --single-transaction --master-data=2 --routines --triggers --events \
  --quick --extended-insert --hex-blob \
  --databases db1 db2 > backup_$(date +%F_%H-%M).sql

2. 僅導出表結構（含 DROP IF EXISTS）

mysqldump -u$user -p$pass -d --add-drop-table db_name > schema.sql

3. 導出單表數據（分片遷移）

mysqldump -u$user -p$pass db_name table_name -w "id BETWEEN 1 AND 10000" > part1.sql

4. 遠程壓縮傳輸直入目標庫

mysqldump -h src_host -u$user -p$pass --quick --compress db_name | \
  mysql -h dst_host -u$user -p$pass db_name

四、導入恢復操作

1. 創建目標庫並導入

mysqladmin -u$user -p$pass create target_db
mysql -u$user -p$pass target_db < backup.sql

2. MySQL 客戶端內部導入

mysql> USE target_db;
mysql> SOURCE /path/to/backup.sql;

3. 並行導入加速（需拆分文件）

# 使用 mydumper/myloader 或手動拆表並行導入

五、大數據量優化策略

導出端： 必加 --quick 防 OOM，--extended-insert 減小體積，--single-transaction 無鎖一致性。
傳輸端： 跨機房加 --compress，本地回環可用管道直傳避免落盤。
導入端：
- 關閉二進制日志：SET sql_log_bin=0;（需 SUPER 權限）
- 關閉外鍵檢查：SET foreign_key_checks=0;
- 關閉唯一性檢查：SET unique_checks=0;
- 調大 innodb_buffer_pool_size、innodb_log_file_size、innodb_flush_log_at_trx_commit=2（導入期間臨時調整）
- 使用 LOAD DATA INFILE 替代 INSERT（需配合 --tab 導出文本文件）

六、常見避坑指南

字符集亂碼： 導出導入均指定 --default-character-set=utf8mb4，目標庫表字符集一致。
版本不兼容： 高版本導出導入低版本可能報錯，添加 --compatible=mysql40 或目標版本號。
GTID 衝突： GTID 模式下導入含 SET @@GLOBAL.GTID_PURGED 的備份需先 RESET MASTER 或使用 --set-gtid-purged=OFF。
權限不足： 導出視圖/觸發器需 SHOW VIEW/TRIGGER 權限，導出存儲過程需 SELECT mysql.proc。

七、現代替代工具

工具	特點	適用場景
mydumper/myloader	多線程並行、輸出分表文件、支援壓縮、斷點續傳	大庫（>100GB）高性能備份恢復
Percona XtraBackup	物理熱備、增量備份、流式傳輸、備份鎖不阻塞寫入	生產庫物理備份、快速搭建從庫
mysqlpump	MySQL 5.7+ 內置、並行導出、壓縮、對象級過濾	邏輯備份現代化替代

總結

mysqldump 仍是中小庫、邏輯遷移、結構同步的首選工具。掌握 --opt、--single-transaction、--master-data、--where 等核心參數組合，配合導入端優化，可覆蓋 90% 場景。超大庫、高可用要求場景請轉向 XtraBackup、mydumper 等專業工具。