MySQL 에서 event_scheduler=ON 으로 설정하면 event scheduler를 생성하고 사용할 수 있습니다.
n Slave 구성시 주의 사항
slave를 구성할 때 보통 master에 모든 스키마를 생성하고 full backup을 받아서
이것을 slave의 초기 db로 사용합니다.
그런데 이렇게 되면 slave 에서도 ( event_scheduler=ON으로 되어 있다고 할 때 )
event 의 status 가 아래와 같이 ENABLED 로 되어 있어서 스케쥴 설정에 따라 event가 작동을 합니다.
이렇게 되면 master에서 발생한 event 로 인한 변경 내역도 replication이 되고 slave에서는 자체적으로 동일한 event가
시간에 맞춰 실행이 됩니다. 이러면서 consistency가 깨지고 replication이 깨지게 됩니다.
이 때는 slave 에서 아래와 같이 상태를 확인하고
alter event event_name disable on slave;
명령을 통해서 상태를 변경해주어야 합니다.
그런데 replication 세팅 후에 master에서 create event .... 문으로 생성되어 이 생성문이 slave로 복제 되어 slave에서 생성되게 되면
자동으로 STATUS = SLAVESIDE_DISABLED 로 세팅되게 되어 문제가 없습니다.
그리고 master가 장애가 발생하여 slave를 master로 변경할 때
STATUS = SLAVESIDE_DISABLED ----> ENABLED로 변경을 해주어야 합니다.
어떤 글에는 event 의 상태를 변경해도 바로 event scheduler가 그 상태를 읽어오지 않을 수 있다고 되어 있는데
5.6 에서 확인해 본 결과 상태 변경하면 바로 적용되는 부분 확인하였습니다.
22 rows in set (0.01 sec)
[root@localhost] (rh_gamedb01) 17:26> show events from rh_gamedb01\G
*************************** 1. row ***************************
Db: rh_gamedb01
Name: event_avg_user_score
Definer: rh_svc@%
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: MINUTE
Starts: 2013-07-23 17:00:00
Ends: NULL
Status: ENABLED
Originator: 13
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
*************************** 2. row ***************************
Db: rh_gamedb01
Name: event_delete_letters
Definer: rh_svc@%
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: DAY
Starts: 2013-08-07 04:00:00
Ends: NULL
Status: ENABLED
Originator: 13
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
*************************** 3. row ***************************
Db: rh_gamedb01
Name: event_week_rank
Definer: rh_svc@%
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: WEEK
Starts: 2013-06-24 05:00:00
Ends: NULL
Status: ENABLED
Originator: 13
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
3 rows in set (0.01 sec)
[root@localhost] (rh_gamedb01) 17:27> alter event event_week_rank status disable on slave;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'status disable on slave' at line 1
[root@localhost] (rh_gamedb01) 17:28> alter event event_week_rank disable on slave;
Query OK, 0 rows affected (0.00 sec)
[root@localhost] (rh_gamedb01) 17:28> alter event event_delete_letters disable on slave;
Query OK, 0 rows affected (0.01 sec)
[root@localhost] (rh_gamedb01) 17:29> alter event event_avg_user_score disable on slave;
Query OK, 0 rows affected (0.01 sec)
[root@localhost] (rh_gamedb01) 17:29> show events from rh_gamedb01\G
*************************** 1. row ***************************
Db: rh_gamedb01
Name: event_avg_user_score
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: MINUTE
Starts: 2013-07-23 17:00:00
Ends: NULL
Status: SLAVESIDE_DISABLED
Originator: 17
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
*************************** 2. row ***************************
Db: rh_gamedb01
Name: event_delete_letters
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: DAY
Starts: 2013-08-07 04:00:00
Ends: NULL
Status: SLAVESIDE_DISABLED
Originator: 17
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
*************************** 3. row ***************************
Db: rh_gamedb01
Name: event_week_rank
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: WEEK
Starts: 2013-06-24 05:00:00
Ends: NULL
Status: SLAVESIDE_DISABLED
Originator: 17
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
3 rows in set (0.01 sec)
[root@localhost] (mysql) 17:34> select name ,status from event;
+----------------------+--------------------+
| name | status |
+----------------------+--------------------+
| event_avg_user_score | SLAVESIDE_DISABLED |
| event_delete_letters | SLAVESIDE_DISABLED |
| event_week_rank | SLAVESIDE_DISABLED |
+----------------------+--------------------+
3 rows in set (0.00 sec)
# MASTER 에서 event 생성시 slave 에서의 상태 확인
1) MASTER 에서 생성
DELIMITER //
create event test_event_scheduler on SCHEDULE EVERY 1 MINUTE STARTS '2013-11-01 19:00:00'
DO
BEGIN
update test_event set a= a+10 ;
END//
DELIMITER ;
alter event test_event_scheduler disable;
[root@localhost] (test) 18:38> show events from test;
-> ;
-> ;
-> //
+------+----------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+------+----------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| test | test_event_scheduler | root@localhost | SYSTEM | RECURRING | NULL | 1 | MINUTE | 2013-11-01 18:40:00 | NULL | ENABLED | 13 | utf8 | utf8_general_ci | utf8_general_ci |
+------+----------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
2) SLAVE 에서 확인
[root@localhost] (test) 18:40> show events from test;
+------+----------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+--------------------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+------+----------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+--------------------+------------+----------------------+----------------------+--------------------+
| test | test_event_scheduler | root@localhost | SYSTEM | RECURRING | NULL | 1 | MINUTE | 2013-11-01 18:40:00 | NULL | SLAVESIDE_DISABLED | 13 | utf8 | utf8_general_ci | utf8_general_ci |
+------+----------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+--------------------+------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
덧글|덧글 쓰기|신고