1)基于语句的复制时,创建一张测试表

作者: www.9159.com  发布:2019-11-29

下面,主要是验证在MySQL主从复制环境下,存储过程,函数,触发器,事件的复制情况,这些确实会让人混淆。

www.9159.com 1

首先,创建一张测试表

 

mysql> create table test.t1(name varchar(10),age int);
Query OK, 0 rows affected (0.10 sec)

截图来自MySQL5.6的pdf版文档。

 

说明:

存储过程

1)基于语句的复制时,trigger会在slave上执行,所以slave上也需要有trigger的定义,不然会导致主从数据不一致的;

创建存储过程

2)基于行的复制时,trigger不会在slave上执行。因为复制的数据,不是sql语句。

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 ;

www.9159.com 2

 

 

执行存储过程

截图来自MySQL5.6的pdf版文档。

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)

说明:

 

基于行的复制时,存储过程,函数,触发器都只在master上执行,然后将执行之后的数据传给 slave 。不会将它们的sql语句发给slave. slave上看到的只有修改的行数据,不会有 存储过程、函数、触发器的调用语句。

查看二进制日志中,记录的是还是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操作,而不是调用动作本身。

www.9159.com 3

 

 

函数

截图来自MySQL5.6的pdf版文档。

创建函数

说明:

CREATE FUNCTION f1 (string VARCHAR(5))
RETURNS VARCHAR(20) DETERMINISTIC
RETURN CONCAT('f1',string);

说的基本和第一幅截图一样。

二进制日志中的记录如下:

基于语句的复制,trigger会在master和slave上都执行。

# 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)
/*!*/;

基于行的复制,trigger只会在master上执行,然后将数据行传给slave. 因为如果基于行的复制,salve上也执行trigger的话,会导致执行两次,导致主从数据不一致。

 

 

执行函数

www.9159.com 4

在这里,其实要分两种情况,一是binlog_format为statement,另一种情况为row

www.9159.com 5

当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)

截图来自MySQL5.6的pdf版文档。

查看该语句对应的二进制日志中的内容

说明:

# 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/*!*/;

1)对于存储过程,如果不是 确定性的,或者该存储过程不对数据进行修改,那么它就会导致在复制时,主从数据的不一致。这里指基于statement的复制。对于基于row的复制,不会导致主从不一致。

可见在statement的二进制日志格式下,复制的调用函数这个操作本身。

 

 

 

当binlog_format为row时

2. trigger 的局限:

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)

carete trigger 语法:

对应的二进制日志的内容

Syntax:
CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }
# 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/*!*/;

1)trigger 只能在表中的数据进行: insert, update, delete 时进行某种处理,也就是说他不能对表上的 DDL 语句进行某种处理!!!!

可见,在row格式下,复制的不是函数操作本身,而是函数对应的值。

2)MySQL triggers activate only for changes made to tables by SQL statements. They do not activate for changes in views, nor by changes to tables made by APIs that do not transmit SQL statements to the MySQL Server.  通过视图修改和通过API修改的表的数据时,不会触发trigger

 

 

触发器

----------------------------------------  分割线  

下面的内容转自:

首先,创建两张测试表

MySQL5.5 主从复制 (触发器,函数,存储引擎,事件处理)说明

mysql5.5 对触发器,函数,存储引擎,事件进行主从复制情况.

一、MySQL主从复制有三种模式.

1.binlog_format = row  : 日志中会记录成每一行数据被修改的形式(记录页面),然后在 slave 端再对相同的数据进行修改。

2.binlog_format = statement  : 每一条会修改数据的 SQL 都会记录到 master 的 bin-log 中。slave 在复制的时候 SQL 进程会解析成和原来 master 端执行过的相同的 SQL 再次执行。

3.binlog_format = mixed : 这个模式是在5.1.8版本之后, 才有的. mixed是row和statement的混合模式.

二、触发器的主从复制.

A .当binlog_format = statement 或 binlog_format = mixed (使用混合模式用的是statement 方式)  这种模式下复制情况

1. 主从复制的时候,主从触发器都受到definer从句的约束.只有主从上都有这个用户才能正常运行这个触发器.

2. 主服务器上SQL语句传到从服务器上,从服务器再执行SQL语句去触发从服务器的触发器(这里要说明是:主服务器不会把触发后的SQL传递给从服务器).

www.9159.com 6

这里可以看到,传递是原始的SQL语句.

B .当binlog_format = row 或 binlog_format = mixed(使用混合模式用的是row方式)  这种模式下复制情况

