메뉴 건너뛰기

XEDITION

MYSQL

mysql 이벤트 스케줄 사용법

엘리후 2016.02.20 14:56 조회 수 : 1866

출처 : http://blog.daum.net/osban/17147075

 

 

 

Mysql 5.1.17 에서부터는 Event 란 기능을 어느정도 이용할수 있다고 한다.
Event 란 특정 시간에 어떠한 쿼리 프로시저 함수등을 실행 시키는것을 말한다.
우선 root 권한으로 작업을 한다는 가정하에 적는다.

Event 를 사용하기위해서는
1. 서버가 동작중이라면 아래와 같은 SQL문을 통해 실행할수 있다.
    SET GLOBAL event_scheduler = ON;
    SET @@global.event_scheduler = ON;
    SET GLOBAL event_scheduler = 1;
    SET @@global.event_scheduler = 1;
2. my.cnf or my.ini 에 옵션(event_scheduler=On)을 추가하고 mysql 을 시작한다.

반대로 Event 를 사용하지 않으려면
1. 서버가 동작중이라면 아래와 같은 SQL문을 통해 종료할수 있다.

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

2. my.cnf or my.ini 에 옵션(event_scheduler=Off)을 추가하고 mysql 을 시작한다.

그럼 실제 event 를 만들어 보자.
우선 아래와 같이 test DB에 테이블을 하나 만들어보자

