在主从架构中,在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)
参考手册: