前言：mysqldump 是邏輯備份的瑞士刀
支援結構+資料、單表/多表/全庫、壓縮、遠程傳輸、一致性快照、多種格式輸出。掌握核心參數組合，能應對 99% 備份場景。
一、基礎語法
mysqldump [OPTIONS] database [tables...]
mysqldump [OPTIONS] --databases db1 [db2...]
mysqldump [OPTIONS] --all-databases
二、核心參數分類詳解
1. 輸出內容控制
| 參數 | 短參 | 作用 |
|---|
| --no-create-info | -t | 不輸出 CREATE TABLE (僅資料) |
| --no-data | -d | 不輸出資料 (僅結構) |
| --add-drop-table | | 每表前加 DROP TABLE IF EXISTS |
| --add-drop-database | | 每庫前加 DROP DATABASE IF EXISTS |
| --complete-insert | -c | INSERT 含欄位名 (INSERT INTO t (a,b) VALUES...) |
| --extended-insert | -e | 多行 INSERT (預設開啟，速度快、體積小) |
| --skip-extended-insert | | 禁用多行 INSERT (便於 diff/合併衝突) |
| --hex-blob | | 二進制欄位用十六進制輸出 (避免編碼問題) |
| --routines | -R | 包含存儲程序/函數 |
| --triggers | | 包含觸發器 (預設開啟) |
| --events | -E | 包含 Event 排程任務 |
2. 一致性與鎖控制
| 參數 | 短參 | 作用 | 適用場景 |
|---|
| --single-transaction | | 啟動事務 + REPEATABLE READ，InnoDB 熱備不鎖表 | 生產首選 |
| --lock-tables | -l | 鎖定所有表 (MyISAM 必須) | MyISAM 混合庫 |
| --lock-all-tables | -x | 全庫鎖定 (FLUSH TABLES WITH READ LOCK) | 非事務引擎 |
| --flush-logs | -F | 備份前輪轉 Binlog (配合增量備份) | PITR 恢複基點 |
| --master-data=2 | | 輸出 CHANGE MASTER TO 註解 (含 Binlog 位置) | 主從搭建/遷移 |
3. 效能與網路
| 參數 | 短參 | 作用 |
|---|
| --quick | -q | 不緩衝結果集直接流式輸出 (大表必加) |
| --opt | | 綜合優化：--quick --add-drop-table --add-locks --extended-insert --lock-tables (預設開啟) |
| --compress | -C | 客戶端-服務端壓縮傳輸 (遠程備份) |
| --net_buffer_length | | 網路緩衝區大小 (大行需調大) |
4. 格式化輸出 (配合 -T 導出檔案)
--fields-terminated-by=, --fields-enclosed-by='"' --fields-escaped-by='\\'
--lines-terminated-by='\n' --tab=/path/to/dir
-- 產生 table.sql (結構) + table.txt (資料)
5. 連線與過濾
| 參數 | 短參 | 作用 |
|---|
| --host | -h | 遠程主機 |
| --port | -P | 端口 |
| --user | -u | 用戶名 |
| --password | -p | 密碼 |
| --socket | -S | Unix socket 路徑 |
| --where | -w | WHERE 條件過濾資料 (如 "-w id>100") |
| --ignore-table | | 排除表 (可多次) |
三、經典備份命令模板
1. 生產環境標準熱備 (InnoDB)
mysqldump -u backup_user -p \
--single-transaction \
--routines --triggers --events \
--master-data=2 \
--flush-logs \
--hex-blob \
--compress \
dbname | gzip > /backup/dbname_$(date +%F_%H%M).sql.gz
2. 結構遷移 (無資料)
mysqldump -u root -p -d --routines --triggers --events dbname > schema.sql
3. 遠程同步到另一台 MySQL
mysqldump -u root -p --single-transaction --compress dbname \
| mysql -h remote_host -u root -p -C dbname
4. 定時備份腳本 (cron 每日 02:00)
#!/bin/bash
BACKUP_DIR="/backup/mysql/$(date +%Y%m)"
mkdir -p "$BACKUP_DIR"
mysqldump -u backup -p'pass' \
--single-transaction --routines --triggers --events \
--master-data=2 --flush-logs --hex-blob \
mydb | gzip > "$BACKUP_DIR/mydb_$(date +%F_%H%M).sql.gz"
# 保留 30 天
find "$BACKUP_DIR/.." -name '*.gz' -mtime +30 -delete
四、還原實戰
# 1. 建立目標庫
mysqladmin -u root -p create target_db
# 2. 還原 (解壓後管道導入)
gunzip < backup.sql.gz | mysql -u root -p target_db
# 3. 或分步
mysql -u root -p target_db < backup.sql
# 4. 部分還原 (單表)
gunzip < backup.sql.gz | sed -n '/CREATE TABLE `t1`/,/CREATE TABLE `/p' | head -n -1 | mysql -u root -p target_db
五、效能優化建議
- 大表必加 --quick：避免客戶端記憶體 OOM。
- --opt 預設開啟，若需關閉多行 INSERT 用
--skip-extended-insert。
- 壓縮傳輸 --compress：遠程備份節省 70%+ 頻寬。
- 並行備份：
mydumper (多線程) 替代 mysqldump，速度提升 3-10 倍。
- 物理熱備：
xtrabackup (InnoDB) 秒級備份、不阻塞寫入，大庫首選。
六、常見陷阱避坑
| 現象 | 原因 | 對策 |
|---|
| 備份卡住/極慢 | 大表無 --quick、MyISAM 鎖表、網路慢 | 加 --quick、改 --single-transaction、本地備份再傳輸 |
| 還原報錯 Unknown collation | 版本不兼容 (8.0 → 5.7) | sed 替換 collation 或用相容版本 mysqldump |
| 備份檔過大 | 含大量 BLOB/TEXT、未壓縮 | 管道 gzip、排除大表單獨備份 |
| 主從同步位置錯誤 | 未用 --master-data=2 | 必加 --master-data=2 --flush-logs |
結語
mysqldump 參數眾多，但核心就三組：內容控制 (-t/-d/-c/-e)、一致性鎖 (--single-transaction/-l/-x)、效能網路 (--quick/--compress/--opt)。記住「InnoDB 熱備必加 --single-transaction、遠程必加 --compress、大表必加 --quick、PITR 必加 --master-data=2 --flush-logs」，即可駕馭絕大多數備份場景。大庫請升級 mydumper/xtrabackup 進入物理/並行備份新時代。
