MySQL故障切换之事件调度器（event）注意事项

在主从架构中，在master创建一个event，如下：

mysql> show create event `insert`G;
*************************** 1. row ***************************
Event: insert
sql_mode:
time_zone: SYSTEM
Create Event: CREATE DEFINER=`root`@`localhost` EVENT `insert`
ON SCHEDULE EVERY 1 MINUTE STARTS "2012-11-20 16:10:09"
ON COMPLETION PRESERVE ENABLE DO BEGIN
insert into t3（name） values（"aa"）;
END
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set （0.02 sec）
ERROR:
No query specified

slave同步过去，结果是这样的，注意红色字体：

mysql> show create event `insert`G;
*************************** 1. row ***************************
Event: insert
sql_mode:
time_zone: SYSTEM
Create Event: CREATE DEFINER=`root`@`localhost` EVENT `insert`
ON SCHEDULE EVERY 1 MINUTE STARTS "2012-11-20 16:10:09"
ON COMPLETION PRESERVE DISABLE ON SLAVE DO BEGIN
insert into t3（name） values（"aa"）;
END
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set （0.02 sec）
ERROR:
No query specified

再回过头来，看下事件状态，注意红色字体：在master上

mysql> show events;
+------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer| Time zone | Type| Execute at | Interval value | Interval field | Starts| Ends | Status| Originator | character_set_client | collation_connection | Database Collation |
+------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| test | insert | root@localhost | SYSTEM| RECURRING | NULL | 1| MINUTE | 2012-11-20 16:10:09 | NULL | ENABLED | 25 | utf8 | utf8_general_ci| utf8_general_ci|
+------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set （0.11 sec）

在slave上

mysql> show events;
+------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+--------------------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer| Time zone | Type| Execute at | Interval value | Interval field | Starts| Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+--------------------+------------+----------------------+----------------------+--------------------+
| test | insert | root@localhost | SYSTEM| RECURRING | NULL | 1| MINUTE | 2012-11-20 16:10:09 | NULL | SLAVESIDE_DISABLED | 25 | utf8 | utf8_general_ci| utf8_general_ci|
+------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+--------------------+------------+----------------------+----------------------+--------------------+
1 row in set （0.10 sec）

也就是说，事件只能在master触发，slave上不会触发，否则如果slave上触发了，同步复制就会坏掉。当主从故障切换之后，VIP漂移到了以前的slave上，此时slave成了新的master。但这时，事件的状态还是维持SLAVESIDE_DISABLED，并不是也改成了ENABLED，这样就会造成切换以后，事件无法执行。所以，需要人工重新开启事件状态。

mysql> alter event `insert` enable;
Query OK, 0 rows affected （0.05 sec）

参考手册：利用MySQL触发器高性能造数据Oracle 存储过程中的细节-日期处理相关资讯 MySQL基础教程 MySQL event

MySQL event的简单使用（05月07日）
MySQL event实现定时建表小记（09/14/2015 13:32:50）
高性能MySQL（第3版）中文PDF带目（10/26/2014 10:03:50）

MySQL基础教程：关于varchar（N）（01月22日）
对MySQL event的相关理解（07/31/2015 15:24:12）
MySQL SELECT同时UPDATE同一张表（02/19/2013 07:20:18）

本文评论查看全部评论（0）