linuxism :: mysql - 이벤트(Event)

mysql - 이벤트(Event)

DB/MySQL 2012. 9. 7. 01:04

이벤트 등록

이벤트 등록을 하기 위해서는 CREATE EVENT문을 사용한다. 구문은 아래와 같다.

CREATE EVENT [IF NOT EXISTS] 이벤트명

ON SCHEDULE 스케줄

[ON COMPLETION [NOT] PRESERVE]

[ENABLE | DISABLE]

[COMMENT '주석']

DO [BEGIN] 실행할 sql문; [실행할 sql문]; [END]

스케줄:

{ AT 타임 [+ INTERVAL 간격 [+INTERVAL 간격...]]

| EVERY 간격 [STARTS 타임] [ENDS 타임] }

타임:

{CURRENT_TIMESTAMP | 년월일시의 리터럴}

간격:

수 {YEAR|QUARTER|MONTH|DAY|HOUR|MINUTE|WEEK|SECOND|YEAR_MONTH|DAY

|HOUR|MINUTE| WEEK| SECOND | YEAR_MONTH|DAY_HOUR|DAY_MINUTE| DAY_SECOND| HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

ON SCHEDULE구에서는 이벤트의 실행시간과 간격을 지정한다. 이것은 필수이다. DO 구 뒤에는 실행할 SQL문을 지정한다. 이것도 필수이다.

이벤트명은 64문자까지이고 대소문자 구분하지 않는다.

유니크한 이름을 설정해야한다.

CURRENT_TIMESTAMP는 현재의 일시를 나타내는 특별한 키워드이다.

ON SCHEDULE AT timestamp는 한번만 실행하는 이벤트의 경우에 사용한다.

Unix의 at같은 것이라고 생각하면 될 것이다. 여기에 지정하는 timestamp에는 날짜와 시간 모두 포함할 필요가 있다. 예를 들어 2010-06-27 11:01:00 처럼 지정한다.

또, 지정하는 날짜는 미래의 시간이 되지 않으면 안된다.

+INTERVAL은 복수 지정이 가능하다. + INTERVAL 1 WEEK + INTERVAL 4 HOUR처럼 지정한다.

ON SCHEDULE EVERY는 이벤트를 반복실행할 때 사용한다. Unix의 cron이라고 생각하면 될 것이다. EVERY구인 경우는 + INTERVAL은 지정불가능이다.

STARTS에서는 개시일시 ENDS로 종료일시를 지정한다.

또, DO이하에 지정하는 SQL문이 한개 인경우에는 BEGIN, END, DELIMITER 지정은 필요없다.

복수의 문을 지정하는 경우는 BEGIN ~END로 문장을 감싼다. 이때 안에 있는 SQL문은「 ;」로 구별하기 때문에 CREATE EVENT문 끝을 의미하는 「 ;」하고 구별할 수 없게 된다.

그래서 stored procedure와 마찬가지로 CREATE EVENT실행전에 DELIMITER 를 지정해서 문장의 끝을 나타내는 마크를 변경해 두어야한다.

ON COMPLETION PRESERVE는 이벤트가 완료하더라고 이벤트의 내용을 유지한채 두게 된다. 보통은 바로 삭제된다.

⧈이벤트 등록예

mysql> delimiter //

mysql> CREATE EVENT test_event

-> ON SCHEDULE EVERY 1 DAY

-> STARTS ' 2010-06-27 11:01:00'

-> ENABLE

-> DO

-> BEGIN

-> DELETE FROM test.log

-> WHERE test.log.artime < NOW();

-> END //

출처 - http://eager2rdbms.blogspot.kr/2010/06/event-scheduler-3.html

===================================================================================

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);

[출처] [MySQL] Event Scheduler|작성자 쪼렙

===================================================================================

MySQL 5.1버전 부터 Event 가 가능합니다. 이벤트는 특정 시간 마다 정해진 일을 할 수 있도록 이벤트를 지정하는 것입니다. 예를 들어 통계 작업을 할때 매번 DB를 접속을 한 후에 값을 불러와 연산을 하고 그 결과를 DB에 다시 입력하도록 프로그램을 작성하고 그 프로그램을 *nix 의 Crond 에 등록을 하거나 Windows 의 이벤트 스케쥴러에 등록을 했습니다. 하지만 MySQL Event 는 이벤트를 직접 MySQL 내부에서 처리 하도록 할 수 있습니다.

