Percona XtraDB Cluster 5.7 event是否重复执行？
最近的某个业务系统即将由单点转入PXC集群，碰到的问题是mysql单实例上运行的那些event，再转入集群之后，该如何执行呢？带着这个问题，做了个实验，并给出相关结论。如果看官有类似的疑惑不妨往下读。 一、环境描述 # more /etc/redhat-release CentOS Linux release 7.4.1708 (Core) mysql> show variables like 'version'; +---------------+------------------+ | Variable_name | Value | +---------------+------------------+ | version | 5.7.20-18-57-log | +---------------+------------------+ mysql> show status like '%wsrep_cluster_size%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+ 二、PXC环境测试event 测试说明 该测试在一个节点上创建event，然后观察其他节点上event是否被执行。那如何判断event是在哪个节点上执行的呢？在这里写一个存储过程来调度使用当前实例的ip地址进行往表写入操作。因此如果任意一节点写入，必定包含自身ip。因此，我们调用了美每个实例上的wsrep_node_address这个变量。 1、在其中的一个节点创建event mysql> show variables like '%wsrep_node_address%'; +--------------------+---------------+ | Variable_name | Value | +--------------------+---------------+ | wsrep_node_address | 192.168.1.248 | +--------------------+---------------+ --当前节点为1.248，变量event_scheduler处于开启 mysql> show variables like '%event_scheduler%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | ON | +-----------------+-------+ --建一个测试库 mysql> create database tempdb; --创建过程 mysql> delimiter // mysql> CREATE PROCEDURE `inst_rows` () BEGIN DECLARE v1 INT DEFAULT 500; WHILE v1 > 0 DO SET @ipaddr = (SELECT VARIABLE_VALUE FROM `performance_schema`.session_variables WHERE variable_name = 'wsrep_node_address'); INSERT INTO tempdb.tb_test(ip_addr, ins_time) VALUES (@ipaddr, now()); SET v1 = v1 - 1; SELECT sleep(1); END WHILE; END // mysql> delimiter ; --创建测试表 mysql> CREATE TABLE tb_test ( id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, ip_addr varchar(30) DEFAULT NULL, ins_time timestamp ); mysql> select now(); +---------------------+ | now() | +---------------------+ | 2018-04-27 17:46:27 | +---------------------+ --添加event mysql> CREATE EVENT test_event ON SCHEDULE AT '2018-04-27 18:05:00' ON COMPLETION PRESERVE DO CALL inst_rows ; mysql> select name,status from mysql.`event` where db='tempdb'; +------------+---------+ | name | status | +------------+---------+ | test_event | ENABLED | +------------+---------+ #####2、其他节点观察event mysql> show variables like '%wsrep_node_address%'; +--------------------+---------------+ | Variable_name | Value | +--------------------+---------------+ | wsrep_node_address | 192.168.1.249 | +--------------------+---------------+ mysql> show variables like '%event_scheduler%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | ON | +-----------------+-------+ --在节点1.248上，event状态为SLAVESIDE_DISABLED，即不会被执行 mysql> select name,status from mysql.`event` where db='tempdb'; +------------+--------------------+ | name | status | +------------+--------------------+ | test_event | SLAVESIDE_DISABLED | +------------+--------------------+ mysql> select "Leshami" Author, "https://blog.csdn.net/leshami",645746311 "WeiXin/QQ"; +---------+-------------------------------+-----------+ | Author | https://blog.csdn.net/leshami | WeiXin/QQ | +---------+-------------------------------+-----------+ | Leshami | https://blog.csdn.net/leshami | 645746311 | +---------+-------------------------------+-----------+ mysql> select now(); +---------------------+ | now() | +---------------------+ | 2018-04-27 18:07:25 | +---------------------+ 1 row in set (0.00 sec) mysql> select * from tb_test where ip_addr='192.168.1.249'; Empty set (0.00 sec) mysql> select count(*) from tb_test where ip_addr='192.168.1.248'; +----------+ | count(*) | +----------+ | 500 | +----------+ 三、主从架构测试event 在mysql当中，系统变量event_scheduler，来自官方的说明，缺省值为OFF。 This variable indicates the status of the Event Scheduler; possible values are ON, OFF, and DISABLED, with the default being OFF. 如下主从架构 mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.248 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysqlbin.000011 Read_Master_Log_Pos: 382972 Relay_Log_File: zcd05-relay-bin.000010 Relay_Log_Pos: 383183 Relay_Master_Log_File: mysqlbin.000011 Slave_IO_Running: Yes Slave_SQL_Running: Yes mysql> show variables like '%report_host%'; +---------------+---------------+ | Variable_name | Value | +---------------+---------------+ | report_host | 192.168.1.244 | +---------------+---------------+ 1 row in set (0.01 sec) mysql> show variables like 'event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+ 1 row in set (0.01 sec) --从库节点上的event状态也为SLAVESIDE_DISABLED mysql> select name,status from mysql.`event` where db='tempdb'; +------------+--------------------+ | name | status | +------------+--------------------+ | test_event | SLAVESIDE_DISABLED | +------------+--------------------+ 1 row in set (0.00 sec) 四、结论 1、The status of the event is set to SLAVESIDE_DISABLED on the slave regardless of the state specified (this does not apply to DROP EVENT).2、SLAVESIDE_DISABLED indicates that the creation of the event occurred on another MySQL server acting as a replication master and replicated to the current MySQL server which is acting as a slave,but the event is not presently being executed on the slave.3、在PXC集群中，在任意一个节点上创建的event，在其余节点上event状态将处于SLAVESIDE_DISABLED。也就是在哪个节点上创建将被所在节点调度。
