mysqldump 是 MySQL 官方邏輯備份工具，生成可重放的 SQL 腳本，支持結構/數據分離、壓縮傳輸、遠程備份、事務一致性快照。本文系統整理核心參數、常用場景命令、導入導出實戰與外鍵約束處理。

三種調用模式

# 1. 單庫備份（可指定表）
mysqldump [options] db_name [table1 table2...]

# 2. 多庫備份
mysqldump [options] --databases db1 [db2 db3...]

# 3. 全實例備份
mysqldump [options] --all-databases

核心參數速查表

參數	短參數	說明
--user	-u	用戶名
--password	-p	密碼（建議交互式輸入）
--host	-h	主機地址
--port	-P	端口
--single-transaction		InnoDB 一致性快照（不加鎖，推薦）
--lock-tables	-l	鎖定所有表（MyISAM 必需）
--master-data=2		輸出 CHANGE MASTER 註釋（含 binlog 位點）
--routines	-R	導出存儲過程/函數
--triggers		導出觸發器（默認開啟）
--events	-E	導出事件調度器
--no-data	-d	僅導出結構（DDL）
--no-create-info	-t	僅導出數據（INSERT）
--add-drop-table		建表前加 DROP TABLE IF EXISTS
--hex-blob		二進制列用十六進制導出
--default-character-set		指定連接字符集（建議 utf8mb4）
--set-gtid-purged=OFF		不輸出 GTID 信息（跨版本遷移用）

常用場景命令實戰

1. 生產環境標準備份（InnoDB 一致性快照 + binlog 位點）

mysqldump -u root -p \
  --single-transaction \
  --master-data=2 \
  --routines --triggers --events \
  --hex-blob \
  --default-character-set=utf8mb4 \
  --databases db1 db2 > backup_$(date +%F_%H%M).sql

2. 僅導出表結構（Schema 遷移/審核）

mysqldump -u root -p -d --add-drop-table db_name > schema.sql

3. 僅導出數據（數據遷移/初始化）

mysqldump -u root -p -t db_name > data.sql

4. 壓縮備份（管道流式壓縮，不佔用臨時磁盤）

mysqldump -u root -p --single-transaction db_name | gzip > backup_$(date +%F).sql.gz

# 解壓還原
gunzip -c backup_2024-01-15.sql.gz | mysql -u root -p db_name

5. 遠程備份（本地執行 mysqldump 連接遠程庫）

mysqldump -h 192.168.1.100 -u root -p --single-transaction db_name > remote_backup.sql

6. 導入/還原數據

# 方式一：重定向輸入
mysql -u root -p db_name < backup.sql

# 方式二：source 命令（需先進入 mysql 客戶端）
mysql -u root -p
USE db_name;
SOURCE /path/backup.sql;

# 方式三：管道流式導入（大文件推薦）
mysql -u root -p db_name < backup.sql

SELECT ... INTO OUTFILE / LOAD DATA INFILE（高性能批量導出入）

適用於大數據量遷移、異構導入，性能優於 mysqldump（無 SQL 解析開銷）。

導出 CSV 格式

SELECT * 
INTO OUTFILE '/tmp/export.csv'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM db_name.table_name;

限制：文件生成在服務器端，需 FILE 權限，目標文件不能存在，受 secure_file_priv 目錄限制。

導入 CSV 格式

LOAD DATA LOCAL INFILE '/tmp/export.csv'
INTO TABLE db_name.table_name
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;  -- 跳過表頭

CMD 命令行快速導入導出（Windows/XAMPP 示例）

# 進入 bin 目錄
d:
cd xampp\mysql\bin\

# 導入本地 SQL 文件
mysql -u root -p db_name < D:\backup\db_name.sql

# 導出數據庫
mysqldump -u root -p db_name > db_name_$(date +%F).sql

# 打開導出文件所在目錄
explorer .

外鍵約束導入失敗解決方案

-- 導入前禁用外鍵檢查
SET FOREIGN_KEY_CHECKS = 0;
SOURCE /path/backup.sql;
-- 導入後恢復檢查
SET FOREIGN_KEY_CHECKS = 1;

或在 mysqldump 導出時加 --disable-keys（僅禁用非唯一索引更新，不影響外鍵）。

避坑清單

• ❌ 密碼明文寫在命令行（歷史記錄洩露） → ✅ 使用 --defaults-extra-file 或交互輸入
• ❌ 大表不加 --single-transaction → ✅ 必加，避免長時間鎖表
• ❌ 忽略 --master-data → ✅ 主從搭建/PITR 恢復必需
• ❌ 字符集不指定 → ✅ 顯式 --default-character-set=utf8mb4
• ❌ 導出後不驗證 → ✅ head -20 backup.sql 檢查建表語句、字符集、GTID 註釋