mysqldump 核心定位
- 邏輯備份工具：導出可移植 SQL 文本（
CREATE + INSERT）
- 位置：
$MYSQL_HOME/bin/mysqldump
- 反向操作：
mysql < dump.sql 或 source dump.sql
基礎用法三部曲
| 場景 | 命令 | 說明 |
|---|
| 全庫備份 | mysqldump -u root -p db_name > dump.sql | 含所有表結構與數據 |
| 單表備份 | mysqldump -u root -p db_name tbl_name > tbl.sql | 指定表名 |
| 條件導出 | mysqldump -u root -p --where="id > 1000" db_name tbl > part.sql | 支持任意 WHERE 子句 |
高頻參數詳細表
| 參數 | 短參數 | 作用 | 推薦場景 |
|---|
--add-drop-table | - | 每表前加 DROP TABLE IF EXISTS | 重複導入不報錯 |
--add-locks | - | INSERT 前後加 LOCK/UNLOCK TABLES | MyISAM 導入加速 |
--complete-insert | -c | INSERT 顯式列名 INSERT INTO t (a,b) VALUES... | 異構遷移、列順序不同 |
--delayed-insert | - | 使用 INSERT DELAYED | MyISAM 高併發寫入 |
--flush-logs | -F | 導出前切換 Binlog | 配合增量備份 |
--hex-blob | - | BLOB/二進制列用十六進制 0x... | 避免特殊字符轉義問題 |
--single-transaction | - | 啟動事務一致性視圖（InnoDB） | 熱備份不鎖表 |
--quick | -q | 不緩存結果集，逐行輸出 | 大表防 OOM |
--lock-tables | -l | 鎖定所有表（默認開啟） | MyISAM 一致性 |
--skip-lock-tables | - | 不鎖表（配合 --single-transaction） | InnoDB 熱備份 |
--routines | -R | 導出存儲過程/函數 | 完整遷移 |
--triggers | - | 導出觸發器 | 完整遷移 |
--events | -E | 導出事件調度器 | 完整遷移 |
--master-data=2 | - | 輸出 Binlog 位點為註釋 | 構建從庫 |
--compact | - | 精簡輸出（少註釋、無 SET 字符集） | 減小文件體積 |
--compress | -C | 客戶端/服務端壓縮協議 | 遠程備份節省帶寬 |
生產環境推薦組合
# InnoDB 熱備份標準命令
mysqldump -u backup_user -p \
--single-transaction --quick --hex-blob \
--routines --triggers --events \
--master-data=2 \
--databases db1 db2 > full_$(date +%F_%H%M).sql
# 遠程壓縮傳輸
mysqldump -h remote_host -u user -p --compress db_name | gzip > dump.sql.gz
恢復操作
# 全量恢復
mysql -u root -p db_name < dump.sql
# 單表恢復（需先建庫）
mysql -u root -p db_name < tbl.sql
# 並行恢復（大文件）
# 1. 拆分文件：awk '/^CREATE TABLE/ {f="part_"++i".sql"} {print > f}' dump.sql
# 2. 多線程導入：cat part_*.sql | xargs -P 4 -I {} mysql -u root -p db_name < {}
進階技巧
- 結構與數據分離：
-d 導結構，-t 導數據，便於審核/修改結構後再導數據
- 異構遷移預處理：
--compatible=postgresql,oracle,mssql 生成兼容語法
- 增量導出： 結合
--master-data=2 記錄 Binlog 位點，配合 mysqlbinlog 做增量
- 排除表：
--ignore-table=db.tbl（多個可重複）
- 只導數據無鎖：
--no-create-info --single-transaction --skip-lock-tables
性能優化建議
- 大表必加
--quick 防客戶端 OOM
- 網絡瓶頸加
--compress 或本地導出再傳輸
- 恢復端：
SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; 批量提交
- 超大庫（TB 級）改用 mydumper/myloader（多線程、分塊、壓縮、斷點續傳）
總結
mysqldump 是 MySQL 生態最通用的邏輯備份/遷移工具。掌握核心參數組合（--single-transaction --quick --hex-blob --routines --triggers --events --master-data=2），能覆蓋 95%+ 生產場景。超大規模請升級 mydumper 或物理備份 XtraBackup。
