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 1end_log_pos 339 CRC32 0xae3dcfda Querythread_id=2exec_time=0error_code=0use `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 1end_log_pos 653 CRC32 0xc532cfae Querythread_id=2exec_time=0error_code=0SET TIMESTAMP=1476113034/*！*/;BEGIN/*！*/;# at 653#161010 23:23:54 server id 1end_log_pos 833 CRC32 0x2982c7a8 Querythread_id=2exec_time=0error_code=0SET 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 1end_log_pos 864 CRC32 0xdf106f41 Xid = 56COMMIT/*！*/;由此可见，对于存储过程，在主从复制中，记录的是存储过程对应的DML操作，而不是调用动作本身。函数创建函数CREATE FUNCTION f1 （string VARCHAR（5））RETURNS VARCHAR（20） DETERMINISTICRETURN CONCAT（"f1",string）;二进制日志中的记录如下：# at 1246#161010 23:34:01 server id 1end_log_pos 1480 CRC32 0x3a1eb0a2 Querythread_id=2exec_time=0error_code=0SET TIMESTAMP=1476113641/*！*/;CREATE DEFINER=`root`@`localhost` FUNCTION `f1`（string VARCHAR（5）） RETURNS varchar（20） CHARSET utf8DETERMINISTICRETURN 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 1end_log_pos 1559 CRC32 0xf1f2c4a2 Querythread_id=2exec_time=0error_code=0SET TIMESTAMP=1476113878/*！*/;BEGIN/*！*/;# at 1559#161010 23:37:58 server id 1end_log_pos 1673 CRC32 0x0c9a73c5 Querythread_id=2exec_time=0error_code=0SET TIMESTAMP=1476113878/*！*/;insert into t1（name） values（f1（"steve"））/*！*/;# at 1673#161010 23:37:58 server id 1end_log_pos 1704 CRC32 0x45419118 Xid = 67COMMIT/*！*/;可见在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 1end_log_pos 2211 CRC32 0x7c74abd9 Querythread_id=2exec_time=0error_code=0SET TIMESTAMP=1476114215/*！*/;BEGIN/*！*/;# at 2211#161010 23:43:35 server id 1end_log_pos 2259 CRC32 0x657ac7ac Table_map: `test`.`t1` mapped to number 78# at 2259#161010 23:43:35 server id 1end_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 1end_log_pos 2334 CRC32 0xe5acc4aa Xid = 80COMMIT/*！*/;可见，在row格式下，复制的不是函数操作本身，而是函数对应的值。 触发器首先，创建两张测试表CREATE TABLE test1（a1 INT）;CREATE TABLE test2（a2 INT）;创建触发器delimiter //CREATE TRIGGER t_test1 BEFORE INSERT ON test1FOR EACH ROWBEGININSERT INTO test2 SET a2 = NEW.a1;END;//delimiter ;二进制日志中的记录如下：# at 556#161011 10:46:52 server id 1end_log_pos 776 CRC32 0xf065830f Querythread_id=4exec_time=0error_code=0SET TIMESTAMP=1476154012/*！*/;CREATE DEFINER=`root`@`localhost` TRIGGER t_test1 BEFORE INSERT ON test1FOR EACH ROWBEGININSERT 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 1end_log_pos 855 CRC32 0x0d73131b Querythread_id=5exec_time=0error_code=0SET TIMESTAMP=1476154177/*！*/;BEGIN/*！*/;# at 855#161011 10:49:37 server id 1end_log_pos 956 CRC32 0x6cf2e73c Querythread_id=5exec_time=0error_code=0SET TIMESTAMP=1476154177/*！*/;insert into test1 values（1）/*！*/;# at 956#161011 10:49:37 server id 1end_log_pos 987 CRC32 0x98e3a631 Xid = 51COMMIT/*！*/;可见，对于触发器，主从均会触发，复制只需记录触发条件本身，在本例中，即“insert into test1 values（1）”，而不会记录所引发的触发操作，即“INSERT INTO test2 SET a2 = NEW.a1”。 EVENT创建EVENTCREATE EVENT e_test1ON SCHEDULEEVERY 10 SECONDDOINSERT INTO test.test1 VALUES （UNIX_TIMESTAMP（））;二进制日志中的记录如下： # at 987#161011 11:02:45 server id 1end_log_pos 1218 CRC32 0x875a245e Querythread_id=5exec_time=0error_code=0SET TIMESTAMP=1476154965/*！*/;SET @@session.time_zone="SYSTEM"/*！*/;CREATE DEFINER=`root`@`localhost` EVENT e_test1ON SCHEDULEEVERY 10 SECONDDOINSERT 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 1end_log_pos 1398 CRC32 0xcc4e1873 Querythread_id=7exec_time=0error_code=0SET TIMESTAMP=1476155165/*！*/;SET @@session.sql_auto_is_null=1/*！*/;BEGIN/*！*/;# at 1398#161011 11:06:05 server id 1end_log_pos 1520 CRC32 0x24ee06c6 Querythread_id=7exec_time=0error_code=0SET TIMESTAMP=1476155165/*！*/;INSERT INTO test.test1 VALUES （UNIX_TIMESTAMP（））/*！*/;# at 1520#161011 11:06:05 server id 1end_log_pos 1551 CRC32 0xa3ed03fa Xid = 65COMMIT/*！*/;可见，对于EVENT，只是复制EVENT语句。可能有人会疑问，slave上面是否同样会执行event呢？经测试证明，即使将slave上event_scheduler开启了，也不会导致slave上event的执行，即使执行了stop slave操作，该event同样不会执行。通过查看主从上的event状态，可以看出两者的不同Mastermysql> show eventsG*************************** 1. row ***************************Db: testName: e_test1 Definer: root@localhost Time zone: SYSTEMType: RECURRINGExecute at: NULLInterval value: 10Interval field: SECONDStarts: 2016-10-11 11:02:45Ends: NULLStatus: ENABLEDOriginator: 1character_set_client: utf8collation_connection: utf8_general_ciDatabase Collation: utf8_general_ci1 row in set （0.00 sec）Slavemysql> show eventsG*************************** 1. row ***************************Db: testName: e_test1 Definer: root@localhost Time zone: SYSTEMType: RECURRINGExecute at: NULLInterval value: 10Interval field: SECONDStarts: 2016-10-11 11:02:45Ends: NULLStatus: SLAVESIDE_DISABLEDOriginator: 1character_set_client: utf8collation_connection: utf8_general_ciDatabase Collation: utf8_general_ci1 row in set （0.00 sec）可以看出，相同的event，master上的状态是ENABLED，而slave上的状态确是SLAVESIDE_DISABLED。总结1. 对于存储过程，只是复制存储过程中定义的DML语句。2. 对于函数，在statement格式下，只是复制函数名，也就是说，函数在主从上同样会被执行。3. 对于触发器，复制的只是触发条件，而不会是触发动作。也就是说，触发器在主从上同样会被运行。4. 对于event，复制的也只是事件体中的DML语句。MySQL 5.6主从同步配置案例 http://www.linuxidc.com/Linux/2016-04/130729.htm实现两个MySQL数据库之间的主从同步 http://www.linuxidc.com/Linux/2016-02/128100.htmLinux环境中MySQL主从同步--添加新的从库 http://www.linuxidc.com/Linux/2015-08/122448.htm通过 XtraBackup 实现不停机不锁表搭建MySQL主从同步 http://www.linuxidc.com/Linux/2015-08/121806.htmMySQL主从同步配置记录 http://www.linuxidc.com/Linux/2015-07/119939.htmLinux下MySQL数据库主从同步配置 http://www.linuxidc.com/Linux/2016-03/129138.htm参考1. http://dev.mysql.com/doc/refman/5.7/en/create-procedure.html2. http://dev.mysql.com/doc/refman/5.7/en/create-trigger.html3. http://dev.mysql.com/doc/refman/5.7/en/create-event.html本文永久更新链接地址