阿里云-云小站（无限量代金券发放中）
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL主从环境下存储过程，函数，触发器，事件的复制情况

178次阅读
没有评论

共计 7817 个字符，预计需要花费 20 分钟才能阅读完成。

下面，主要是验证在 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 语句。

MySQL 5.6 主从同步配置案例  http://www.linuxidc.com/Linux/2016-04/130729.htm

实现两个 MySQL 数据库之间的主从同步 http://www.linuxidc.com/Linux/2016-02/128100.htm

Linux 环境中 MySQL 主从同步 – 添加新的从库 http://www.linuxidc.com/Linux/2015-08/122448.htm

通过 XtraBackup 实现不停机不锁表搭建 MySQL 主从同步 http://www.linuxidc.com/Linux/2015-08/121806.htm

MySQL 主从同步配置记录 http://www.linuxidc.com/Linux/2015-07/119939.htm

Linux 下 MySQL 数据库主从同步配置 http://www.linuxidc.com/Linux/2016-03/129138.htm

参考

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

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-10/135939.htm

正文完
星哥玩云-微信公众号
post-qrcode
MongoDB MySQL MySQL主从 MySQL主从环境下存储过程，函数，触发器，事件的复制情况 NoSQL Oracle Oracle教程 Oracle数据库 PostgreSQL 数据库
发表至： 数据库技术
2022-01-22
 0
星锅
版权声明：本站原创文章，由 星锅 于2022-01-22发表，共计7817字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
【腾讯云】推广者专属福利，新客户无门槛领取总价值高达2860元代金券，每种代金券限量500张，先到先得。
SparkSQL读取Hive中的数据
使用Percona Data Recovery Tool for InnoDB工具问题
安装Oracle遭遇INS-30131错误解决思路
MySQL Scheduler Events带来的风险
MySQL二进制文件规范安装
Oracle数据加载和卸载
Oracle视图编译错误解决办法
Linux下Redis集群安装部署及使用详解
阿里云-最新活动爆款每日限量供应
评论（没有评论）
验证码
星哥说事
星哥说事
分享互联网知识
阅读量
3592092
评论数
4
文章搜索
热门文章
选择PHP与Python，可以考虑这三个问题

选择PHP与Python，可以考虑这三个问题

导读 尽管 RESTful API 和跨平台库已经使网页开发容易起来，但依然没有解决阻碍并隔分开发者、管理者和...
Centos 7平滑无缝升级PHP7.1.0到PHP 7.1.5

Centos 7平滑无缝升级PHP7.1.0到PHP 7.1.5

实验环境：CentOS Linux release 7.3.1611 (Core) 内核版本：Linux ve...
介绍ansible的Ad-hoc与commands模块

介绍ansible的Ad-hoc与commands模块

Ad-Hoc 是指 ansible 下临时执行的一条命令，并且不需要保存的命令，对于复杂的命令后面会说 pla...
Linux安装使用pidstat命令以对进程数据进行监控

Linux安装使用pidstat命令以对进程数据进行监控

导读 Linux 系统监控的命令其实有很多，pidstat 命令就是其中的一种，使用 pidstat 命令可以...
linux下使用tree命令以树形结构显示文件目录结构

linux下使用tree命令以树形结构显示文件目录结构

导读 今天小编来给分享 Linux 系统下一个非常有用的命令的使用：tree 命令可以以树形结构显示文件目录结...
CDN
阿里云CDN-提高用户访问的响应速度和成功率
随机文章
如何解决Oracle临时表空间过大

如何解决Oracle临时表空间过大

导读 解决 Oracle 临时表空间过大有两种方法，方法一增加临时表空间的大小，方法二重建临时表空间，解决临时...
详解：Linux中设置SSH无密码安全登录

详解：Linux中设置SSH无密码安全登录

作为一名运维人员来说，管理 1 - 5 台机器尚有余力，但如果是 10 台、100 台或更多服务器，是不是每次...
系统加固-Linux不允许用户使用密码登录，只能使用密钥登录

系统加固-Linux不允许用户使用密码登录，只能使用密钥登录

一、密码登录的安全隐患 传统的密码登录方式，尽管简单直接，却存在诸多安全隐患。首先，密码本身容易被猜测或通过暴...
linux中文文件名乱码解决

linux中文文件名乱码解决

由于安装英文版的系统不支持中文，出现中文文件名乱码。下面操作时在 centos 上安装中文支持。 1) 先下载...
解决vsftpd连接变慢的情况

解决vsftpd连接变慢的情况

一、问题现象 业务反馈，现网的一些主机通过 ftp 连接时，经常会遇到超时情况，通过手动连接也发现正常响应后，...
一言一句话
-「」
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中