你的位置:首页 > 数据库

[数据库]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=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 1 end_log_pos 653 CRC32 0xc532cfae   Query  thread_id=2  exec_time=0  error_code=0SET 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=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 1 end_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 1 end_log_pos 1480 CRC32 0x3a1eb0a2   Query  thread_id=2  exec_time=0  error_code=0SET TIMESTAMP=1476113641/*!*/;CREATE DEFINER=`root`@`localhost` FUNCTION `f1`(string VARCHAR(5)) RETURNS varchar(20) CHARSET utf8  DETERMINISTICRETURN 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=0SET 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=0SET 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 = 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 1 end_log_pos 2211 CRC32 0x7c74abd9   Query  thread_id=2  exec_time=0  error_code=0SET 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 = 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 1 end_log_pos 776 CRC32 0xf065830f   Query  thread_id=4  exec_time=0  error_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 1 end_log_pos 855 CRC32 0x0d73131b   Query  thread_id=5  exec_time=0  error_code=0SET 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=0SET TIMESTAMP=1476154177/*!*/;insert into test1 values(1)/*!*/;# at 956#161011 10:49:37 server id 1 end_log_pos 987 CRC32 0x98e3a631   Xid = 51COMMIT/*!*/;

可见,对于触发器,主从均会触发,复制只需记录触发条件本身,在本例中,即“insert into test1 values(1)”,而不会记录所引发的触发操作,即“INSERT INTO test2 SET a2 = NEW.a1”。

 

EVENT

创建EVENT

CREATE EVENT e_test1ON SCHEDULEEVERY 10 SECONDDOINSERT 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=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 1 end_log_pos 1398 CRC32 0xcc4e1873   Query  thread_id=7  exec_time=0  error_code=0SET 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=0SET 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 = 65COMMIT/*!*/;

可见,对于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: 1character_set_client: utf8collation_connection: utf8_general_ci Database Collation: utf8_general_ci1 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: 1character_set_client: utf8collation_connection: utf8_general_ci Database Collation: utf8_general_ci1 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