萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

mysqldump 核心參數精通:從基礎備份到生產級熱備份策略

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-transactionInnoDB 一致性熱備(不鎖表)✅ 生產環境必加
--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-insertINSERT 包含欄位名結構變更時相容性好
--extended-insert多行合併 INSERT(預設開啟)大幅提升匯入速度

三大經典備份模式實戰

模式一:開發/測試環境 - 結構同步

# 僅結構,加 DROP TABLE,含常規物件
mysqldump -u dev -p --no-data --add-drop-table --routines --triggers --events db_name > schema_sync.sql

模式二:生產環境 - 熱備份 + Binlog 位點(標準 SOP)

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 即時壓縮,不佔用磁碟臨時空間

模式三:大表分表並行備份(TB 級應對)

# 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

還原驗證清單

  1. SHOW TABLES; 表數量對齊
  2. SELECT COUNT(*) FROM key_table; 核心表行數對齊
  3. CHECKSUM TABLE key_table; 校驗和對齊(需相同 MySQL 版本)
  4. 關鍵業務 SQL 抽樣查詢結果對齊
  5. SHOW SLAVE STATUS 從庫同步正常(若為主庫備份建從)

PHP 程式化調用 mysqldump(部署腳本整合)

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 不一致備份
  • ❌ 未驗證備份檔案完整性(大小、校驗和、可還原性)
  • ❌ 備份檔案存放在同一磁碟/伺服器(單點故障)
  • ❌ 不定期演練還原流程(RTO/RPO 未驗證)

mysqldump 看似簡單,參數組合卻蘊含深邃的架構權衡。建立標準化備份分級策略(每日全量+每小時增量 Binlog、異地備份、定期演練),才是資料資產的真正護城河。

copyright © 萬盛學電腦網 all rights reserved