기본 적인 문법에 대해서 보도록 하겠습니다.
CREATE EVENT [이벤트 이름] ON SCHEDULE [스케쥴:시간:간격] [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE] DO [이벤트 구문] END

위와 같이 이벤트를 동작 시킬 수 있습니다. 이벤트는 기본적으로 OFF로 설정이 되어 있습니다. (옵션을 지정하지 않았을 경우 )

SHOW VARIABLES LIKE '%event%'; 를 통해 이벤트 스케쥴러가 활성화가 되어 있는지 확인 을 할 수 있습니다. event_scheduler 의 값이 없거나 OFF로 되어 있으면 전자는 지원하지 않고 , 후자는 이벤트 스케쥴러가 동작을 하지 않고 있는 것입니다.

이벤트 스케쥴러를 우선적으로 활성화를 합니다 (지원하지 않는 버전일 경우에 MySQL 을 5.1 이상 버전으로 업그레이드를 해주세요)

서버가 실행될때 매번 이벤트 스케쥴러를 실행하고자 할때는 my.ini 에 아래 구문을 추가해 주세요
event_scheduler = On

일회성 또는 테스트용으로 사용하시려면 아래와 같이 SET 으로 현재 설정 상태를 변경하시면 됩니다.

위와 같이 설정하시면 이벤트 스케쥴러가 정상적으로 동작을 할 것입니다.

1SETGLOBALevent_scheduler = ON;
2SHOW VARIABLES LIKE'%event%';

위와 같이 설정을 하셨으면 이제 부터 이벤트 스케쥴러 사용법에대에 본격적으로 다루어 보겠습니다.

일단 이벤트를 사용할 사용자에 대한 권한을 지정해야 합니다. root로 사용하는 사례는 별로 없기때문에 MySQL 사용자에 대한 이벤트 사용권한을 주도록 하겠습니다.

이벤트 스케쥴러란 이벤트 즉 정해진 루틴에 따라 시작을 하기때문에 설정 에 대한 옵션을 먼저 보겠습니다.

01-- 이벤트 부여
02-- 사용자 user 에 모든 데이터 베이스와 테이블에 이벤트 권한을 부여
03GRANTEVENT ON*.* TOuser;
04-- 사용자 user 에게 database_name 하위에 모든 테이블에 대한 이벤트 권한을 부여
05GRANTEVENT ONdatabase_name.* TOuser;
06 
07-- 이벤트 취소
08-- 사용자 user에 모든 데이터 베이스에 대한 이벤트 권한을 제거한다
09REVOKEEVENT ON*.* FROMuser;
10-- 사용자 user에 데이터 베이스 database_name 하위에 모든 테이블에 대한 권한을 제거 한다.
11REVOKEEVENT ONdatabase_name.* FROMuser;

CREATE EVENT
이벤트를 직접 생성해 보도록 하겠습니다.

01DROPEVENT IF EXISTS testevent;
02DELIMITER $
03-- 이벤트 testevet 를 생성합니다.
04CREATEEVENT testevent
05-- 지금 현재 시간(CURRENT_TIMESTAMP) 후(+) 1일(INTERVAL 1 DAY) 이 지나면 수행합니다.
06ONSCHEDULE ATCURRENT_TIMESTAMP+ INTERVAL 1 DAY
07-- 이벤트가 완료된(수행) 후 이벤트를 보전합니다. 
08ONCOMPLETION PRESERVE
09DO
10BEGIN
11    -- 이벤트 구문을 시작합니다.
12    INSERTINTOTriggerTest (`seq`, `name`) VALUES(null, 'This is not a reale news');
13    -- 이 구문은 특정 테이블에 1일에 한번씩 ROW를 삽입합니다.
14END$
15DELIMITER ;
16-- 위 생성된 이벤트는 1회성 이벤트로 1번 수행 후 다시 수행하지 않습니다
17-- 이벤트가 종료가 되면 해당 이벤트는 information_schema.EVENTS 에서 삭제가 됩니다.
18-- 하지만 ON COMPLETION PRESERVE 를 추가하면 이벤트가 보전이 됩니다.
19 
20 
21DROPEVENT IF EXISTS testevent;
22DELIMITER $
23-- 이벤트 testevet 를 생성합니다.
24CREATEEVENT testevent
25-- 지금 부터 2일에 한번씩 이벤트를 수행합니다.
26ONSCHEDULE EVERY 2 DAY
27 
28-- 반복적 이벤트는 아래와 같은 시간 단위를 사용할 수 있습니다. 
29-- YEAR : 년
30-- QUARTER : 분기
31-- MONTH : 월
32-- WEEK : 주
33-- DAY : 일
34-- HOUR : 시간
35-- MINUTE : 분
36-- SECOND : 초
37-- 등을 사용 할 수 있습니다. 자세한 INTERVAL 구문은 아래 다시 다루도록 하겠습니다.
38 
39DO
40BEGIN
41    -- 동작할 이벤트 구문을 넣습니다.
42END$
43DELIMITER ;

