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)

参考手册：

转载请保留固定链接： https://linuxeye.com/database/952.html

热门标签:

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