首页 > > 网络编程 > Mysql >

MySQL主从环境下存储过程，函数，触发器，事件的…

2018-06-17 23:05:37来源：未知 阅读 ()

新老客户大回馈,云服务器低至5折

下面，主要是验证在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中添加一条记录

在STATEMENT格式下

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”。

 

在ROW格式下

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

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

对应的二进制日志为：

# at 399
#161019 10:10:27 server id 1  end_log_pos 471 CRC32 0x667b6938     Query    thread_id=3    exec_time=0    error_code=0
SET TIMESTAMP=1476843027/*!*/;
BEGIN
/*!*/;
# at 471
#161019 10:10:27 server id 1  end_log_pos 519 CRC32 0xccaee383     Table_map: `test`.`test1` mapped to number 85
# at 519
#161019 10:10:27 server id 1  end_log_pos 567 CRC32 0x9625b60f     Table_map: `test`.`test2` mapped to number 86
# at 567
#161019 10:10:27 server id 1  end_log_pos 607 CRC32 0x620381e3     Write_rows: table id 86
# at 607
#161019 10:10:27 server id 1  end_log_pos 647 CRC32 0xff82eb9d     Write_rows: table id 85 flags: STMT_END_F

BINLOG '
E9YGWBMBAAAAMAAAAAcCAAAAAFUAAAAAAAEABHRlc3QABXRlc3QxAAEDAAGD467M
E9YGWBMBAAAAMAAAADcCAAAAAFYAAAAAAAEABHRlc3QABXRlc3QyAAEDAAEPtiWW
E9YGWB4BAAAAKAAAAF8CAAAAAFYAAAAAAAAAAgAB//4CAAAA44EDYg==
### INSERT INTO `test`.`test2`
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
E9YGWB4BAAAAKAAAAIcCAAAAAFUAAAAAAAEAAgAB//4CAAAAneuC/w==
'/*!*/;
### INSERT INTO `test`.`test1`
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
# at 647
#161019 10:10:27 server id 1  end_log_pos 678 CRC32 0x5384a1bc     Xid = 87
COMMIT/*!*/;

可见，在row格式下，会同时复制触发操作本身，此时，无论是否删除slave上的触发器，主从数据仍保持一致。但是在statement的格式下，如果删除了slave上的触发器，则会导致主从数据不一致。

 

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. 对于触发器，在statement格式下，复制的只是触发条件，而不会是触发动作。也就是说，触发器在主从上同样会被运行。

    但是在row格式下，则不仅会复制触发条件，还会复制触发动作。

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

 

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有

上一篇：导出Mysql数据库中的数据

下一篇：简单谈谈数据库索引

相关文章

IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设

网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源

网站联盟： 联盟新闻 联盟介绍 联盟点评 网赚技巧

行业资讯： 搜索引擎 网络游戏 电子商务 广告传媒

网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它

服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护

软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷

网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash

程序设计： Java技术 C/C++ VB delphi

网络知识： 网络协议 网络安全 网络管理 组网方案 Cisco技术

操作系统： Win2000 WinXP Win2003 Mac OS Linux FreeBSD

热门词条
最新资讯
热门关注
热门标签