DB 데이터가 계속 쌓이면 디스크가 풀 찰 수 있기 때문에 주기적으로 필요 없는 데이터를 delete 하는 스케줄러를 활용하면 좋다.
CREATE EVENT 구문으로 스케줄러 지정을 할 수 있는데 HeidiSQL에서 GUI로 쉽게 설정이 가능하다.
1. MySQL 이벤트 스케줄러 동작 설정
이벤트 스케줄러 기능이 ON 상태인지 확인한다.
SHOW VARIABLES LIKE 'event%'기본값은 OFF 다. 이걸 ON으로 변경해준다.
SET GLOBAL event_scheduler = ON;참고로 이 방법은 mysql 서버를 재시작하면 다시 초기화 된다. 영구적으로 적용하려면 my.cnf에 설정해야 된다.
2. 데이터 보관 기간 확인
삭제 자동화를 위해선 특정 날짜를 지정하는 방법이 아니라 현재 날짜를 기준으로 몇 년, 몇 개월 이런 식으로 지정해야 한다.
최근 1년치만 남긴다는 가정으로 진행해보겠다.
logs 테이블에는 log_date 컬럼(TIMESTAMP)에 2024년부터 2018년까지 데이터가 들어가 있다.
현재 날짜는 2024년 4월 10일이다. 오늘 날짜를 기준으로 1년치 데이터만 남길 것이다.
그러니까 2023년 4월 10일 포함 이전 데이터는 모두 지운다. 다음 날이 되면 2023년 4월 11일 포함 이전 데이터는 모두 지운다… 그 다음날이 되면 2023년 4월 12일 포함 이전 데이터는 모두 지운다…
이런 시나리오다.
결론부터 말하면
DELETE FROM logs WHERE log_date <= NOW() - INTERVAL 1 YEAR이 쿼리를 매일 같은 시간에 반복하도록 할 것이다.
3. 삭제할 데이터 조회
주의사항이 있다.
삭제 스케줄러를 걸기 전에는 무조건 select 로 삭제할 데이터를 먼저 조회해 봐야 한다.
SELECT * FROM logs WHERE log_date <= NOW() - INTERVAL 1 YEAR
2023년 4월 10일 이전 데이터가 정상적으로 확인된다.
부등호 하나 잘못 걸면 최신 데이터가 모조리 날라기기 때문에 다시 말하지만 꼭 확인해야 된다.
4. 스케줄러 설정
HeidiSQL에서 스케줄러를 설정할 테이블을 우클릭 > 새로 생성 > 이벤트를 클릭한다.
스케줄러 이름과 코멘트를 적고 일정 탭으로 넘어간다.
반복 버튼을 선택하고 시작됨을 체크한 뒤에 최초로 시작되는 시간을 설정한다. 그리고 실행 본문 BEGIN과 END 사이에 자동으로 실행할 쿼리를 넣는다.
저장을 클릭하면 스케줄러가 설정된다. 왼쪽 테이블 내역에 보면 logs_delete 라는 스케줄러가 생성된 걸 볼 수 있다.
CREATE 코드 탭을 보면 해당 스케줄러를 생성하기 위해 사용된 쿼리를 볼 수 있는데 결국
CREATE EVENT `logs_delete`
ON SCHEDULE
EVERY 1 DAY STARTS '2024-04-10 11:00:00'
ON COMPLETION NOT PRESERVE
ENABLE
COMMENT '1년 치 로그만 남기고 자동으로 삭제'
DO BEGIN
DELETE FROM logs WHERE log_date <= NOW() - INTERVAL 1 YEAR
END이 쿼리를 수행한 것이다.
5. 스케줄러 실행 후 확인
예약 걸어둔 시간이 지나고 logs 테이블을 조회해보니 1년치만 남고 모두 삭제됐다.
이런 흐름 안에서 삭제 뿐만 아니라 필요한 쿼리를 스케줄러에 등록하고 쓸 수 있고 일반적으로 쿼리 실행 시간은 사용이 거의 없는 새벽시간으로 지정한다.