이벤트를 지정할 때 사용되는 (+/-) INTERVAL [값] [단위] 표현에 대해서 알아보겠습니다. 이벤트를 지정할 때는 지금 시점 부터 또는 특정 시점부터 언제까지 또는 얼마만에 동작을 하겠다는 표현을 합니다. 그 표현은 아래 표에서 참고 하시면 됩니다.

단위	사용 방법
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MICROSECOND	'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_MICROSECOND	'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'

date + INTERVAL [시간표기] 단위
date - INTERVAL [시간표기] 단위
위 와 같은 방법으로 사용합니다. 예를 들어 1시간 후면 INTERVAL 1 HOUR 가 됩니다.
그럼 언제 부터 언제 까지 이벤트를 수행하려면 어떻게 할까요?
아래와 같이 하면 됩니다

01DELIMITER $
02CREATEEVENT testevent
03ONSCHEDULE
04-- 1분마다 이벤트 실행
05EVERY 1 MINUTE
06-- 이벤트 시작시간은 지금 시간으로 부터 1시간 후 부터
07STARTS CURRENT_TIMESTAMP+ INTERVAL 1 HOUR
08-- 종료 시간은 지금 시간 부터 2시간 후에 종료 한다.
09ENDS CURRENT_TIMESTAMP+ INTERVAL 2 HOUR
10DO
11 
12BEGIN
13    -- 수행할 구문
14    INSERTINTOtesttable VALUES(1,2,3,4);
15END$
16DELIMITER ;

이벤트 BEGIN 과 END 안에 사용하고 자 하는 SQL구문은 모두 가능합니다. 프로시져를 호출 할 수도 있고 , FUNCTION을 사용할 수 있습니다. ^^

좀더 SQL생활이 윤택해졌나요? ㅋㅋ

출처 - http://www.lovelgw.com/Blog/209

===================================================================================

데이터를 주기적으로 삭제 하는 방법을 찾아 보다가 MySQL이벤트 스케줄러라는걸 알게 되었습니다.

이번 시간에는 MySQL이벤트 스케줄러 를 사용해서 데이터를 주기적으로 삭제하는 방법에대해서 알아보도록 하겠습니다.

스케줄러 사용방법은 다음과 같습니다.

1. 커맨드 창에서 MySQL에 접속합니다.

2. 이벤트 스케줄러 활성화 시킵니다.

3. 이벤트를 등록합니다.

그럼 위와 같은 순서대로 진행 해 보겠습니다.

1. 커맨드 창에서 MySQL에 접속합니다.

1-1. 커맨드 창을 연다음 mysql -u아이디 -p패스워드 을 입력해서 MySQL에 접속합니다.

2. 이벤트 스케줄러 활성화 시킵니다.

2-1. 아래의 명령어를 입력합니다.

SET GLOBAL event_scheduler = ON;

SET @@global.event_scheduler = ON;

SET GLOBAL event_scheduler = 1;

SET @@global.event_scheduler = 1;

[실행결과]

[참고]:http://dev.mysql.com/doc/refman/5.1/en/events-configuration.html

3. 이벤트를 등록합니다.

3-1. 이벤트 등록 형식

CREATE

[DEFINER = { user | CURRENT_USER }]

EVENT

[IF NOT EXISTS]

event_name

ON SCHEDULE schedule

[ON COMPLETION [NOT] PRESERVE]

[ENABLE | DISABLE | DISABLE ON SLAVE]

[COMMENT 'comment']

DO event_body;

schedule:

AT timestamp [+ INTERVAL interval] ...

