程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL主從環境下存儲過程,函數,觸發器,事件的復制情況,mysql主從

MySQL主從環境下存儲過程,函數,觸發器,事件的復制情況,mysql主從

編輯:MySQL綜合教程

MySQL主從環境下存儲過程,函數,觸發器,事件的復制情況,mysql主從


下面,主要是驗證在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。

 

總結

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

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved