您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 數據導出導入命令大全：mysqldump 參數詳解與實戰範例

編輯：MySQL 綜合教學

MySQL 數據導出導入命令大全：mysqldump 參數詳解與實戰範例

mysqldump 是 MySQL 官方邏輯備份工具，支援全庫、單表、結構、數據、壓縮、遠程傳輸等多種模式。本文系統整理常用參數與實戰範例，涵蓋備份、導出、還原、遷移全場景。

核心參數速查表

參數	說明
`-h, --host`	服務器主機（默認 localhost）
`-u, --user`	用戶名
`-p, --password[=pwd]`	密碼（建議不寫明文，交互輸入）
`-P, --port`	端口（默認 3306）
`--single-transaction`	InnoDB 熱備份關鍵：啟動事務快照，保證一致性不鎖表
`--master-data[=2]`	輸出 CHANGE MASTER 語句：1=註釋掉，2=註釋並包含位置（從庫部署用）
`--flush-logs`	備份前切換 Binlog，便於增量恢復
`-d, --no-data`	只導出結構（不含數據）
`-t, --no-create-info`	只導出數據（不含 CREATE TABLE）
`--no-create-db`	不包含 CREATE DATABASE 語句
`--add-drop-table`	每張表前加 DROP TABLE IF EXISTS
`--databases db1 db2`	指定多個庫（輸出包含 CREATE DATABASE）
`--all-databases`	備份所有庫
`--where="condition"`	按條件導出數據（如 `id>1000`）
`--routines`	包含存儲過程/函數
`--events`	包含事件調度器
`--triggers`	包含觸發器（默認開啟）
`--hex-blob`	二進制列用十六進制導出
`--quick, -q`	流式讀取大表，不緩存到內存
`--compress, -C`	壓縮傳輸（需客戶端/服務端支援）

實戰範例集錦

1. 全量備份（生產標準：熱備份+Binlog位置+切換日誌）

mysqldump -h localhost -u root -p \
  --single-transaction --master-data=2 --flush-logs \
  --routines --events --triggers --hex-blob \
  --databases mydb1 mydb2 > full_backup_$(date +%F).sql

配合壓縮：

mysqldump -u root -p --single-transaction --master-data=2 --flush-logs \
  --databases mydb1 mydb2 | gzip > full_backup_$(date +%F).sql.gz

2. 只導出表結構（Schema）

mysqldump -u root -p --no-data --databases mydb > schema_backup.sql
# 或單庫單表
mysqldump -u root -p --no-data mydb mytable > table_schema.sql

3. 只導出數據（Data Only）

mysqldump -u root -p --no-create-info --no-create-db mydb > data_only.sql
# 帶刪除語句，便於覆蓋導入
mysqldump -u root -p --no-create-info --no-create-db --add-drop-table mydb > data_with_drop.sql

4. 按條件導出部分數據

# 導出 id > 1000 的數據
mysqldump -u root -p mydb --no-create-db --no-create-info --add-drop-table=FALSE \
  --where="id > 1000" mytable > partial_data.sql

# 導出前 10000 行
mysqldump -u root -p mydb --single-transaction --where="pid=100776562 LIMIT 10000" \
  table1 > table1_subset.sql

5. 多庫備份

mysqldump -u root -p --databases db1 db2 db3 > multi_db_backup.sql

6. 全實例備份（含系統庫）

mysqldump --all-databases --single-transaction --master-data=2 --flush-logs \
  -u root -p > all_databases_backup.sql

7. 直接壓縮備份

mysqldump -u root -p mydb | gzip > mydb_backup_$(date +%F).sql.gz

8. 遠程備份到另一台服務器（流式傳輸，無中間文件）

mysqldump -u root -p -h source_host mydb | mysql -u root -p -h target_host -C mydb

9. 導出到文件（SELECT INTO OUTFILE，需 FILE 權限，服務器本地）

SELECT field1, field2 FROM mytable INTO OUTFILE '/backup/outfile.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

還原/導入命令

場景	命令
標準還原	`mysql -h localhost -u root -p mydb < backup.sql`
還原壓縮文件	`gunzip < backup.sql.gz \| mysql -u root -p mydb`
導入單表文件	`mysql -u root -p mydb < table_data.sql`
導入指定庫（備份含 CREATE DATABASE）	`mysql -u root -p < multi_db_backup.sql`

自動化備份腳本範例

#!/bin/bash
# /root/backup_mysql.sh
BACKUP_DIR="/home/backup/mysql"
DB_USER="root"
DB_PASS="your_password"
DBS="mydb1 mydb2"
DATE=$(date +%F_%H-%M)
mkdir -p $BACKUP_DIR

for DB in $DBS; do
    mysqldump -u$DB_USER -p$DB_PASS --single-transaction --master-data=2 --flush-logs \
        --routines --events --triggers --hex-blob --databases $DB | gzip > $BACKUP_DIR/${DB}_${DATE}.sql.gz
done

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

加入 crontab -e：0 2 * * * /root/backup_mysql.sh（每天凌晨 2 點備份）。

常見問題與技巧

大表導出慢：加 --quick（默認開啟）、--single-transaction、避免 --lock-tables。
max_allowed_packet 過小導入報錯：導入時加 --max-allowed-packet=1G 或臨時調大服務端參數。
字符集亂碼：導出導入均指定 --default-character-set=utf8mb4。
GTID 複制環境：備份加 --set-gtid-purged=OFF 避免 GTID 集合衝突。
只備份結構用於審計：mysqldump --no-data --routines --events --triggers -u root -p db > schema_audit.sql

總結

mysqldump 參數靈活組合可覆蓋 99% 邏輯備份場景。生產環境核心原則：必加 --single-transaction（InnoDB 熱備份）、--master-data=2（記錄 Binlog 位置）、--flush-logs（切換日誌）、壓縮存儲、定期驗證恢復演練。結合自動化腳本與異地備份，構建可靠的數據保護體系。