| EVERY interval

[STARTS timestamp [+ INTERVAL interval] ...]

[ENDS timestamp [+ INTERVAL interval] ...]

interval:

quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |

DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

3-2. 예) 한달 간격으로 오늘을 기준으로해서 한달 이후의 데이터를 삭제하는 샘플

CREATE EVENT IF NOT EXISTS 이벤트 이름

ON SCHEDULE

EVERY 1 MONTH

STARTS CURRENT_TIMESTAMP

DELETE FROM db명.테이블명 where 시간컬럼명 <= date_sub(curdate(), INTERVAL 1 MONTH);

※주의: 한글은 임의로 쓴것입니다. 각자 상황에 맞게 바꿔서 쓰시길 바랍니다.

실행은 sql커맨드에서 실행합니다.

예)

mysql>use 데이터베이스명

Database changed

mysql>CREATE EVENT IF NOT EXISTS 이벤트 이름

ON SCHEDULE

EVERY 1 MONTH

STARTS CURRENT_TIMESTAMP

DELETE FROM db명.테이블명 where 시간컬럼명 <= date_sub(curdate(), INTERVAL 1 MONTH);

[참고]http://dev.mysql.com/doc/refman/5.1/en/create-event.html

자세한 내용은 참고 사이트에 가보시면 설명이 되어있습니다.

사이트를 참고하세요.

감사합니다.

출처 - http://forum.falinux.com/zbxe/?document_srl=565440&mid=lecture_tip&page=1

===================================================================================

1. MySQL Event

이벤트는 스케쥴에 따라 작업을 수행한다. ORACLE 의 ‘Job’ 이나 ‘Scheduler’ 와 동일한 기능을 한다.

기본적으로 Unix 의 crontab (cron job 으로 알려짐) 이나 Windows 의 Task Scheduler 와도 비슷하다.

MySQL Event 는 다음과 같은 특징을 가진다.

l MySQL Event 는 name 과 schema 로 구분된다.

l events 의 수행방법은 one-time (일회성) 이나 recurrent (주기적인) 가 될 수 있다.
recurrent 의 기본셋팅은 create 시점에 바로 수행되며, disabled 나 drop 되기 전까지 반복수행된다.

l 반복되는 event 를 interval 안에 종료하지 않으면, 동시수행되는 결과가 생길 수 있다.
이를 회피하기 위한 방안으로 GET_LOCK() function 을 사용할 수 있다.

l event 의 default definer 는 event 가 변경되지 않은 한 (alterd) 생성한 유저이다. alterd event 의
경우 definer 는 alter 구문을 수행한 유저이다.

2. Event Scheduler Configuration

Event 는 ‘event scheduler thread’ 에 의해 수행된다. event scheduler 의 thread 와 현재상태는 ‘event’ 권한을 가진 유저의‘show processlist’ 결과로 알 수 있다.

Global ‘event_scheduler’ system variable 은 event scheduler 의 사용가능 여부를 결정한다. 이 값은 3가지 값을 가진다.

l OFF – Event Scheduler 는 정지되어 있음. show processlist 결과를 볼 수 없다. event_scheduler 의 기본 값이다.

l ON – Event Scheduler 가 시작되어 있음.

l DISABLED – Event Scheduler 가 수행 불가능함을 의미. 이 상태는 runtime 에 변경하지 못한다.

event scheduler 의 비활성화 (disabled) 는 다음과 같은 방법을 사용할 수 있다.

l Starting option : --event-scheduler=DISABLED

l my.cnf : in a [mysqld] section : event_scheduler=DISABLED

event_scheduler 의 상태는 ON | OFF | 1 (ON) | OFF (0) 과 같은 Value 로도 컨트롤 할 수 있다.

3. Event Syntax

A. CREATE EVENT

<one-time style>

create event myevent1

on schedule at ‘2011-01-10 15:00:00’

do insert into test.totalse values(‘myevent1’,now());

CREATE EVENT myevent2

on schedule at current_timestamp + interval 1 hour

do insert into test.totalse values(‘myevent2’,now());

CREATE EVENT myevent3

on schedule every 1 hour

do insert into test.totalse values(‘myevent3’,now());

B. ALTER EVENT

alter event myevent 3

on schedule every 2 hour starts current_timestamp + interval 4 hour;

alter event myevent3 disable;

