核心語法
mysqldump [options] db_name [tables]
mysqldump [options] --databases db1 [db2 ...]
mysqldump [options] --all-databases
常用操作範例
| 操作 | 命令 |
|---|
| 備份單庫 | mysqldump -u root -p dbname > dbname.sql |
| 備份多庫 | mysqldump -u root -p --databases db1 db2 > multi.sql |
| 備份全庫 | mysqldump -u root -p --all-databases > all.sql |
| 備份單表 | mysqldump -u root -p dbname table1 table2 > tables.sql |
| 按條件導出 | mysqldump -u root -p dbname table --where="id>100" > part.sql |
| 只導結構不導數據 | mysqldump -u root -p dbname -d > schema.sql |
| 只導數據不導結構 | mysqldump -u root -p dbname -t > data.sql |
| 壓縮備份 | mysqldump -u root -p dbname | gzip > dbname.sql.gz |
| 還原單庫 | mysql -u root -p dbname < dbname.sql |
| 還原全庫 | mysql -u root -p < all.sql |
| 跨服務器遷移 | mysqldump -u root -p dbname | mysql -h remote_host -u root -p dbname |
核心參數分類詳解
1. 輸出內容控制
| 參數 | 說明 |
|---|
--add-drop-table | 每表前加 DROP TABLE IF EXISTS，避免導入報錯 |
--add-drop-database | 每庫前加 DROP DATABASE IF EXISTS (慎用) |
--no-create-info / -t | 不輸出 CREATE TABLE (只導數據) |
--no-data / -d | 不輸出 INSERT (只導結構) |
--complete-insert / -c | 完整 INSERT 包含列名，利於異構遷移，但稍大 |
--extended-insert / -e | 多行合併 INSERT (默認開啟)，大幅縮小文件、加快導入 |
--hex-blob | 二進制列 (BLOB/BINARY/VARBINARY) 用十六進制輸出，避免編碼問題 |
--routines / -R | 導出存儲過程與函數 |
--triggers | 導出觸發器 (默認開啟) |
--events | 導出 Event 調度器任務 |
2. 效能與內存優化
| 參數 | 說明 |
|---|
--quick / -q | **大表必加**，逐行讀取寫入文件，不載入內存 (默認開啟) |
--opt | 綁定優化組合：--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset (默認開啟) |
--disable-keys / -K | INSERT 前禁用索引，導入後重建，**MyISAM 大表導入必加** |
--max-allowed-packet | 設置最大包大小 (如 1GB)，防止大行/大 INSERT 截斷 |
--net-buffer-length | 配合 --extended-insert 調整批次大小 |
3. 一致性與鎖控制 (關鍵)
| 參數 | 適用引擎 | 行為 | 推薦場景 |
|---|
--single-transaction | InnoDB (事務型) | 啟動事務 + 一致性快照，**無阻塞**，熱備份首選 | 生產環境 InnoDB 主庫備份 |
--lock-tables / -l | MyISAM | 鎖定當前庫所有表 (READ LOCAL)，允許並發插入 | MyISAM 庫備份 |
--lock-all-tables / -x | 所有 | 全局讀鎖 (FLUSH TABLES WITH READ LOCK)，**阻塞所有寫入** | 需全局一致性點 (如建立從庫) |
--master-data=1|2 | InnoDB | 輸出 CHANGE MASTER TO (1=可執行, 2=註釋)，需配合 --single-transaction | 主從搭建、基於時間點恢復 (PITR) |
--flush-logs / -F | 所有 | 備份前刷新 binlog，配合 --master-data 固定 binlog 位置 | PITR 備份 |
4. 兼容性與格式
| 參數 | 說明 |
|---|
--compatible=name | 輸出兼容其他 DB/舊版本：ansi,mysql40,postgresql,oracle,mssql,db2 等 |
--default-character-set=charset | 指定導出字符集 (如 utf8mb4)，**防止亂碼必加** |
--set-charset | 輸出 SET NAMES 語句 (默認開啟) |
--tab=path / -T | 輸出為 TSV 文本文件 (.sql 結構 + .txt 數據)，需 FILE 權限、同機器 |
--xml / -X | 輸出 XML 格式 |
生產環境推薦配置模板
InnoDB 熱備份 (主庫、無阻塞)
mysqldump \
--single-transaction \
--quick \
--max-allowed-packet=1G \
--hex-blob \
--routines \
--triggers \
--events \
--master-data=2 \
--flush-logs \
--default-character-set=utf8mb4 \
-u backup_user -p \
dbname | gzip > dbname_$(date +%F_%H%M).sql.gz
MyISAM 備份 (需鎖表)
mysqldump \
--lock-tables \
--disable-keys \
--quick \
--hex-blob \
--routines \
--triggers \
--default-character-set=utf8mb4 \
-u backup_user -p \
dbname | gzip > dbname_$(date +%F_%H%M).sql.gz
建立從庫專用 (記錄 binlog 位置)
mysqldump \
--all-databases \
--single-transaction \
--master-data=1 \
--flush-logs \
--default-character-set=utf8mb4 \
-u root -p > setup_slave_$(date +%F).sql
導入後在從庫執行 SOURCE setup_slave.sql 即可自動配置同步位置。
還原最佳實踐
- 預先建庫：
CREATE DATABASE dbname CHARACTER SET utf8mb4;
- 關閉外鍵檢查： 導入前
SET FOREIGN_KEY_CHECKS=0;，導入後 SET FOREIGN_KEY_CHECKS=1;
- 大文件分批導入： 使用
sed 分割或 pv 監控進度 pv backup.sql.gz | gunzip | mysql -u root -p dbname
- 並行導入： 多表可用
mysqlpump (5.7+) 或手動拆分文件並行 mysql
避坑指南
- 版本不匹配： 高版本導出導入低版本需加
--compatible=mysql56 等
- 字符集亂碼： **必加
--default-character-set=utf8mb4**，確保客戶端、連接、結果集編碼一致
- GTID 衝突： 5.6+ GTID 模式下跨實例導入需
--set-gtid-purged=OFF
- 大表導入卡住： 檢查
max_allowed_packet、innodb_log_file_size、bulk_insert_buffer_size
