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