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


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

  1. mysql> show create event `insert`\G;
  2. *************************** 1. row ***************************
  3. Event: insert
  4. sql_mode:
  5. time_zone: SYSTEM
  6. Create Event: CREATE DEFINER=`root`@`localhost` EVENT `insert`
  7. ON SCHEDULE EVERY 1 MINUTE STARTS '2012-11-20 16:10:09'
  8. ON COMPLETION PRESERVE ENABLE DO BEGIN
  9. insert into t3(name) values('aa');
  10. END
  11. character_set_client: utf8
  12. collation_connection: utf8_general_ci
  13. Database Collation: utf8_general_ci
  14. 1 row in set (0.02 sec)
  16. ERROR:
  17. No query specified

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

  1. mysql> show create event `insert`\G;
  2. *************************** 1. row ***************************
  3. Event: insert
  4. sql_mode:
  5. time_zone: SYSTEM
  6. Create Event: CREATE DEFINER=`root`@`localhost` EVENT `insert`
  7. ON SCHEDULE EVERY 1 MINUTE STARTS '2012-11-20 16:10:09'
  8. ON COMPLETION PRESERVE DISABLE ON SLAVE DO BEGIN
  9. insert into t3(name) values('aa');
  10. END
  11. character_set_client: utf8
  12. collation_connection: utf8_general_ci
  13. Database Collation: utf8_general_ci
  14. 1 row in set (0.02 sec)
  16. ERROR:
  17. No query specified

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

在master上

  1. mysql> show events;
  2. +------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
  3. | Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
  4. +------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
  5. | 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 |
  6. +------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
  7. 1 row in set (0.11 sec)

在slave上

  1. mysql> show events;
  2. +------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+--------------------+------------+----------------------+----------------------+--------------------+
  3. | Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
  4. +------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+--------------------+------------+----------------------+----------------------+--------------------+
  5. | 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 |
  6. +------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+--------------------+------------+----------------------+----------------------+--------------------+
  7. 1 row in set (0.10 sec)

也就是说，事件只能在master触发，slave上不会触发，否则如果slave上触发了，同步复制就会坏掉。

当主从故障切换之后，VIP漂移到了以前的slave上，此时slave成了新的master。

但这时，事件的状态还是维持SLAVESIDE_DISABLED，并不是也改成了ENABLED，这样就会造成切换以后，事件无法执行。

所以，需要人工重新开启事件状态。

  1. mysql> alter event `insert` enable;
  2. Query OK, 0 rows affected (0.05 sec)


参考手册：

相关内容