mysqldump 定位:邏輯備份瑞士軍刀
mysqldump 是 MySQL 官方提供的邏輯備份工具,輸出可重放的 SQL 文本。適用於:**跨版本遷移、異構同步、中小庫全量/增量備份、開發環境數據構建**。大庫(>100GB)首選物理備份。
核心參數分類速查表
🔥 必知必會:輸出控制類
| 參數 | 短參 | 作用 | 建議場景 |
--add-drop-table | - | 每個 CREATE TABLE 前加 DROP TABLE IF EXISTS | 反覆導入測試環境,避免表已存在報錯 |
--add-drop-database | - | 每個 CREATE DATABASE 前加 DROP DATABASE IF EXISTS | 整庫遷移重建 |
--no-create-info | -t | 不輸出 CREATE TABLE(僅數據) | 只同步數據、結構已存在 |
--no-data | -d | 不輸出 INSERT(僅結構) | Schema 遷移、對比結構差異 |
--complete-insert | -c | INSERT 語句包含列名 | 列順序可能變、跨版本導入、可讀性 |
--extended-insert | -e | 多行合併 INSERT INTO t VALUES (...),(...),(...) | **默認開啟**,大幅減小文件、加快導入 |
--skip-extended-insert | - | 每行一個 INSERT | 需逐行審計、合併衝突解決、異構導入 |
--hex-blob | - | 二進制列(BLOB/BINARY/GEOMETRY)以十六進制導出 | **強烈建議常開**,避免字符集/編碼導致二進制損壞 |
--default-character-set | - | 指定導出連接字符集 | **必加 utf8mb4**,防止中文亂碼 |
⚡ 性能與內存類
| 參數 | 短參 | 作用 | 建議 |
--quick | -q | 流式讀取(逐行獲取、寫入),不緩存全量結果集 | **大表必加**,防 OOM。--opt 默認包含 |
--opt | - | 組合優化:--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset | **5.1+ 默認開啟**。單庫備份推薦;多庫並行備份時 --lock-tables 會阻塞,可 --skip-lock-tables |
--max-allowed-packet | - | 客戶端/服務端通訊緩衝區上限 | 大字段(BLOB/TEXT)場景調大至 1G(需服務端同步調大) |
--net-buffer-length | - | 通訊緩衝區初始大小(影響多行 INSERT 打包大小) | 配合 --extended-insert,大表可調至 1M+ |
🔒 一致性與鎖控制類(核心權衡)
| 參數 | 短參 | 行為 | 適用引擎 | 阻塞寫入 | 一致性 |
--lock-tables | -l | 每庫鎖定所有表(READ LOCAL) | MyISAM | 是(表級) | 庫級一致 |
--lock-all-tables | -x | 全局讀鎖(FLUSH TABLES WITH READ LOCK) | 所有 | 是(全局) | 實例級一致 |
--single-transaction | - | 啟動事務 REPEATABLE READ,獲取一致性快照 | **InnoDB** | **否** | **實例級一致** |
--master-data[=1|2] | - | 輸出 Binlog 位點(1=SQL, 2=註釋),隱含 --lock-all-tables(除非加 --single-transaction) | 所有 | 視鎖策略 | 支持 PITR/搭建從庫 |
黃金組合(InnoDB 生產熱備):
mysqldump --single-transaction --master-data=2 --flush-logs --opt dbname > backup.sql
--single-transaction:非阻塞一致性快照
--master-data=2:註釋記錄 Binlog 位點,便於 PITR/搭建從庫
--flush-logs:輪轉 Binlog,備份位點對齊新文件
--opt:性能優化組合
🎯 篩選與並行類
| 參數 | 短參 | 作用 | 示例 |
--where | -w | WHERE 條件過濾導出行 | --where="created_at > '2024-01-01'" |
--tables | - | 覆蓋 --databases,後續參數視為表名 | --databases db1 --tables t1 t2 |
--databases | -B | 後續所有參數視為庫名,輸出含 CREATE DATABASE/USE | --databases db1 db2 |
--all-databases | -A | 備份所有庫 | --all-databases |
--parallel | - | 8.0.16+** 並行導出(實驗特性) | --parallel=4 |
📦 輸出格式類
| 參數 | 短參 | 輸出格式 | 用途 |
--tab=path | -T | 每表兩文件:.sql(結構) + .txt(數據,Tab 分隔) | 配合 LOAD DATA INFILE 極速導入 |
--xml | -X | XML 格式 | 異構系統交換、解析友好 |
--result-file | -r | 指定輸出文件(Windows 防換行符轉換) | Windows 腳本推薦 |
🛠 兼容性與調試類
| 參數 | 作用 |
--compatible=name | 輸出兼容目標:ansi,mysql323,mysql40,postgresql,oracle,mssql,db2,maxdb,no_key_options,no_table_options,no_field_options |
--compress/-C | 客戶端-服務端通訊壓縮(遠程備份加速) |
--verbose/-v | 詳細輸出進度 |
--debug | 調試日志 |
--force/-f | 遇錯繼續(慎用,可能產生不完整備份) |
生產級備份命令模板庫
模板 1:InnoDB 核心庫熱備(標準)
mysqldump --single-transaction --master-data=2 --flush-logs \
--routines --triggers --events \
--hex-blob --default-character-set=utf8mb4 \
--quick --opt \
-u backup_user -p'StrongPass' \
--databases db1 db2 \
| gzip > /backup/$(date +%F_%H%M)_dblist.sql.gz
模板 2:大表並行導出(配合 GNU parallel)
# 獲取大表列表
BIG_TABLES=$(mysql -N -u backup_user -p'Pass' -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='bigdb' AND DATA_LENGTH > 10*1024*1024*1024")
# 並行導出每張大表
echo "$BIG_TABLES" | parallel -j 4 \
"mysqldump --single-transaction --quick --hex-blob -u backup_user -p'Pass' bigdb {} | gzip > /backup/bigdb_{}_$(date +%F).sql.gz"
模板 3:僅導出結構(Schema 版本控制)
mysqldump --no-data --routines --triggers --events \
--default-character-set=utf8mb4 \
-u backup_user -p'Pass' \
--databases db1 db2 \
> /backup/schema_$(date +%F).sql
模板 4:跨機器直接同步(無落盤)
# 源 -> 目標(需目標庫已存在)
mysqldump --single-transaction --opt --compress \
-u backup_user -p'Pass' src_db | mysql -u root -p'Pass' -h target_host dst_db
恢復驗證:備份未驗證等於無備份
# 1. 解壓
gunzip -c backup.sql.gz > backup.sql
# 2. 空庫恢復驗證(測試環境)
mysql -u root -p -e "CREATE DATABASE verify_db;"
mysql -u root -p verify_db < backup.sql
# 3. 核心表行數/校驗和對比
mysql -u root -p -e "SELECT COUNT(*), CHECKSUM TABLE core_table FROM verify_db;"
# 對比生產庫結果
避坑指南
- 不要備份
mysql 系統庫(除非遷移實例),權限表版本差異極大,直接導入易導致權限系統崩潰
--lock-tables 不要用於 InnoDB 主庫:會阻塞所有寫入,改用 --single-transaction
- 大事務場景:
--single-transaction 下長事務會阻塞 FLUSH TABLES WITH READ LOCK(--master-data 觸發),設置 innodb_lock_wait_timeout 或避開業務高峰
- GTID 複制環境:備份文件含
SET @@SESSION.GTID_NEXT,恢復時需 SET GTID_PURGED 或用 --set-gtid-purged=OFF
- 定期測試恢復:每月至少一次全量恢復演練,記錄 RTO/RPO 實測值
總結
mysqldump 參數看似繁多,核心邏輯只有三條線:**輸出什麼(結構/數據/過濾)、怎麼保證一致性(鎖/事務)、怎麼跑得快(quick/extended-insert/parallel)**。掌握「InnoDB 熱備黃金組合」、「大表並行導出」、「Schema 版本化管理」、「恢復驗證閉環」,即可駕馭 99% 的邏輯備份場景。