MySQL主從環境下存儲過程，函數，觸發器，事件的複製情況

来源:http://www.cnblogs.com/ivictor/archive/2016/10/11/5947639.html
-Advertisement-
Play Games

下麵，主要是驗證在MySQL主從複製環境下，存儲過程，函數，觸發器，事件的複製情況，這些確實會讓人混淆。 首先，創建一張測試表 存儲過程 創建存儲過程 通過查看二進位日誌，可以看到該DDL語句已被記錄 執行存儲過程 查看二進位日誌中，記錄的是還是call p1('tom',10)操作記錄對應的SQL ...


下麵，主要是驗證在MySQL主從複製環境下，存儲過程，函數，觸發器，事件的複製情況，這些確實會讓人混淆。

首先，創建一張測試表

mysql> create table test.t1(name varchar(10),age int);
Query OK, 0 rows affected (0.10 sec)

 

存儲過程

創建存儲過程

delimiter //
CREATE procedure p1 (IN name varchar(10),IN age int)
 BEGIN
 insert into test.t1 values(name,age);
END//
delimiter ;

通過查看二進位日誌，可以看到該DDL語句已被記錄

# at 120
#161010 23:18:38 server id 1  end_log_pos 339 CRC32 0xae3dcfda     Query    thread_id=2    exec_time=0    error_code=0
use `test`/*!*/;
SET TIMESTAMP=1476112718/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`(IN name varchar(10),IN age int)
BEGIN
 insert into test.t1 values(name,age);
END
/*!*/;
DELIMITER ;

 

執行存儲過程

mysql> call p1('tom',10);
Query OK, 1 row affected (0.08 sec)

mysql> select * from t1;
+-------+------+
| name  | age  |
+-------+------+
| tom   |   10 |
+-------+------+
1 rows in set (0.01 sec)

 

查看二進位日誌中，記錄的是還是call p1('tom',10)操作記錄對應的SQL語句

# at 574
#161010 23:23:54 server id 1  end_log_pos 653 CRC32 0xc532cfae     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1476113034/*!*/;
BEGIN
/*!*/;
# at 653
#161010 23:23:54 server id 1  end_log_pos 833 CRC32 0x2982c7a8     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1476113034/*!*/;
insert into test.t1 values( NAME_CONST('name',_utf8'tom' COLLATE 'utf8_general_ci'), NAME_CONST('age',10))
/*!*/;
# at 833
#161010 23:23:54 server id 1  end_log_pos 864 CRC32 0xdf106f41     Xid = 56
COMMIT/*!*/;

由此可見，對於存儲過程，在主從複製中，記錄的是存儲過程對應的DML操作，而不是調用動作本身。

 

函數

創建函數

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

二進位日誌中的記錄如下：

# at 1246
#161010 23:34:01 server id 1  end_log_pos 1480 CRC32 0x3a1eb0a2     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1476113641/*!*/;
CREATE DEFINER=`root`@`localhost` FUNCTION `f1`(string VARCHAR(5)) RETURNS varchar(20) CHARSET utf8
    DETERMINISTIC
RETURN CONCAT('f1',string)
/*!*/;

 

執行函數

在這裡，其實要分兩種情況，一是binlog_format為statement，另一種情況為row

當binlog_format為statement時

mysql> show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.02 sec)

mysql> insert into t1(name) values(f1('steve'));
Query OK, 1 row affected (0.07 sec)

mysql> select * from t1;
+---------+------+
| name    | age  |
+---------+------+
| tom     |   10 |
| f1steve | NULL |
+---------+------+
2 rows in set (0.00 sec)

查看該語句對應的二進位日誌中的內容

# at 1480
#161010 23:37:58 server id 1  end_log_pos 1559 CRC32 0xf1f2c4a2     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1476113878/*!*/;
BEGIN
/*!*/;
# at 1559
#161010 23:37:58 server id 1  end_log_pos 1673 CRC32 0x0c9a73c5     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1476113878/*!*/;
insert into t1(name) values(f1('steve'))
/*!*/;
# at 1673
#161010 23:37:58 server id 1  end_log_pos 1704 CRC32 0x45419118     Xid = 67
COMMIT/*!*/;

可見在statement的二進位日誌格式下，複製的調用函數這個操作本身。

 

當binlog_format為row時

mysql> set session binlog_format='row';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1(name) values(f1('tiger'));
Query OK, 1 row affected (0.03 sec)

對應的二進位日誌的內容

# at 2139
#161010 23:43:35 server id 1  end_log_pos 2211 CRC32 0x7c74abd9     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1476114215/*!*/;
BEGIN
/*!*/;
# at 2211
#161010 23:43:35 server id 1  end_log_pos 2259 CRC32 0x657ac7ac     Table_map: `test`.`t1` mapped to number 78
# at 2259
#161010 23:43:35 server id 1  end_log_pos 2303 CRC32 0x3f15b37c     Write_rows: table id 78 flags: STMT_END_F
### INSERT INTO `test`.`t1`
### SET
###   @1='f1tiger' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
###   @2=NULL /* VARSTRING(30) meta=0 nullable=1 is_null=1 */
# at 2303
#161010 23:43:35 server id 1  end_log_pos 2334 CRC32 0xe5acc4aa     Xid = 80
COMMIT/*!*/;

可見，在row格式下，複製的不是函數操作本身，而是函數對應的值。

 

觸發器

首先，創建兩張測試表

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);

創建觸發器

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

二進位日誌中的記錄如下：

# at 556
#161011 10:46:52 server id 1  end_log_pos 776 CRC32 0xf065830f     Query    thread_id=4    exec_time=0    error_code=0
SET TIMESTAMP=1476154012/*!*/;
CREATE DEFINER=`root`@`localhost` TRIGGER t_test1 BEFORE INSERT ON test1
FOR EACH ROW
BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
END
/*!*/;

 

測試觸發器，向test1中添加一條記錄

mysql> insert into test1 values(1);
Query OK, 1 row affected (0.07 sec)

mysql> select * from test1;
+------+
| a1   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

mysql> select * from test2;
+------+
| a2   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

查看該語句對應的二進位日誌中的內容

# at 776
#161011 10:49:37 server id 1  end_log_pos 855 CRC32 0x0d73131b     Query    thread_id=5    exec_time=0    error_code=0
SET TIMESTAMP=1476154177/*!*/;
BEGIN
/*!*/;
# at 855
#161011 10:49:37 server id 1  end_log_pos 956 CRC32 0x6cf2e73c     Query    thread_id=5    exec_time=0    error_code=0
SET TIMESTAMP=1476154177/*!*/;
insert into test1 values(1)
/*!*/;
# at 956
#161011 10:49:37 server id 1  end_log_pos 987 CRC32 0x98e3a631     Xid = 51
COMMIT/*!*/;

可見，對於觸發器，主從均會觸發，複製只需記錄觸發條件本身，在本例中，即“insert into test1 values(1)”，而不會記錄所引發的觸發操作，即“INSERT INTO test2 SET a2 = NEW.a1”。

 

EVENT

創建EVENT

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

二進位日誌中的記錄如下：

# at 987
#161011 11:02:45 server id 1  end_log_pos 1218 CRC32 0x875a245e     Query    thread_id=5    exec_time=0    error_code=0
SET TIMESTAMP=1476154965/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
CREATE DEFINER=`root`@`localhost` EVENT e_test1
ON SCHEDULE
EVERY 10 SECOND
DO
INSERT INTO test.test1 VALUES (UNIX_TIMESTAMP())
/*!*/;

 

如果要讓EVENT執行，必須將event_scheduler設置為ON，預設為OFF。

mysql> set global event_scheduler=1;
Query OK, 0 rows affected (0.09 sec)

 

這時EVENT會執行，每10s向test1表中插入一條記錄

mysql> select * from test1;
+------------+
| a1         |
+------------+
|          1 |
| 1476155165 |
| 1476155175 |
+------------+
3 rows in set (0.01 sec)

對應的二進位日誌中的內容

# at 1319
#161011 11:06:05 server id 1  end_log_pos 1398 CRC32 0xcc4e1873     Query    thread_id=7    exec_time=0    error_code=0
SET TIMESTAMP=1476155165/*!*/;
SET @@session.sql_auto_is_null=1/*!*/;
BEGIN
/*!*/;
# at 1398
#161011 11:06:05 server id 1  end_log_pos 1520 CRC32 0x24ee06c6     Query    thread_id=7    exec_time=0    error_code=0
SET TIMESTAMP=1476155165/*!*/;
INSERT INTO test.test1 VALUES (UNIX_TIMESTAMP())
/*!*/;
# at 1520
#161011 11:06:05 server id 1  end_log_pos 1551 CRC32 0xa3ed03fa     Xid = 65
COMMIT/*!*/;

可見，對於EVENT，只是複製EVENT語句。

 

可能有人會疑問，slave上面是否同樣會執行event呢？

經測試證明，即使將slave上event_scheduler開啟了，也不會導致slave上event的執行，即使執行了stop slave操作，該event同樣不會執行。

通過查看主從上的event狀態，可以看出兩者的不同

Master

mysql> show events\G
*************************** 1. row ***************************
                  Db: test
                Name: e_test1
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 10
      Interval field: SECOND
              Starts: 2016-10-11 11:02:45
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

Slave

mysql> show events\G
*************************** 1. row ***************************
                  Db: test
                Name: e_test1
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 10
      Interval field: SECOND
              Starts: 2016-10-11 11:02:45
                Ends: NULL
              Status: SLAVESIDE_DISABLED
          Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

可以看出，相同的event，master上的狀態是ENABLED，而slave上的狀態確是SLAVESIDE_DISABLED。

如果要開啟slave上面的event，可通過如下命令開啟

alter event test.e_test1 enable;

經測試，直接update mysql.event沒有效果。

 

總結

1. 對於存儲過程，只是複製存儲過程中定義的DML語句。

2. 對於函數，在statement格式下，只是複製函數名，也就是說，函數在主從上同樣會被執行。

3. 對於觸發器，複製的只是觸發條件，而不會是觸發動作。也就是說，觸發器在主從上同樣會被運行。

4. 對於event，複製的也只是事件體中的DML語句。

 

參考

1. http://dev.mysql.com/doc/refman/5.7/en/create-procedure.html

2. http://dev.mysql.com/doc/refman/5.7/en/create-trigger.html

3. http://dev.mysql.com/doc/refman/5.7/en/create-event.html

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 學習sqlserve的一些筆記
    創建表： 表數據的增刪改查： 修改表結構： 去除重覆記錄： 排序： 查詢數據前多少條： 模糊查詢： 通配符：_ 表示任意的單個字元 % 匹配任意多個字元 [] 表示範圍內的單個字元 [^] 不在指定範圍內的單個字元 自定義轉義符：escape 空值判斷： 類型轉換函數：cast(表達式 as 數據類 ...
  • Laxcus大數據管理系統集群單機版
    Laxcus大數據管理系統是我們Laxcus大數據實驗室歷時5年，全體系全功能設計研發的產品，目前已經發展到2.1版本，並投入到多個大數據和雲計算項目中使用。Laxcus大數據管理系統採用松耦合架構，整合了大數據和關係資料庫的技術，實現了一站式數據處理，具有易操作、易維護、運行穩定的特點，並行節點數... ...
  • oracle（sql）基礎篇系列（一）——基礎select語句、常用sql函數、組函數、分組函數
    花點時間整理下sql基礎，溫故而知新，也方便複習查看。文章的demo來自oracle自帶的dept,emp,salgrade三張表。解鎖scott用戶，使用scott用戶登錄就可以看到自帶的表。 #使用oracle用戶登錄linux [oracle@localhost ~]$ sqlplus / a... ...
  • MySQL 5.7 create VIEW or FUNCTION or PROCEDURE
    ...
  • 就數據平臺建設，80%的500強企業都有一個共性
    80%的前500強企業就數據管理方面都有一個共性——管理規範，高效輔助流程。 但數據管理並不是一言即成，尤其是處於快速發展和轉型的企業。就數據系統而言，一旦系統增多，相應的數據問題也隨之而來。那麼如何統一有效地管理數據？實現數據可視化？這裡分享某百強集團搭建數據平臺的建設經驗。 ...
  • PostgreSQL 備忘
    truncate table page_frame_mst; select setval('page_frame_mst_id_seq', 1, false)； select setval('image_group_mst_id_seq', (select max(id) from image_gr ...
  • python--同一mysql資料庫下批量遷移數據
    最近接手些mysql資料庫維護，發現mysql在批量操作方面就是個渣渣啊，比起MS SQL SERVER簡直就是“不可同日而語”。 咨詢了下MySQL的高手，對於數據遷移這種問題，一種處理方式就是直接“一步到位” ，一次性將所有數據查詢插入到另外一個表，然後再刪除原表數據；另外一種處理方式就是使用p ...
  • 伺服器資料庫編碼格式問題
    最近做項目在部署到阿裡雲伺服器上之後出現了兩個問題： 1、亂碼問題。 2、ajax的php處理頁面裡面利用json_encode()函數返回json數據，則資料庫返回的數據只能是UTF8，如果是gbk則json也無法返回。 發現是資料庫編碼格式問題，網站使用的編碼格式為UTF8，資料庫的編碼格式調為 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類