最近的某个业务系统即将由单点转入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。也就是在哪个节点上创建将被所在节点调度。
