MySQL [Stored Procedure & EVENT Scheduler]

"데이터베이스 내부에 여러 테이블 생성과 트랜잭션 실행 등 작업을 매일 배치성으로 진행할 수 없을까?" 라는 고민에서 해당 작업을 시작하였다.

 

1. Cron과 Airflow 를 통해 SQL 을 배치성으로 수행하기

2. 데이터베이스가 제공하는 배치 기능을 통해 배치성으로 수행하기

 

여기서 2번을 선택하였다. 이유는 데이터베이스 내부의 테이블 생성, 변경, 삭제 등의 작업은 데이터베이스 자체적으로 관리하려했기 때문이다. 1번처럼 다른 도구의 스케줄링과 SQL 을 연결할 수 있지만 관리가 점점 어려워지고 있음을 느꼈다.

 

그래서 여러 스케줄링 작업이 있다고 해도, 각 목적에 맞는 프로그램을 사용하는게 관리하기 더 용이하다고 생각한다.

- 젠킨스 : CI/CD 등 인프라 작업

- AIrflow : 데이터 E,T,L 작업

- 데이터베이스 : 테이블 생성/변경/삭제 혹은 트랜잭션 덤프

 

Stored Procedure 

---

그 외에도 Stored Procedure 를 사용하면 아래와 같은 장점들이 있다고 한다.

1. 성능

- 데이터베이스 내부의 캐시를 이용하기 때문에 실행속도가 빠르다.

2. 보안

- 사용자 별로 권한을 따로 부여한다고 했을 때 테이블 생성/삭제/변경의 권한을 부여하는게 아닌 프로시저 실행/중지에만 권한을 부여할 수 있다. 테이블에 직접 접근하지 않기 때문에 각 보안성이 더 강화된다.

3. 네트워크 부하

- 데이터베이스가 아닌 외부 도구에서 SQL 을 원격으로 실행한다면, 네트워크로 SQL 이 전달된 이후 데이터베이스 내부에서 실행된다. 하지만 저장 프로시저는 데이터베이스 내부에서 실행되기 때문에 원격으로 전달받는 메시지 크기를 줄일 수 있다.

 

그런데 내 목적은 여러 개의 SQL 을 "주기적" 으로 실행하는 것이다. Stored Procedure 는 여러 SQL 을 한번에 실행하는 것임으로 주기적인 실행을 위해서 MySQL 의 "Event Scheduler" 를 사용하였다.

create
    procedure TRANSACTION_PROC(IN DAY decimal)
BEGIN
    SET @TABLE_DETAIL='(
    	#TABLE_DDL
    )';
    
    SET @TRANSACTION_TABLE_CREATE=CONCAT('CREATE TABLE transaction.transaction_',NEXT_DAY, @TABLE_DETAIL);
    PREPARE STMT FROM @TRANSACTION_TABLE_CREATE;
    EXECUTE STMT;
    DEALLOCATE PREPARE STMT;
END;

Event Scheduler

---

1. 우선 MySQL 에 이벤트 스케줄러 사용이 가능한지 확인해야한다.

SHOW VARIABLES LIKE 'event%';

 

1.-1 이벤트 스케줄러 사용을 ON 으로 바꾸기 위해선 아래의 구문을 실행한다.

SET GLOBAL event_scheduler = ON;

 

2. 이벤트 스케줄러 확인

SELECT * FROM information_schema.events;

 

3. 이벤트 생성 

나는 transaction 이라는 스키마에 TRANSACTION_PROC 라는 Stored Procedure 를 생성해 두었다.

CREATE EVENT IF NOT EXISTS DAILY_TRANSACTION_PROC
    ON SCHEDULE
        EVERY 1 DAY
        STARTS '2022-12-29 00:00:01'
    DO
        CALL transaction.TRANSACTION_PROC(CURRENT_DATE()+1);
SHOW EVENTS;

cf. 내 서버는 GMT 기준으로 되어있기 때문에 한국시간보다 9시간 느리다. SELECT NOW() 를 통해 서버의 시간을 보고 적절한 스케줄링이 필요하다.

 

4. 이벤트 지울때 DROP 을 사용하면 된다.

DROP DAILY_TRANSACTION_PROC