CREATE TABLE `e` (
  `num` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `regdate` datetime NOT NULL,
  PRIMARY KEY (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;



그리고 이벤트를 하나 만들어 보자
아래의 SQL문은 test.e 테이블에 현재시간으로 부터 30초마다 insert 를 실행하는
event 를 생성하는 것이다.

create event IF NOT EXISTS e1
    ON SCHEDULE
        EVERY 30 SECOND
        STARTS CURRENT_TIMESTAMP
    DO insert into test.e set regdate=now();

몇분지나다 보면 test.e 테이블에 데이터가 입력되는것을 확인할수 있을것이다.

EVERY 30 SECOND 의 의미가 30초씩 루프를 돈다는 의미이며 STARTS CURRENT_TIMESTAMP 가
현재 시간부터 시작한다는 의미이다. SECOND 외에도 아래와 같은 옵션으로 올수 있다.
YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND
EVERY 2 MINUTE => 2분마다 DO 구문을 실행
EVERY 1 HOUR => 매일 실행
...

이렇게 입력한 Event 는 mysql.event 테이블에 등록이 된다.
또는 show events; 를 통해 확인할수도 있다. 그외로 information_schema.EVENTS 에서도 확인할수 있다.
mysql.event 를 확인하다보면 created modified 시간이 정확하지만
last_excuted starts ends 는 뭔가 좀 다르다는걸 알수 있다. 이는 그리니치 표준시각을 의미한다.

이런 Event 를 삭제하기 위해서는 아래와 같은 SQL 문을 실행하면 된다.
drop event 이벤트명;
즉 위의 e1 이라는 event 를 삭제하기 위해서는 drop event e1; 인 SQL문을 실행하면 된다.
event는 mysql.event 테이블에 등록된다고 했는데 mysql.event 에서 바로 삭제하는경우
메모리의 영향때문인지 삭제후 똑같은 event 명으로 등록을 한경우 앞전에 사용한 event 가 제대로 지워지지
않고 동작하는 경우를 볼수 있다. 그러니 삭제시는 꼭 drop event 이벤트명; 을 이용하길 바란다.

여하튼 위에서 생성한 e1 쿼리를 좀더 발전시켜 아래와 같이 작성해보자

create event IF NOT EXISTS e2
   ON SCHEDULE 
       EVERY 1 MINUTE
       STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
       ENDS CURRENT_TIMESTAMP + INTERVAL 5 MINUTE
   DO insert into test.e set regdate=now();

위의 SQL문과 다른점은 ENDS 가 지정되고 + INTERVAL 1 MINUTE 같은 문구가 지정된것이다.
이는 현재시간으로 부터 1 분뒤 부터 Event 가 실행되고 1분마다 do 의 sql 문이 실행되며
현재시간으로부터 5분뒤에 멈춘다는 것이다. 그러니 총 5번의 실행후 멈추게 된다.
예를 들면 13:00:00 이 현재 시간이라고 한다면 13:01:00 부터 이벤트가 실행되며 13:05:00 까지 insert 가
일어나고 동시에 13:05:00 에 이벤트가 종료된다.
이런경우 즉 Ends 가 있는 경우는 event 가 종료되고 mysql.event 테이블에서 해당 event 가 자동삭제된다.

그럼 딱 한번 실행을 위해서는 어떻게 해야 할까?

create event IF NOT EXISTS e2
   ON SCHEDULE 
       EVERY 1 MINUTE
       STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
       ENDS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
   DO insert into test.e set regdate=now();

이렇게 해도 될것이다.
하지만 이는 너무 비효율적이다.

그래서 AT 이라는 지시어가 별도로 있다.
즉 EVERY는 순환을 위해 AT 은 한번 사용을 위해 사용된다.
Unux 계열의 at 과 cron 의 의미와 관련지어 생각하면 될듯 하다.
위의 SQL문은 아래와 같이 수정할수 있다.

create event IF NOT EXISTS e2
   ON SCHEDULE 
       AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
    DO insert into test.e set regdate=now();

이 경우에도 Ends 가 붙은것처럼 한번실행하고 나면 mysql.event 에서 삭제된다.
즉 종료 조건이 있는 경우 자동삭제된다고 보면 된다.

물론 아래와 같이 ON COMPLETION PRESERVE 구문을 추가하면 이부분 역시 남겨둘수 있다.

create event IF NOT EXISTS e2
   ON SCHEDULE 
       AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
        ON COMPLETION PRESERVE
   DO insert into test.e set regdate=now();



Event 는 위의 경우처럼 do 에서 insert 외에도 다양한 구문을 지원한다.
아래는 mysql 에서 제공하는 예제이다.
mysql 프로시저 등을 공부해보신 분이라면 금방이해가 갈듯...

DELIMITER |

CREATE EVENT e
    ON SCHEDULE 
      EVERY 5 SECOND
    DO
      BEGIN
        DECLARE v INTEGER;
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;

        SET v = 0;

        WHILE v < 5 DO
          INSERT INTO t1 VALUES (0);
          UPDATE t2 SET s1 = s1 + 1;
          SET v = v + 1;
        END WHILE;
    END |

DELIMITER ;

CREATE EVENT e_call_myproc
    ON SCHEDULE 
      AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
    DO CALL myproc(5, 27); 
번호 제목 글쓴이 날짜 조회 수
55 MySQL 데이터 복구하기 엘리후 2016.10.06 85
54 플래시로 DB테이블 연동 기초~ 엘리후 2016.09.07 62
53 [문제해결 일지] systemctl start mariadb 실행시 Failed to issue method call: No such file or directory. 에러 엘리후 2016.05.10 83
52 알기 쉬운 게임DB 이야기 엘리후 2016.02.28 248
» mysql 이벤트 스케줄 사용법 엘리후 2016.02.20 1866
50 MySql 문자열 합치기 엘리후 2016.02.19 239
49 MySQL Insert Where query 엘리후 2016.02.19 171
48 [MySQL]이벤트 스케줄러를 사용해서 데이터 삭제하기 엘리후 2016.02.02 215
47 mysql 이벤트 스케쥴러 사용하기 엘리후 2016.02.02 85
46 SQL FULL OUTER JOIN Keyword 엘리후 2016.01.28 132
45 mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead… 엘리후 2015.12.01 67
44 mysql 명령어로 접속시도시 발생되는 오류 엘리후 2015.12.01 328
43 MYSQL: AND & OR CONDITIONS 엘리후 2015.11.26 32
42 [MY-SQL] 현재(오늘)부터 특정일 까지를 구하는 Query문(between, subdate) + 오늘 기준 이번주, 이번달 구하는 Query문 엘리후 2015.11.20 201
41 mysql에서 가장 오래된 row select 쿼리는? 엘리후 2015.11.19 158
40 MySQL 함수를 활용한 날짜비교 쿼리문 작성하기 [출처] MySQL 함수를 활용한 날짜비교 쿼리문 작성하기|작성자 네오 에이치 엘리후 2015.11.18 60
39 MySQL 기본쿼리 : DELETE 문 엘리후 2015.11.02 45
38 [서버관리] phpMyAdmin을 통한 DB 및 사용자추가 엘리후 2015.10.28 116
37 Ubuntu MySQL 원격 접속 허용 엘리후 2015.10.22 56
36 [mysql] mysql 중복 제거 쿼리 (dist, group by) 엘리후 2015.10.21 137
위로