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

mysqldump 導出表結構與資料完整指南：參數詳解、條件導出、壓縮與自動化

編輯：MySQL 資料庫相關知識

核心語法速查

mysqldump [options] -u user -p db_name [tbl_name ...] > dump.sql
mysqldump [options] -u user -p --databases db1 db2 ... > dump.sql
mysqldump [options] -u user -p --all-databases > dump.sql

常用參數對照表

參數	短參數	說明	典型場景
`--no-data`	`-d`	僅導出結構（CREATE TABLE）	Schema 遷移、版本控管
`--no-create-info`	`-t`	僅導出資料（INSERT）	資料遷移、同步
`--single-transaction`		InnoDB 一致性備份（不鎖表）	線上熱備份必加
`--lock-tables`		鎖定所有表（MyISAM 必須）	MyISAM 混合環境
`--where="cond"`		條件導出	歷史資料歸檔、分表導出
`--routines`	`-R`	導出預存程序/函數	完整遷移
`--triggers`		導出觸發器（預設開啟）	完整遷移
`--events`	`-E`	導出 Event 排程器	完整遷移
`--hex-blob`		二進位欄位用十六進位	避免字元集亂碼
`--quick`	`-q`	逐行讀取不緩存（大表必加）	防止記憶體爆滿
`--compress`	`-C`	客戶端/伺服端壓縮傳輸	遠程備份加速
`--default-character-set`		指定連線字符集	utf8mb4 環境必加
`--master-data=2`		輸出 Binlog 位置（CHANGE MASTER TO 註解）	主從搭建、PITR

實戰範例集

1. 僅導出結構（Schema）

# 單表
mysqldump -u root -p -d dbname tbl_name > schema_tbl.sql

# 全庫
mysqldump -u root -p -d dbname > schema_all.sql

2. 導出結構 + 資料（完整備份）

# 線上熱備份標準命令
mysqldump -u root -p \
  --single-transaction \
  --routines --triggers --events \
  --hex-blob --quick \
  --master-data=2 \
  dbname | gzip > dbname_$(date +%F).sql.gz

3. 條件導出（如只要近 30 天訂單）

mysqldump -u root -p \
  --where="created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)" \
  dbname orders > orders_recent.sql

4. 遠程備份直接壓縮傳輸

mysqldump -h db_server -u root -p \
  --compress --single-transaction --quick \
  dbname | gzip -c | ssh backup_user@backup_server "cat > /backups/dbname_$(date +%F).sql.gz"

5. 只導出特定資料庫的特定表

mysqldump -u root -p dbname tbl1 tbl2 tbl3 > partial.sql

還原操作

# 一般還原
mysql -u root -p dbname < dump.sql

# 壓縮檔直接還原
gunzip < dump.sql.gz | mysql -u root -p dbname

# 進入 mysql 客戶端後
SOURCE /path/to/dump.sql;

自動化備份腳本（每日輪循 7 份）

#!/bin/bash
# /usr/local/bin/backup_mysql.sh
set -euo pipefail

USER="backup_user"
PASS="strong_password"
HOST="127.0.0.1"
DBS="db1 db2 db3"
DEST="/backup/mysql"
DATE=$(date +%F)
DAY=$(date +%u)  # 1-7

mkdir -p "$DEST"

for DB in $DBS; do
    FILE="$DEST/${DB}_${DAY}.sql.gz"
    mysqldump -h "$HOST" -u "$USER" -p"$PASS" \
        --single-transaction --routines --triggers --events \
        --hex-blob --quick --master-data=2 \
        "$DB" | gzip -c > "$FILE"
    echo "[$(date)] Backup $DB -> $FILE"
done

# 刪除 7 天前舊檔（輪循自動覆蓋，此行可選）
# find "$DEST" -name "*.sql.gz" -mtime +7 -delete

加入 crontab：0 3 * * * /usr/local/bin/backup_mysql.sh >> /var/log/backup_mysql.log 2>&1

避坑指南

密碼含特殊字元： 用 --defaults-extra-file=~/.my.cnf 避免命令列洩露。
大表導出慢： 加 --quick、--single-transaction、考慮 mydumper 多執行緒並行導出。
還原報錯 Unknown collation： 導出時加 --default-character-set=utf8mb4，還原前確認目標資料庫支援該編碼。
GTID 複製環境： 加 --set-gtid-purged=OFF 避免 GTID 衝突。
權限不足： 備份帳號需 SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, EVENT, PROCESS 權限。

總結

mysqldump 是 MySQL 邏輯備份的基石。掌握 -d/-t、--single-transaction、--where、--master-data、壓縮管線 等核心參數，配合自動化腳本與異地備份，即可構建可靠的資料保護體系。大表場景建議改用 mydumper/myloader（多執行緒、分塊、斷點續傳）大幅縮短備份窗口。