MySQL 備份鎖機制深度解析：LOCK TABLES FOR BACKUP 與 LOCK BINLOG FOR BACKUP

從 FTWRL 到 Backup Lock：MySQL 熱備份鎖演進與核心原理

傳統 FLUSH TABLES WITH READ LOCK (FTWRL) 為獲取一致性位點而鎖定全庫，導致寫入完全阻塞、大查詢引發連鎖阻塞、主備延遲飆升。Percona 引入 Backup Lock（LOCK TABLES FOR BACKUP、LOCK BINLOG FOR BACKUP），在 Server 層實現細粒度鎖控制，大幅降低備份對業務影響。本文深度解析其架構設計、鎖相容性矩陣、物理備份流程重構、工具鏈適配與限制場景。

一、核心痛點：FTWRL 的三大副作用

1. 全局寫入阻塞：持有鎖期間，所有 DML/DDL 完全阻塞
2. 大查詢放大效應：FTWRL 需關閉表，長查詢導致鎖等待，DML 排隊時間不可控
3. 主備延遲：備庫上鎖阻塞 SQL 線程應用 Relay Log，複製延遲累積

二、Backup Lock 核心架構：兩類 MDL 全局鎖

三、鎖相容性矩陣與行為語義

1. Backup Lock (backup-lock, MDL_SHARED, MDL_EXPLICIT)

• 持有者本 Session：嘗試更新非 InnoDB 表 → 直接報錯
• 其他 Session：更新非 InnoDB 表 / 執行 DDL → 等待，show processlist 顯示 Waiting for backup lock
• InnoDB 表：讀寫完全正常，無任何阻塞

2. Binlog Lock (binlog-lock, MDL_SHARED, MDL_EXPLICIT)

• 持有者本 Session：執行更新 → 正常執行，不阻塞（不寫 Binlog 即不卡）
• 其他 Session：執行更新 → 寫 Binlog 瞬間等待，Waiting for binlog lock
• DDL：同理，僅在寫 Binlog 階段短暫等待

四、MDL 子系統集成：四大操作類別的鎖協議

A. 備份操作

// 申請 Backup Lock 共享鎖
acquire_lock(backup_lock, MDL_SHARED, MDL_EXPLICIT);

B. 庫級 DDL (CREATE/ALTER/DROP DATABASE)

// 1. 若已持有全局 Backup Lock → 直接報錯
// 2. 申請 Schema 排他鎖
acquire_lock(schema, MDL_EXCLUSIVE, MDL_TRANSACTION);
// 3. 申請 IX 意向排他鎖，阻塞後續 Backup/Global Lock
acquire_lock(global, MDL_INTENTION_EXCLUSIVE, MDL_STATEMENT);
acquire_lock(backup, MDL_INTENTION_EXCLUSIVE, MDL_STATEMENT);

C. 表級 DDL (RENAME/DROP/TRUNCATE/ALTER TABLE)

// 1. 申請 Table 排他鎖
acquire_lock(table, MDL_EXCLUSIVE, MDL_TRANSACTION);
// 2. 申請 Schema IX 鎖
acquire_lock(schema, MDL_INTENTION_EXCLUSIVE, MDL_TRANSACTION);
// 3. 檢測全局 Backup Lock → 報錯
// 4. 申請 Global/Backup IX 鎖

D. 表級 DML (非 InnoDB 寫操作)

// open_table 階段
if (mdl_request.type >= MDL_SHARED_WRITE && 
    engine->flags & HTON_SUPPORTS_ONLINE_BACKUPS) {
    // InnoDB 等支援線上備份引擎：跳過 Backup Lock 申請
} else {
    // MyISAM/MEMORY/CSV 等：申請 Backup Lock IX 鎖
    acquire_lock(backup, MDL_INTENTION_EXCLUSIVE, MDL_STATEMENT);
}

五、物理備份流程重構：XtraBackup 適配變化

關鍵優勢：

• 步驟 3：無 flush tables 關表操作，不被大查詢阻塞
• 步驟 3：InnoDB 業務表讀寫零阻塞
• 步驟 5→8：Binlog Lock 窗口極小（僅寫 Binlog 瞬間），DDL/DML 幾乎無感

六、XtraBackup 工具鏈適配要點