Percona XtraDB Cluster 5.7 event是否重复执行？

谁践踏了优雅 2022-05-26 08:18 152阅读 0赞

最近的某个业务系统即将由单点转入PXC集群，碰到的问题是mysql单实例上运行的那些event，再转入集群之后，该如何执行呢？带着这个问题，做了个实验，并给出相关结论。如果看官有类似的疑惑不妨往下读。

### 一、环境描述 ###

### 二、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。也就是在哪个节点上创建将被所在节点调度。