基本語法
mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases db1 db2 ...
mysqldump [OPTIONS] --all-databases
核心參數分類詳解
輸出格式控制
| 參數 | 說明 |
|---|
-c, --complete-insert | 完整 INSERT 包含欄位名，利於閱讀與部分恢復，但受 max_allowed_packet 限制 |
-e, --extended-insert | 多行 INSERT (預設開啟)，體積小、恢復快 |
--hex-blob | BINARY/VARBINARY/BLOB 十六進位輸出，避免特殊字元問題 |
-t, --no-create-info | 僅導出資料，無 CREATE TABLE |
-d, --no-data | 僅導出結構 |
--add-drop-table | CREATE 前加 DROP TABLE，便於覆蓋恢復 |
一致性與鎖控制 (關鍵)
| 參數 | 適用引擎 | 行為 |
|---|
--single-transaction | InnoDB | 啟動 REPEATABLE READ 快照，不鎖表，熱備份首選 |
--lock-tables | MyISAM | 鎖定當前庫所有表 |
--lock-all-tables (-x) | 所有 | 全域讀鎖，自動關閉 single-transaction |
--master-data=1|2 | 所有 | 輸出 CHANGE MASTER TO，1=生效，2=註解 |
-F, --flush-logs | 所有 | 備份前切換 Binlog，配合增量備份 |
字元集與相容性
--default-character-set=utf8：指定導出編碼，避免亂碼 (注意非 utf-8)。--compatible=mysql40,postgresql,oracle...：產生相容特定版本/異構資料庫的 SQL。
效能與除錯
-q, --quick：大表必加，流式讀取不緩存結果集。-v, --verbose：詳細輸出進度。--debug：除錯追蹤。
生產級備份命令模板
InnoDB 熱備份 (標準)
/usr/local/mysql/bin/mysqldump -uyejr -pyejr \
--default-character-set=utf8 \
--single-transaction \
--master-data=2 \
--routines --triggers \
--hex-blob \
--extended-insert=FALSE \
--quick \
db_name > db_name.sql
MyISAM 備份
/usr/local/mysql/bin/mysqldump -uyejr -pyejr \
--default-character-set=utf8 \
--lock-tables \
--extended-insert=FALSE \
--triggers -R --hex-blob \
db_name > db_name.sql
線上備份並記錄 Binlog 位點 (搭建從庫專用)
/usr/local/mysql/bin/mysqldump -uyejr -pyejr \
--default-character-set=utf8 \
--single-transaction --flush-logs --master-data=1 \
db_name > db_name.sql
還原方式
- 管道導入：
mysql -uusr -ppwd db < backup.sql
- SOURCE 指令：
mysql> SOURCE /absolute/path/backup.sql; (需 mysqld 執行身份可讀)
常見編碼問題解決
mysql> SET NAMES utf8; -- 注意無連字號
mysql> SOURCE /path/backup.sql;
進階：mysqlhotcopy 與 SQL 語法備份簡介
mysqlhotcopy：Perl 腳本，僅 MyISAM、類 Unix，速度最快 (複製檔案層級)。BACKUP TABLE / RESTORE TABLE：鎖表複製檔案，不備索引，恢復慢，不推薦。SELECT ... INTO OUTFILE / LOAD DATA INFILE：文字檔導出入，適合跨平台遷移或 ETL。
Binlog 增量備份策略
開啟 Binlog → 定期 FLUSH LOGS 切割 → 複製新 Binlog 檔 → mysqlbinlog 重放實現 PITR。配合主從架構在從庫備份可達零影響主庫。
