本文共 5722 字，大约阅读时间需要 19 分钟。
最近的某个业务系统即将由单点转入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 |+--------------------+-------+
测试说明
该测试在一个节点上创建event，然后观察其他节点上event是否被执行。那如何判断event是在哪个节点上执行的呢？在这里写一个存储过程来调度使用当前实例的ip地址进行往表写入操作。因此如果任意一节点写入，必定包含自身ip。因此，我们调用了美每个实例上的wsrep_node_address这个变量。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 |+---------------------+--添加eventmysql> CREATE EVENT test_eventON SCHEDULE AT '2018-04-27 18:05:00' ON COMPLETION PRESERVEDO CALL inst_rows ;mysql> select name,status from mysql.`event` where db='tempdb';+------------+---------+| name | status |+------------+---------+| test_event | ENABLED |+------------+---------+#####2、其他节点观察eventmysql> 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 |+----------+
在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: Yesmysql> 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_DISABLEDmysql> 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。也就是在哪个节点上创建将被所在节点调度。