alter event myevent3 rename to myevent5;

alter event a.myevent3 rename to b.myevent3;

C. DROP EVENT

DROP EVENT [IF EXISTS] EVENT_NAME

4. Event Metadata

Event 의 Metadata 를 확인하는 방법은 다음과 같다.

l mysql.event 를 조회

l information_schema.events 를 조회

l show create events 의 사용

l show events 를 사용

5. The Event Scheduler and MySQL Privilleges

global event_scheduler system variable 을 수정하기 위해서는 super 권한이 필요하다.

event 는 이를 정의한 definer 의 권한을 가지고 수행된다. 때문에 definer 가 필요한 권한이 없는 경우 아무 동작을 하지 못한다.

6. Event 의 사용 (실습)

<event 를 사용할 user 생성>

mysql> grant insert, event on *.* to 'hello'@'localhost' identified by 'hi';

Query OK, 0 rows affected (0.00 sec)

<event 가 사용할 테이블 생성>

mysql> use test_case;

Database changed

mysql> create table test(dt date);

Query OK, 0 rows affected (0.03 sec)

<create event>

[mysql@dg1 mysql]$ ./bin/mysql -uhello –p

mysql> create event myevent

-> on schedule every 1 second

-> do insert into test_case.test values(now());

위 상태에서는 event 가 동작하지 않는다. system variable ‘event_scheduler’ 를 ‘on’ 으로 변경해야 한다.

mysql> set global event_scheduler=on;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_case.test;

+------------+

| dt |

+------------+

| 2012-01-10 |

+------------+

<alter event>

mysql> alter event test_case.myevent disable;

Query OK, 0 rows affected (0.00 sec)

mysql> select name, status from event;

+---------+----------+

| name | status |

+---------+----------+

| myevent | DISABLED |

+---------+----------+

1 row in set (0.00 sec)

<Metadata>

- mysql.event

mysql> select db, name, body from event;

+-----------+---------+------------------------------------------+

| db | name | body |

+-----------+---------+------------------------------------------+

| test_case | myevent | insert into test_case.test values(now()) |

+-----------+---------+------------------------------------------+

1 row in set (0.00 sec)

- information_schema.events

mysql> select event_schema, event_name, event_body, event_definition from events;

+--------------+------------+------------+------------------------------------------+

+--------------+------------+------------+------------------------------------------+

+--------------+------------+------------+------------------------------------------+

1 row in set (0.00 sec)

- show events

mysql> show events\G

*************************** 1. row ***************************

Db: test_case

Name: myevent

Definer: root@localhost

Time zone: SYSTEM

Type: RECURRING

Execute at: NULL

Interval value: 1

Interval field: SECOND

Starts: 2012-01-10 13:49:03

Ends: NULL

Status: DISABLED

Originator: 1

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: latin1_swedish_ci

1 row in set (0.00 sec)

- show create event

mysql> show create event myevent\G

*************************** 1. row ***************************

Event: myevent

sql_mode:

time_zone: SYSTEM

Create Event: CREATE DEFINER=`root`@`localhost` EVENT `myevent` ON SCHEDULE EVERY 1 SECOND STARTS '2012-01-10 13:49:03' ON COMPLETION NOT PRESERVE DISABLE DO insert into test_case.test values(now())

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: latin1_swedish_ci

1 row in set (0.00 sec)

7. Latency in executing event

가정 : 1 (MySQL 이 shutdown 되어 있는 상황)

여기서의 가정사항은 인터벌에 대해 수행 못한 동작들이 누적되지 않을까 하는 것이다.

다시 말해 1분마다 수행이 되는 작업이 있는데 중간에 3분의 shutdown 이 있었다면, MySQL startup 후에

3번의 작업이 한번에 호출되나 하는 것이다.

확인을 위해 1분마다 수행되는 test 테이블에 now() 값을 insert 하는 event 를 만들었다.

추가로 event_scheduler system variable 은 my.cnf 수정을 통해 on 으로 수정한 상태이다.

mysql> select * from test_case.test;

+---------------------+

| dt |

+---------------------+

| 2012-01-10 16:08:48 |

| 2012-01-10 16:09:48 |

| 2012-01-10 16:10:48 |

| 2012-01-10 16:11:48 |

| 2012-01-10 16:19:48 |

| 2012-01-10 16:20:48 |

