mysqldump 核心參數深度解析與 mk-parallel-dump 效能實測對比

前言

本文深入探討 mysqldump 主要工作模式，並與 mk-parallel-dump（Maatkit 工具套件）進行實測對比，為備份策略選型提供依據。

一、mysqldump 關鍵參數行為分析

測試環境：單表 yejr，觀察各參數下的 SQL 執行序列。

1. -q (--quick)

僅在 SELECT 加入 SQL_NO_CACHE，不鎖表、不快取結果，逐行串流輸出。

LOCK TABLES `yejr` READ /*!32311 LOCAL */;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`;
UNLOCK TABLES;

2. --lock-tables

同上，但加 READ LOCAL LOCK，允許並發插入（MyISAM 特性），不阻塞讀取。

3. --lock-all-tables

發起全域讀鎖（FLUSH TABLES WITH READ LOCK），阻塞所有寫入，確保跨表一致性。備份完成會話斷開自動解鎖。

FLUSH TABLES;
FLUSH TABLES WITH READ LOCK;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`;

4. --master-data

在 --lock-all-tables 基礎上新增 SHOW MASTER STATUS，記錄二進制日誌位點，支援主從同步建立。

5. --single-transaction（InnoDB 推薦）

設定會話隔離級別為 REPEATABLE READ，啟動事務後釋放鎖，確保一致性視圖不受其他會話提交影響。

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
UNLOCK TABLES;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`;

6. --single-transaction --master-data

結合兩者：需短暫全域鎖獲取主庫狀態，改用 START TRANSACTION WITH CONSISTENT SNAPSHOT 替代 BEGIN，確保快照一致性。

FLUSH TABLES WITH READ LOCK;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SHOW MASTER STATUS;
UNLOCK TABLES;
SELECT ...;

原理：WITH CONSISTENT SNAPSHOT 僅在隔離級別為 REPEATABLE READ 或 SERIALIZABLE 時生效，等同 START TRANSACTION + 任意 InnoDB 表 SELECT。

二、mysqldump vs mk-parallel-dump 實測對比

測試資料：單表 yejr，約 600MB+。

關鍵發現

• mk-parallel-dump 透過並發顯著縮短備份窗口（快 ~30%）
• 致命缺陷：不支援 InnoDB 一致性備份（社群已提交補丁但未合併）
• 生產環境 InnoDB 必須用 mysqldump --single-transaction

建議策略

• InnoDB 核心業務：mysqldump --single-transaction --master-data=2
• MyISAM 唯讀/歸檔表：可嘗試 mk-parallel-dump 加速
• 大表備份考慮 xtrabackup（實體備份、支援增量、不阻塞）