안녕하세요. 그린주입니다 ๑'ٮ'๑
오늘도 힘차게 시작해보겠습니다!
개요
이번 글에서는 테이블 백업을 수행하는 프로시저를 생성해보고 이벤트 스케줄러를 만드는 방법에 대해 공유하고자 합니다.
예시 설명
오늘은 10월 1일입니다.
member 테이블을 가입 명단이라고 했을 때, 매달 1일에 지난달 가입 명단을 백업하려고 합니다.
가입일자는 member 테이블의 reg_date입니다.
프로시저 명은 backup_member로 생성합니다.
백업 테이블명은 backup_member_[%Y%m]으로 생성합니다.
목차
프로시저
이벤트
프로시저
프로시저는 함수처럼 매개변수를 받고 여러 쿼리를 하나의 쿼리로 실행하는 명령어입니다.
처리 성능과 재사 용면에서의 단점은 존재하지만
하나의 요청으로 여러 개의 SQL문을 실행할 수 있어 네트워크 소유 시간을 줄일 수 있고,
보수성이 뛰어나며, DB 관련 처리를 API처럼 만들어서 제공할 수 있다는 장점이 있습니다.
프로시저 생성
-- 해당 프로시저명이 존재하면 삭제 후 실행
DROP PROCEDURE IF EXISTS backup_member;
DELIMITER $$
-- 프로시저 생성
CREATE PROCEDURE backup_member()
BEGIN
-- 백업 테이블명
SET @table := CONCAT('backup_member_', DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y%m'));
-- 백업 테이블 생성
SET @sql := CONCAT(
'CREATE TABLE ',
@table,
' SELECT * FROM member',
' WHERE DATE_FORMAT(reg_date,"%Y%m") = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),"%Y%m")'
);
-- SQL문 선언 및 실행
PREPARE stmt FROM @sql;
EXECUTE stmt;
SELECT '테이블 백업 성공';
END $$
DELIMITER ;DROP PROCEDURE IF EXISTS [프로시저명];
프로시저를 생성하기 전에 생성하는 프로시저명으로 된 프로시저가 있을 경우 삭제합니다.
이 부분은 생략이 가능하지만 이미 해당 이름을 가진 프로시저가 존재한다면 SQL 오류 (1304)가 발생할 수 있습니다.
DELIMITER $$ ~~~ DELIMITER ;
MySQL에서는 SQL문을 세미콜론(;)으로 구분하여 실행하는데, 프로시저 안에는 여러 SQL문을 사용하기 때문에 어떤 문법인지 인식이 안됩니다. DELIMITER를 사용하여 프로시저 자체를 하나의 SQL문으로 구분하기 위한 명령어입니다.
CREATE PROCEDURE [프로시저명](매개변수); BEGIN ~~[SQL문]~~ END $$
프로시저를 생성합니다.
PREPARE STATEMENT
PREPARE, EXECUTE, DEALLOCATE 명령문을 사용하여 동적 SQL문을 만드는 준비된 명령문입니다.
PREPARE [stmt] FROM "[SQL문]";
SQL문을 선언합니다.( = 명령문)
위 코드에서는 CONCAT()으로 문자열로 만들어줬기 때문에 바로 변수명(@sql)을 사용했습니다.
직접 넣어 줄 경우는 PREPARE stmt FROM "SELECT * FROM member WHERE reg_date = ?"; 이런 식으로 넣어줍니다.
EXECUTE [stmt] USING [@변수, ...];
미리 선언된 SQL 구문을 실행합니다.( = 명령문에 의해 준비된 명령문(PREPARE)을 실행합니다.)
위 코드에서는 인자 값이 없기 때문에 USING 부분은 생략했습니다.
직접 넣어 줄 경우는 ?의 순서에 따라 변수를 바인딩하면 됩니다.
프로시저 확인
등록된 프로시저를 확인합니다.
-- 프로시저 목록 확인(전체)
SHOW PROCEDURE STATUS;
-- 프로시저 확인
SHOW CREATE PROCEDURE backup_member;프로시저 실행
프로시저가 성공적으로 실행됐다면 SQL 구문에서 설정한 "테이블 백업 성공" 문구가 출력되고
백업 테이블이 생성된 것을 확인할 수 있습니다.
CALL backup_member();
프로시저 삭제
해당 프로시저명이 존재하는지 여부에 따라 삭제합니다.
DROP PROCEDURE IF EXISTS backup_member;
"프로시저가 삭제되었습니다."가 출력됩니다.이벤트
프로시저를 정기적으로 실행시켜야 할 때 이벤트 스케줄러를 사용합니다.
이벤트에 DAY, MONTH, TIME별로 시간을 지정해 준다면 자동으로 프로시저가 실행됩니다.
기본 설정
MySQL에서 이벤트 스케줄러를 사용하려면 사용 상태를 설정해야 합니다.
저는 AWS RDS를 사용하고 있어 아래 설정은 AWS 콘솔 파라미터 그룹에서 따로 설정해줬습니다.
-- Value가 ON인지 확인합니다.
SHOW VARIABLES LIKE 'event%';
-- OFF라면 아래 명령어를 통해 설정을 ON으로 변경합니다.
SET GLOBAL event_scheduler = ON;이벤트 생성
프로시저와 동일하게 이벤트 스케줄러명은 중복될 수 없음으로 EXISTS를 통해 확인 후 생성하도록 합니다.
이벤트는 DB에 개별적으로 생성됨으로 "여러 DB가 연결되어있는 경우" 이벤트를 생성할 DB를 더블 클릭 후 진행합니다.
-- []는 생략 가능합니다.
CREATE EVENT [IF NOT EXISTS] event_backup_member
ON SCHEDULE
EVERY 1 MONTH
[STARTS '2022-10-01']
[COMMENT '회원 가입명단 백업']
DO CALL backup_member();CREATE EVENT [IF NOT EXISTS] [이벤트 스케줄러명]
중복된 이벤트 스케줄러명이 있는지 확인 후 이벤트 스케줄러를 생성합니다.
ON SCHEDULE EVERY [반복 주기]
반복 주기 설정입니다. 매달 반복이기 때문에 1 MONTH를 넣어줍니다.
MONTH, WEEY, DAY, HOUR 등 사용 가능합니다. 예) 매일 - 1 DAY, 매년 - 1 YEAR
COMMENT [주석]
어떤 이벤트인지 설명을 넣어줍니다.
STARTS [시작일자]
시작일을 지정할 수 있습니다.
DO [동작]
프로시저를 실행할 이벤트기 때문에 CALL backup_member()를 넣어줍니다.
이벤트 조회
SElECT * FROM information_schema.`EVENTS`;
or
SHOW EVENTS;이벤트 삭제
DROP EVENT IF EXISTS event_backup_member;
"명령이 성공적으로 실행되었습니다."가 출력됩니다.MySQL 시간 확인
SELECT NOW();마무리
이렇게 테이블 백업을 수행하는 프로시저를 생성해보고 이벤트 스케줄러를 만드는 방법에 대해 적어보았습니다.
다들 성공하셨을까요?? 끝까지 파이팅입니다!
긴 글 봐주셔서 감사합니다!
오늘도 행복한 하루 보내세요 ✿'◡'✿
참고
[MySQL] 프로시저(Procedure), Delimiter
MySQL 이벤트 스케쥴러(Event Scheduler)