1. 主服务器把被修改的页面复制给从服务器,并且这个修改的页面的值是触发后的改变值.

  1. 因为这个页面的值是触发后改变的值, 所以在从服务器上可以不需要这个触发器.

  2. 删除从服务器上的触发器.一样的可以得到跟主服务器一样的值.

www.9159.com 7

三、存储过程的主从复制

1. 主服务器上的存储过程同样收到definer从句的约束.但是,在复制的时候,从服务上不需要有存储过程

A. 当binlog_format = statement 或binlog_format = mixed (使用混合模式用的是statement 方式)  这种模式下复制情况

www.9159.com 8

可以看到通过系统函数转换后的值复制给从服务器,不需要在从服务器上建立。(注:其实这里是错误的!和文档中的明显不一致,参见最后的总结。)

B .当binlog_format = row 或 binlog_format = mixed(使用混合模式用的是row方式)  这种模式下复制情况(传的都是数据)

www.9159.com 9

四、函数主从复制

A . 当binlog_format = statement 或binlog_format = mixed (使用混合模式用的是statement 方式)  这种模式下复制情况

1. 主从复制的时候,主从触发器都受到definer从句的约束.只有主从上都有这个用户才能正常运行这个函数

2.  主服务器上SQL语句复制从服务器上,从服务器再执行SQL语句再去调用从服务器的函数(主服务器不会把函数的返回值传给从服务器的)

www.9159.com 10

B. 当binlog_format = row 或 binlog_format = mixed(使用混合模式用的是row方式)  这种模式下复制情况(传的都是数据)

1.主服务器会直接把修改过的页面复制给从服务器,从服务器不需要有对应的函数

www.9159.com 11

五、事件主从复制

当事件有用函数,触发器,存储过程时.跟上面的操作情况是一样的.

但有一点不同的是.

在主服务器上建立一个event,当然,在从服务器上也会创建一个event..(默认情况下主event复制到从服务器的event是关闭着的)

1.主服务器上的event

www.9159.com 12

2.从服务器上的event

www.9159.com 13

如果在从服务器,开启事件.不仅主服务器复制过来的SQL语句执行一遍,从服务器上的EVENT也会执行.

(events 没有必要迁移到slave上,默认disable也是正确的。因为master上执行的events也会通过sql或者数据复制到slave上。)

===============分割线=============

总结:

MySQL5.6文档的说明和上面转帖的博文,说的是不太相符的。不相符的地方在 存储过程的说明上。我们还是应该以文档为准。博文毕竟良莠不齐。

 

在基于row的复制时:

不管是 存储过程,函数,还是触发器,都是将 执行之后的数据行,传给slave。slave上看到的都是数据,不会有它们的调用语句。slave上可以没有它们的定义。

 

在基于statement 的复制时:

1)存储过程、函数、触发器 都会在slave上执行。所以基于statement复制时,slave也必须有它们的定义。而且存储过程、函数必须是确定性www.9159.com ,的或者它们不修改表的数据。不然就会导致基于statement的复制,主从数据不一致。

2)存储过程和函数在定义时,有 确定性函数和非确定性函数的分别;比如 uuid(),now()这些函数肯定都是非确定性函数,因为它们每次执行的值是不确定的。所以在master和slave上分别执行会导致数据不一致。所以在基于语句复制的环境中,函数都必须是 确定性的(DETERMINISTIC)。不然就只能采用row复制格式了。

3)在基于statement复制时,trigger中定义的sql语句在执行时,不会传给slave,而仅仅传调用trigger的语句本身。所以不会导致定义在trigger中的sql执行两次。也就是说不会既将调用trigger的语句传给slave,又将在trigger执行时,在trigger中运行的那些sql语句传给slave. 因为这样会导致那些 sql 执行两次。

4)上面存储过程、函数、触发器在复制时的区别,也说明了,基于row格式的复制要优于基于statement格式的复制,基于row的复制明显要健壮很多。

     同时也说明:触发器、存储过程能不用最好就不要用了。

5)最后,虽然有文档的说明,实际使用时最好先通过自己的测试,来确认一下,到底 存储过程、函数、触发器是在基于statement的复制中是如何处理的。

    基于row的复制,很定是没有问题的。最好是采用基于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 eventsG
*************************** 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 eventsG
*************************** 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格式下,则不仅会复制触发条件,还会复制触发动作。

  1. 对于event,复制的也只是事件体中的DML语句。

 

参考

1. 

2. 

3. 

 

本文由9159.com发布于www.9159.com,转载请注明出处:1)基于语句的复制时,创建一张测试表

关键词:

上一篇:'把这个字符串分解成各个元素
下一篇:没有了