MySQL 예약 작업 (with Prisma)

News Keeper 앱에는 데이터 백업/복원 기능이 있습니다.

 

그리고 백업 데이터는 최대 한 시간까지만 서버에 저장되도록 방침을 정했습니다.

이것을 구현하기 위해 3가지 방법을 고려했습니다.

1. 백업 API 요청 완료 후에 setTimeout 함수 실행

uploadEncruptedData는 클라이언트에서 전송된 백업 데이터를 데이터베이스에 저장하는 News Keeper 백앤드 함수입니다.

백업을 하고 얻은 ID를 사용하여 일정 시간 후에 백업 데이터를 삭제해 줍니다.

 

uploadEncryptedData() {
    // 백업 수행, 백업 데이터에 접근할 ID 획득
    
    setTimeout(() => {
    	// ID를 사용하여 백업 삭제
    }, 3600000)
}

 

이 방법에는 두 가지 문제가 있습니다.

• 서버가 다운되면 백업 데이터는 관리자가 직접 개입하지 않는한 영원히 삭제되지 못한다.
• 백앤드 리소스를 차지하여 다른 사용자의 요청에 방해가 될 수 있다.

2. 새로운 PM2 앱에서 setInterval 함수 실행

일정 시간마다 백업 테이블을 검사해서 createdAt과 현재 시간이 1시간 이상 차이나는 모든 레코드를 삭제하는 방법입니다.

일단 메인 앱에서 실행하면 안됩니다. News Keeper 백앤드는 PM2 위에서 실행되고 메인 앱은 모든 스래드를 사용하도록 설정해 놨기 때문에 CPU 개수만큼 setInterval이 수행되기 때문입니다.

그래서 1개 인스턴스를 사용하는 앱을 따로 만들어서 아래 함수만 실행시킵니다.

 

setInterval(() => {
    // createdAt이 1시간 이상 경과한 백업 레코드 삭제
}, 900000) // 15분

 

이 방법도 문제가 있습니다.

• 일정 시간마다 테이블 전체를 검사합니다.
• 인스턴스가 CPU 한 개를 차지하는데 너무 아깝다.

3. 데이터베이스 스케쥴링 기능 사용

MySQL 데이터베이스는 특정 시간에 또는 일정시간 마다 특정 명령어가 수행되는 기능을 제공합니다.

이 기능을 사용하면 다음과 같은 장점이 있습니다.

• 작업이 데이터베이스 레코드로 기록되기 때문에 백앤드 서버가 다운되도 작업이 유실될 염려가 없습니다.
• 데이터베이스 서버는 백앤드와 독립된 컴퓨터로 구성했기 때문에 백앤드 리소스를 차지하지 않습니다.

2020년 12월 10일 10시 15분에 ID가 1인 백업 레코드를 삭제하는 MySQL 명령어는 다음과 같습니다.

aaa는 스케쥴의 이름이고 고유한 값(저같은 경우 랜덤 문자열 입력)을 입력해 주면 됩니다. 그리고 날짜만 현재 시간을 기준으로 한 시간을 더해주면 됩니다.

 

CREATE EVENT IF NOT EXISTS aaa
ON SCHEDULE AT 2020-12-10 10:15:00
ON COMPLETION NOT PRESERVE
ENABLE
DO DELETE FROM Backup WHERE id=1;

 

그렇다고 문제가 완전히 해결된 것은 아니었습니다. 다음 두 가지 문제가 추가로 발생하더군요.

• News Keeper 백앤드는 데이터베이스 관리 라이브러리로 Prisma를 사용하고 있는데, Prisma는 스케쥴링을 지원하지 않는다.
• 대신 Prisma는 SQL 명령어를 그대로 실행시켜주는 $queryRaw 함수를 제공하는데, 이마저도 모든 SQL 명령어를 지원하는 것은 아니다. 운이 없게도 스케쥴링을 지원하지 않았습니다.

Prisma는 다른 테이블과 연결된 테이블의 레코드 삭제를 지원하지 않기 때문에, $queryRaw 함수에 SQL 명령어를 입력해서 삭제했었는데, 이 경우에는 적용하지 못했습니다.

 

그래서 mysql 라이브러리를 사용하여 스케쥴링 명령어를 실행했습니다. Prisma CLI가 만든 데이터베이스 테이블은

Prisma 설정 파일의 내용대로 릴레이션이 적용된 일반적인 테이블입니다. 그리고 Prisma 클라이언트는 거의 쿼리 생성기에 가깝습니다. 따라서 mysql 라이브러리를 사용해서 Prisma CLI가 만든 테이블을 다루는데는 아무런 문제가 없습니다.

참조: Prisma2 CLI를 사용하여 데이터베이스 테이블 생성

Prisma2 CLI를 사용하여 데이터베이스 테이블 생성

 

그래서 uploadEncryptedData 함수 마지막에 다음 코드를 실행시키는 방법으로 이 문제를 해결했습니다.

백업/복원 기능의 경우 사용 빈도가 적을 것으로 예상되기 때문에 굳이 Pool을 만들지는 않았습니다.

 

const temp_name = await cryptoRandomString.async({length: 15, type: 'alphanumeric'})
const date = new Date(Date.parse(backup.createdAt) + 3600000) // 3600000 = 1시간


const connection = mysql.createConnection({
    host     : process.env.HOST,
    user     : process.env.USER,
    password : process.env.PASSWORD,
    database : process.env.DATABASE
})

connection.connect()

const query = `CREATE EVENT IF NOT EXISTS ${temp_name}
ON SCHEDULE AT '${date.getUTCFullYear()}-${date.getUTCMonth()+1}-${date.getUTCDate()} ${date.getUTCHours()}:${date.getUTCMinutes()}:${date.getUTCSeconds()}'
ON COMPLETION NOT PRESERVE
ENABLE
DO DELETE FROM Backup WHERE id=${backup.id};`

connection.query(query, function (error) {
    if (error) {
        throw error
    }
})

connection.end()