核心參數詳細說明
| 參數 | 功能 | 適用場景/建議 |
--opt | 啟用預設優化組合 (--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset) | **預設開啟**。一般備份直接用 `--opt` 即可。大表建議搭配 `--quick`。 |
--single-transaction | 啟動一致性快照 (REPEATABLE READ),不鎖表 | **InnoDB 熱備份必加**。與 `--lock-tables` 互斥。 |
--lock-all-tables (-x) | 全域讀鎖 (FLUSH TABLES WITH READ LOCK) | MyISAM/混合引擎備份一致性。會阻塞寫入,**生產慎用**。 |
--quick (-q) | 逐行串流輸出,不緩存結果集到記憶體 | **大表必加**,防止 OOM。 |
--extended-insert | 多行合併 INSERT (INSERT INTO t VALUES (...),(...),...) | 預設開啟。還原極快。若目標 `max_allowed_packet` 小需關閉 (`--skip-extended-insert`)。 |
--hex-blob | 二進位欄位以 0xHEX 輸出 | **含 BLOB/BINARY/GEOMETRY 必加**,避免編碼損壞。 |
--default-character-set | 指定連線字符集 | **必須與資料庫實際編碼一致** (如 `utf8mb4`),否則還原亂碼。 |
--routines (-R) | 包含預存程序/函數 | 生產備份**建議加上**。 |
--triggers | 包含觸發器 | 預設開啟 (`--skip-triggers` 關閉)。 |
--master-data=1/2 | 輸出 CHANGE MASTER TO (binlog 位置) | 建立 Slave 同步基準。**1=註解,2=可執行**。需 `RELOAD` 權限。 |
--flush-logs | 備份前切換新 binlog | 配合 `--master-data` 使用,確保位置精確。 |
--no-create-info (-t) | 僅匯出資料 (無 CREATE TABLE) | 資料遷移/同步專用。 |
--no-data (-d) | 僅匯出結構 | Schema 比對/遷移專用。 |
--compatible=name | 相容模式 (ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb...) | 跨版本/異構遷移時嘗試。不保證 100% 相容。 |
生產環境標準備份命令範本
MyISAM 表為主
/usr/local/mysql/bin/mysqldump \
-u backup_user -p'StrongPass' \
--default-character-set=utf8mb4 \
--opt --extended-insert=FALSE \
--triggers -R --hex-blob \
--lock-all-tables \
DB_Name > /backup/DB_Name_$(date +%F_%H%M).sql
InnoDB 表為主 (熱備份、不鎖表)
/usr/local/mysql/bin/mysqldump \
-u backup_user -p'StrongPass' \
--default-character-set=utf8mb4 \
--opt --extended-insert=FALSE \
--triggers -R --hex-blob \
--single-transaction \
--master-data=2 --flush-logs \
DB_Name > /backup/DB_Name_$(date +%F_%H%M).sql
還原操作
方法一:mysql 客戶端管道導入 (標準、高效)
mysql -u root -p DB_Name < /backup/DB_Name_20240101.sql
# 或指定編碼防亂碼
mysql -u root -p --default-character-set=utf8mb4 DB_Name < /backup/DB_Name_20240101.sql
方法二:SOURCE 指令 (需進入 mysql 互動模式)
mysql> USE DB_Name;
mysql> SOURCE /backup/DB_Name_20240101.sql;
-- 注意:需 mysql 進程使用者有檔案讀取權限,路徑為伺服器端絕對路徑。
線上備份進階:GTID 複寫基準 (MySQL 5.6+)
mysqldump ... --single-transaction --master-data=2 --flush-logs --set-gtid-purged=ON DB_Name > backup.sql
-- 還原到 Slave 後自動包含 GTID_SET,CHANGE MASTER TO MASTER_AUTO_POSITION=1 即可同步。
常見坑點避雷
- ❌ **忘記 `--single-transaction` 導致 InnoDB 鎖表阻塞業務**。
- ❌ **編碼不匹配** (`latin1` 連線匯出 `utf8` 資料) → 還原後中文全是 `???`。永遠指定 `--default-character-set`。
- ❌ **大表無 `--quick`** → 客戶端記憶體暴漲 OOM Kill。
- ❌ **還原前未建立資料庫/編碼不一致** → 物件建立失敗或資料截斷。
- ❌ **未驗證備份可用性** → 定期 `RESTORE VERIFYONLY` 或實測還原到測試環境。