mysqldump 是 MySQL 官方提供的邏輯備份工具，支援全庫、單表、僅結構、僅資料等多種匯出模式，並提供豐富參數控制輸出格式、鎖定行為、壓縮傳輸等。本文整理最實用的操作指令、核心參數詳解與實戰範例，作為 DBA 與開發者的桌邊速查手冊。
| 需求 | 指令範例 |
|---|---|
| 整庫備份（含資料） | mysqldump -u user -p dbname > dbname.sql |
| 僅匯出結構（無資料） | mysqldump -u user -p -d dbname > dbname.sql |
| 單表備份（含資料） | mysqldump -u user -p dbname tblname > tblname.sql |
| 單表僅結構 | mysqldump -u user -p -d dbname tblname > tblname.sql |
| 參數 | 縮寫 | 功能說明 | 典型用法 |
|---|---|---|---|
| --all-databases | -A | 備份所有資料庫 | mysqldump -u root -p --all-databases > all.sql |
| --databases | -B | 指定多個資料庫（後續參數視為資料庫名） | mysqldump -u root -p --databases db1 db2 > two.sql |
| --no-data | -d | 不匯出資料，僅結構 | 同上 |
| --no-create-info | -t | 不匯出 CREATE TABLE，僅資料（INSERT） | mysqldump -u root -p -t dbname > data_only.sql |
| --add-drop-table | 每個 CREATE TABLE 前加 DROP TABLE（預設開啟） | --skip-add-drop-table 可關閉 | |
| --add-locks | 每表匯出前加 LOCK TABLES / UNLOCK TABLES（預設開啟） | --skip-add-locks 可關閉 | |
| --single-transaction | InnoDB 熱備必備：啟動事務一致性讀，不鎖表 | mysqldump -u root -p --single-transaction dbname > backup.sql | |
| --lock-all-tables | -x | 全域讀鎖，確保多庫一致性（會阻塞寫入） | 配合 --flush-logs 做全備基準點 |
| --master-data | 在輸出檔頭記錄 CHANGE MASTER TO（含 binlog 位置） | --master-data=2 以註解形式輸出 | |
| --flush-logs | 備份前刷新 binlog（配合 master-data 產生新 binlog） | 多庫備份時每庫各刷一次，除非加 --lock-all-tables | |
| --complete-insert | -c | INSERT 含欄位名，利於跨版本還原，但可能觸發 max_allowed_packet 限制 | |
| --compress | -C | 客戶端與 Server 間啟用壓縮傳輸 | 跨網段備份時減少頻寬 |
| --default-character-set | 指定連線字元集（預設 utf8） | --default-character-set=utf8mb4 | |
| --ignore-table | 排除特定表（需重複指定多次） | --ignore-table=db.tbl1 --ignore-table=db.tbl2 | |
| --events / --routines / --triggers | -E / -R / --triggers | 匯出 Event / Stored Procedure / Trigger | 完整遷移需加上 -E -R --triggers |
| --hex-blob | 二進位欄位以十六進位輸出（避免編碼問題） | 含 BLOB/BINARY/GEOMETRY 時建議開啟 | |
| --quick / --opt | --quick：不緩衝直接串流；--opt：組合優化參數（預設開啟） | 大表備份必加 --quick |
#!/bin/bash # 每日全備 + binlog 位置記錄 + 壓縮存檔 mysqldump -u backup_user -p'strong_pass' \ --single-transaction \ --master-data=2 \ --flush-logs \ --routines --triggers --events \ --hex-blob \ --quick \ --compress \ --default-character-set=utf8mb4 \ --all-databases | gzip > /backup/mysql/full_$(date +%F).sql.gz
# 建立空資料庫（若備份檔未含 CREATE DATABASE） mysql -u root -p -e "CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;" # 導入 mysql -u root -p dbname < dbname.sql # 或直接在 mysql shell 內 mysql> SOURCE /path/to/dbname.sql;
gunzip < /backup/mysql/full_2024-01-15.sql.gz | mysql -u root -p
--complete-insert 可能導致封包過大，還原時同樣需調大 Server 端 max_allowed_packet（如 1G）。--default-character-set 必須一致，建議統一 utf8mb4。SET @@SESSION.SQL_LOG_BIN=0; 避免 GTID 衝突。mysql 系統庫時務必加 --flush-privileges，還原後執行 FLUSH PRIVILEGES;。mysqldump --help | grep -A1 -E '(all-databases|single-transaction|master-data|ignore-table|hex-blob|quick|compress)'
掌握 mysqldump 的參數組合，能讓備份策略既安全又高效，是資料庫運行維護的核心技能之一。
