默认情况下,master创建event,slave同步后默认是disable的:
1.在master创建event:
create event event_master
on schedule at current_timestamp + interval 1 minute
on completion preserve enable
do insert into baofeng(id) values( 1000);
on schedule at current_timestamp + interval 1 minute
on completion preserve enable
do insert into baofeng(id) values( 1000);
--查看master的event状态:
mysql
>
select status
from information_schema.EVENTS
where event_name
=
'event_master';
+ - - - - - - - - - +
| status |
+ - - - - - - - - - +
| ENABLED |
+ - - - - - - - - - +
1 row in set ( 0.00 sec)
+ - - - - - - - - - +
| status |
+ - - - - - - - - - +
| ENABLED |
+ - - - - - - - - - +
1 row in set ( 0.00 sec)
2.查看slave的event语句:
mysql
>
show
create event event_master\
G;
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * *
Event: event_master
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
time_zone: SYSTEM
Create Event: CREATE DEFINER =`root`@`%` EVENT `event_master` ON SCHEDULE AT '2014-06-25 10:12:56' ON COMPLETION PRESERVE DISABLE ON SLAVE DO insert into baofeng(id) values( 1000)
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set ( 0.00 sec)
ERROR:
No query specified
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * *
Event: event_master
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
time_zone: SYSTEM
Create Event: CREATE DEFINER =`root`@`%` EVENT `event_master` ON SCHEDULE AT '2014-06-25 10:12:56' ON COMPLETION PRESERVE DISABLE ON SLAVE DO insert into baofeng(id) values( 1000)
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set ( 0.00 sec)
ERROR:
No query specified
--查看slave的event状态:
mysql
>
select status
from information_schema.EVENTS
where event_name
=
'event_master';
+ - - - - - - - - - - - - - - - - - - - - +
| status |
+ - - - - - - - - - - - - - - - - - - - - +
| SLAVESIDE_DISABLED |
+ - - - - - - - - - - - - - - - - - - - - +
1 row in set ( 0.00 sec)
+ - - - - - - - - - - - - - - - - - - - - +
| status |
+ - - - - - - - - - - - - - - - - - - - - +
| SLAVESIDE_DISABLED |
+ - - - - - - - - - - - - - - - - - - - - +
1 row in set ( 0.00 sec)
可见当默认的情况下,在slave的event并不会被enable
主从切换:
如果主库切换到从库,这个时候我们需要显式的执行alter将该event启动
alter event event_master enable;
这个时候有个问题,若主库恢复正常后,需要加到从库中,但这个时候的event的状态是“enable”,如果这时启动“master主库”,则event会自动执行,因此我们需要做如下步骤:
1.修改参数文件,将event_scheduler=OFF,启动“主库”。
2.将event disable:
alter event event_master SLAVESIDE_DISABLED;
3.启动event_scheduler(没有特殊需求,这个步骤做不做应该问题都不大):
set event_scheduler
=
ON;
4.修改参数文件,将event_scheduler=ON
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25704976/viewspace-1193199/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25704976/viewspace-1193199/