MySQL 主從複制環境下存儲過程、函數、觸發器、事件的複製行為分析

MySQL 主從複制環境下存儲過程、函數、觸發器、事件的複製行為分析

透過實測驗證 MySQL 主從架構中，四種程式物件 (Stored Procedure, Function, Trigger, Event) 的 Binlog 記錄與複製機制差異，釐清常見誤區。

測試環境準備

CREATE TABLE test.t1 (name VARCHAR(10), age INT);

1. 存儲過程

建立過程

DELIMITER //
CREATE PROCEDURE p1 (IN name VARCHAR(10), IN age INT)
BEGIN
  INSERT INTO test.t1 VALUES (name, age);
END//
DELIMITER ;

Binlog 記錄 CREATE PROCEDURE DDL 語句。

呼叫過程

CALL p1('tom', 10);

關鍵發現： Binlog 記錄的是過程內部的 實際 DML 語句 (INSERT INTO test.t1 VALUES(...))，而非 CALL p1(...) 呼叫動作本身。從庫重放的是具體資料異動。

2. 函數

建立函數

CREATE FUNCTION f1 (string VARCHAR(5)) RETURNS VARCHAR(20) DETERMINISTIC
RETURN CONCAT('f1', string);

Binlog 記錄 CREATE FUNCTION DDL。

呼叫函數 - 兩種 Binlog 格式行為不同

STATEMENT 格式 (預設舊版)

INSERT INTO t1(name) VALUES(f1('steve'));

Binlog 記錄 原始呼叫語句，從庫同樣執行函數計算。

ROW 格式 (現代預設)

# Binlog 內容
INSERT INTO `test`.`t1` SET @1='f1steve', @2=NULL;

Binlog 記錄 函數計算後的結果值，從庫不執行函數，直接寫入結果。此模式下從庫不需存在函數定義也能複製成功。

3. 觸發器

建立觸發器

CREATE TRIGGER t_test1 BEFORE INSERT ON test1
FOR EACH ROW
BEGIN
  INSERT INTO test2 SET a2 = NEW.a1;
END;

觸發測試

INSERT INTO test1 VALUES(1);  -- 觸發器自動插入 test2

關鍵發現： Binlog 僅記錄 觸發條件語句 (INSERT INTO test1 VALUES(1))，不記錄觸發器內部動作。主從雙方各自執行觸發器邏輯，需確保從庫存在相同觸發器定義。

4. 事件

建立事件

CREATE EVENT e_test1 ON SCHEDULE EVERY 10 SECOND
DO INSERT INTO test.test1 VALUES (UNIX_TIMESTAMP());

啟用排程器

SET GLOBAL event_scheduler = ON;

複製行為

• Binlog 記錄事件內部的 DML 語句 (如 INSERT INTO test.test1 VALUES (1476155165))
• 從庫不會自動執行事件：SHOW EVENTS 顯示狀態為 SLAVESIDE_DISABLED
• 即使從庫開啟 event_scheduler=ON 也不會重複執行，避免主從雙寫衝突

總結比較表

實務建議

• 採用 binlog_format=ROW 減少函數/觸發器相容性問題
• DDL 變更 (建立/修改過程、函數、觸發器、事件) 需手動同步到從庫或用工具自動化
• 事件排程僅在主庫執行，從庫作為備援不啟用排程器
• 監控 SHOW SLAVE STATUS 確認複製無延遲