mysql scheduler 사용하기

데이터베이스에서 정기작업 ( repeat작업) 은 항상 필요하고, 개발자를 괴롭힌다.

mysql을 사용하는 경우 아래와 같이 사용하는 방법이 있으니, 참고 바랍니다.

http://dev.mysql.com/doc/refman/5.1/en/events-configuration.html

Mysql Event Scheduler  시작하기.

스켤쥴러 시작하기

SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;

아래는 간단한 데모입니다.  1 초 간격으로 1분동안 insert 1건을 실행시킵니다. 결국 60건이 들어 갈것 같습니다.

$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.44 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

mysql> use test;
Database changed

mysql> select * from mulder01;
Empty set (0.00 sec)

mysql> SET GLOBAL event_scheduler = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@global.event_scheduler = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL event_scheduler = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@global.event_scheduler = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> desc mulder01;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| xname | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> 


mysql> INSERT INTO mulder01 (xname) values ('hi')' at line 5

mysql> CREATE EVENT IF NOT EXISTS EVENT_001
    -> ON SCHEDULE
    ->     EVERY 1 SECOND
    ->     STARTS CURRENT_TIMESTAMP
    ->     ENDS DATE_ADD( CURRENT_TIMESTAMP, INTERVAL 1 MINUTE)
    -> DO
    ->     INSERT INTO mulder01 (xname) values ('hi');
Query OK, 0 rows affected (0.01 sec)

mysql> 

mysql> select * from mulder01;
+-------+
| xname |
+-------+
| hi    |
| hi    |
| hi    |
| hi    |
| hi    |
| hi    |
| hi    |
| hi    |
| hi    |
+-------+
61 rows in set (0.00 sec)

mysql> 

스케쥴러를 중지 시키기.

SET GLOBAL event_scheduler = OFF;
SET @@global.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@global.event_scheduler = 0;

1