mysqldump 是 MySQL 官方邏輯備份工具,掌握其核心參數組合,能應對從開發測試到生產災備的全場景需求。本文系統梳理參數矩陣與實戰策略。
| 類別 | 參數 | 作用 | 推薦場景 |
|---|---|---|---|
| 連線認證 | -u, --user | 用戶名 | 必填 |
-p, --password[=pwd] | 密碼(不帶值會提示輸入,更安全) | 必填 | |
-h, --host | 主機(預設 localhost) | 遠程備份必填 | |
-P, --port | 埠號(預設 3306) | 非標準埠必填 | |
| 輸出控制 | --no-data, -d | 僅匯出結構(無 INSERT) | 結構遷移、Schema 比對 |
--no-create-info, -t | 僅匯出資料(無 CREATE TABLE) | 資料同步、導入既有表 | |
--add-drop-table | 加入 DROP TABLE IF EXISTS | 重建環境、測試還原 | |
--add-drop-database | 加入 DROP DATABASE | 全庫重建 | |
--single-transaction | InnoDB 一致性熱備(不鎖表) | ✅ 生產環境必加 | |
--master-data=[1|2] | 記錄 Binlog 位點(1=CHANGE MASTER, 2=註釋) | 主從搭建、PITR 關鍵 | |
| 物件範圍 | --databases, -B | 指定多個資料庫(含 CREATE DATABASE) | 多庫備份 |
--tables | 指定表列表(需配合 --databases) | 部分表備份 | |
--ignore-table=db.tbl | 排除特定表 | 排除日誌/大表 | |
--routines, -R | 包含存儲過程/函數 | 完整備份必加 | |
--triggers | 包含觸發器 | 完整備份必加 | |
| 效能優化 | --quick, -q | 逐行讀取,不緩存全表結果集 | 大表必加,防 OOM |
--compress, -C | 壓縮 Client-Server 傳輸 | 跨網段備份 | |
--max-allowed-packet | 最大封包大小(如 1G) | 大 Blob/Text 欄位 | |
--net-buffer-length | 網路緩衝區大小 | 配合 max-allowed-packet | |
| 格式控制 | --hex-blob | 二進位欄位十六進位輸出 | 避免編碼問題 |
--complete-insert | INSERT 包含欄位名 | 結構變更時相容性好 | |
--extended-insert | 多行合併 INSERT(預設開啟) | 大幅提升匯入速度 |
# 僅結構,加 DROP TABLE,含常規物件
mysqldump -u dev -p --no-data --add-drop-table --routines --triggers --events db_name > schema_sync.sql
mysqldump -u backup_user -p \
--single-transaction \
--master-data=2 \
--routines --triggers --events \
--hex-blob --quick --compress \
--databases prod_db1 prod_db2 \
| gzip > /backup/$(date +%F_%H-%M)_prod.sql.gz
關鍵點:
--single-transaction:InnoDB MVCC 一致性快照,不阻塞寫入--master-data=2:輸出 -- CHANGE MASTER TO MASTER_LOG_FILE='bin.000001', MASTER_LOG_POS=12345; 註釋gzip 即時壓縮,不佔用磁碟臨時空間# 1. 獲取大表主鍵範圍
mysql -u backup_user -p -e "SELECT MIN(id), MAX(id) FROM big_table" db_name
# 2. 分範圍並行導出(假設 1-10000000,分 10 段)
for i in {0..9}; do
start=$((i*1000000+1))
end=$(( (i+1)*1000000 ))
mysqldump -u backup_user -p --single-transaction --where="id >= $start AND id < $end" db_name big_table \
| gzip > /backup/big_table_part${i}_$(date +%F).sql.gz &
done
wait
# 解壓並還原
gunzip < backup.sql.gz | mysql -u root -p target_db
# 或分步
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS target_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -u root -p target_db < backup.sql
SHOW TABLES; 表數量對齊SELECT COUNT(*) FROM key_table; 核心表行數對齊CHECKSUM TABLE key_table; 校驗和對齊(需相同 MySQL 版本)SHOW SLAVE STATUS 從庫同步正常(若為主庫備份建從)function mysqldumpBackup(array $config, string $outputFile): bool {
$cmd = sprintf(
'mysqldump -h%s -u%s -p%s --single-transaction --routines --triggers --events --quick --hex-blob %s | gzip > %s',
escapeshellarg($config['host']),
escapeshellarg($config['user']),
escapeshellarg($config['password']),
escapeshellarg($config['database']),
escapeshellarg($outputFile)
);
$descriptors = [1 => ['pipe', 'w'], 2 => ['pipe', 'w']];
$proc = proc_open($cmd, $descriptors, $pipes);
if (!is_resource($proc)) return false;
$stderr = stream_get_contents($pipes[2]);
fclose($pipes[2]);
$ret = proc_close($proc);
if ($ret !== 0) {
error_log("mysqldump 失敗: $stderr");
return false;
}
return true;
}
--lock-all-tables(阻塞所有寫入)--single-transaction 導致 InnoDB 不一致備份mysqldump 看似簡單,參數組合卻蘊含深邃的架構權衡。建立標準化備份分級策略(每日全量+每小時增量 Binlog、異地備份、定期演練),才是資料資產的真正護城河。