| 2012-01-10 16:21:48 |

| 2012-01-10 16:22:48 |

| 2012-01-10 16:23:48 |

| 2012-01-10 16:24:48 |

| 2012-01-10 16:25:48 |

| 2012-01-10 16:26:48 |

+---------------------+

12 rows in set (0.00 sec)

결과를 보면 shutdown 이 발생했던 시각에 대해 단순히 event 가 호출되지 않았음을 알 수 있다.

가정 : 2 (Table Lock 경합)

여기서의 가정사항은 MySQL 의 작업을 이루는 SQL 이 Table Lock 경합으로 인해 지연되는 상황이다.

MySQL 은 CTAS, Insert-select 작업 시 CTAS 의 S 의 테이블, INSERT-SELECT 의 SELECT 테이블에 대해 UPDATE, DELETE 하지 못한다.

아래 예제에서는 myevent4 라는 event 를 사용하였다.

이 테이블은 test3 (258만건) 의 데이터를 하나 삭제하고, 타임정보를 test4 에 insert 하는 event 이다.

CTAS 를 이용해 test3 테이블을 test5 로 복제하는 경우 test4 에 기록된 타임정보가 어떻게 되는가를 보겠다.

mysql> set global event_scheduler=on;

Query OK, 0 rows affected (0.00 sec)

mysql> create event myevent4 on schedule every 1 second

-> do

-> begin

-> delete from test3 limit 1;

-> insert into test4 values(now());

-> end|

Query OK, 0 rows affected (0.00 sec)

mysql> create table test5 as select * from test3;

Query OK, 2586579 rows affected (15.90 sec)

Records: 2586579 Duplicates: 0 Warnings: 0

mysql> set global event_scheduler=off;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test4;

+---------------------+

| dt |

+---------------------+

~~ (중략) ~~

| 2012-01-11 10:49:40 |

| 2012-01-11 10:49:57 |

| 2012-01-11 10:49:58 |

~~ (중략) ~~

test4 의 타임정보를 보니 10:49:57 데이터가 중첩되는 것을 알 수 있다.

event 자체를 매초 수행하도록 만들었고, 그 작업 내용도 시간이 안 걸리는 작업이기에 위 데이터의 의미는 작업에 지연이 발생했다는 것이다.

CTAS 를 다시 수행해보면서 다른 세션에서 processlist 를 확인해 보았다.

l CTAS 수행 전…

mysql> show processlist;

+-----+-----------------+-----------+-----------+---------+------+-----------------------------+------------------+

+-----+-----------------+-----------+-----------+---------+------+-----------------------------+------------------+

+-----+-----------------+-----------+-----------+---------+------+-----------------------------+------------------+

3 rows in set (0.00 sec)

l CTAS 수행 중…

mysql> show processlist;

+------+-----------------+-----------+-----------+---------+------+-----------------------------+---------------------------------------+

+------+-----------------+-----------+-----------+---------+------+-----------------------------+---------------------------------------+

+------+-----------------+-----------+-----------+---------+------+-----------------------------+---------------------------------------+

14 rows in set (0.00 sec)

event 가 수행한 sql (delete) 이 모두 대기중이며 Time 정보를 보면 해당 동작이 매초 수행되었음을 알 수 있다.

8. Cautions

table 을 지워도 이것과 연관된 event 는 삭제되지 않고 그대로 남아있다.

추가로 status 가 disable 되지도 않는다.

mysql> show tables;

Empty set (0.00 sec)

mysql> show events;

+-----------+----------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+

4 rows in set (0.00 sec)

9. References

A. MySQL 5.5 Reference Manual | 18.4.1. Event Scheduler Overview |
http://dev.mysql.com/doc/refman/5.5/en/events-overview.html

B. MySQL 5.5 Reference Manual | 18.4.2. Event Scheduler Configuration |
http://dev.mysql.com/doc/refman/5.5/en/events-configuration.html

출처 - http://h391106.tistory.com/257

저작자표시

'DB > MySQL' 카테고리의 다른 글

mysql - enum type (0)	2012.09.12
mysql - sql 연산자 (0)	2012.09.08
mysql - 날짜 함수 및 날짜 변환 (0)	2012.09.03
mysql - 사용자 패스워드 변경하기 (0)	2012.09.03
mysql - 리플리케이션(replication) (0)	2012.09.01