MySQL主从复制中的定时任务EVENT

MySQL: 5.7.25-log
MHA主从集群
开启GTID同步

正常运行时：

1.主从环境中主库创建事件event会自动同步到备库；
2.主event状态ENABLED，备库中状态为 SLAVESIDE_DISABLED；
3.event触发的所有操作均会记录binlog，备库通过binlog进行同步；

主从切换后：

1.新主库(原备库)不会启动event执行，需要执行命令 alter event e_xxxx ENABLE;来启动事件；(启动前确保参数event_scheduler=ON，可通过set global event_scheduler=1 ; 开启)
2.新备库(原宕机的主库)启动时需要注意不能开启event，可在my.cnf文件中添加参数 event_scheduler=OFF（8.0是默认开启的），否则会因为event自己生成的数据和binlog同步的数据发生主键冲突等报错。
3.启动后执行change master设置为从库，开启同步即可。

测试案例：

1.正常同步

## 主库执行
mysql> show VARIABLES like '%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+

mysql> select db,name,interval_value,interval_field,created,last_executed,starts,status from mysql.`event`  where name ='e_insert_tt';
+-------+-------------+----------------+----------------+---------------------+---------------------+---------------------+---------+
| db    | name        | interval_value | interval_field | created             | last_executed       | starts              | status  |
+-------+-------------+----------------+----------------+---------------------+---------------------+---------------------+---------+
| test1 | e_insert_tt |              2 | SECOND         | 2020-08-13 11:19:10 | 2020-08-13 11:44:33 | 2020-08-13 11:19:53 | ENABLED |
+-------+-------------+----------------+----------------+---------------------+---------------------+---------------------+---------+

## 备库执行
mysql> show VARIABLES like '%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+

mysql> select db,name,interval_value,interval_field,created,last_executed,starts,status from mysql.`event`  where name ='e_insert_tt';
+-------+-------------+----------------+----------------+---------------------+---------------+---------------------+--------------------+
| db    | name        | interval_value | interval_field | created             | last_executed | starts              | status             |
+-------+-------------+----------------+----------------+---------------------+---------------+---------------------+--------------------+
| test1 | e_insert_tt |              2 | SECOND         | 2020-08-13 11:19:10 | NULL          | 2020-08-13 11:19:53 | SLAVESIDE_DISABLED |
+-------+-------------+----------------+----------------+---------------------+---------------+---------------------+--------------------+

2.发生切换后

##在新主库(原备库)上执行
查看参数：结果同上面的备库，未开启 event_scheduler 且 event状态为SLAVESIDE_DISABLED
mysql> show VARIABLES like '%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+

mysql> select status from mysql.`event`  where name ='e_insert_tt';
+--------------------+
| status             |
+--------------------+
| SLAVESIDE_DISABLED |
+--------------------+


启动event:
set global event_scheduler=1 ; 
alter event e_insert_tt  ENABLE ;

查询任务执行情况，一切正常。

3.启动新备库(原宕机的主库)

##在新备库(原宕机的主库)上执行
先在my.cnf中添加参数 event_scheduler=OFF

启动mysql，service mysqld start

查看参数：
mysql> show VARIABLES like '%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+

mysql> select status from mysql.`event`  where name ='e_insert_tt';
+--------------------+
| status             |
+--------------------+
| SLAVESIDE_DISABLED |
+--------------------+

启动同步：
CHANGE MASTER TO MASTER_HOST='192.xx.xx.xx',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_PORT=3306,MASTER_AUTO_POSITION = 1;
start slave;

查看同步状态：
show slave status \G

检验event涉及到的表数据是否同步一致。

一般情况下，我们是建议禁止使用event的。