mysql general_log shell script로 백업 관리 하기

mysql general_log  shell script로 백업 관리 하기

 

안녕하세요.

 

저는 mysql general_log 파일 형태가 아니고 테이블 형태로 사용하고 있습니다.

 

mysql의 general_log를 설정하셔서 사용하게 되면,

 

아래와 같이 mysql DB내에서 실행된 다양한 SQL의 log를 기록하고 확인할 수 있습니다.

general_log를 파일 형태로 저장하여 볼 수도 있지만, 저 같은 경우에는 그냥 DB 툴을 이용해서 SQL로 바로 확인하는 것이 편해서 테이블로 받는 방법을 선호합니다.

 

general_log 설정

우선 general_log를 mysql에서 설정하는 방법을 알려드려야 하는데, 이전에 이미 작성해둔 관련 포스팅이 있어서 함께 링크를 걸겠습니다. 필요하신 분은 참고하시면 됩니다.

2020/01/06 - [Database/mariaDB administrator] - [mariadb] general log 설정 및 table에 log 쌓고 조회 설정 하기

[mariadb] general log 설정 및 table에 log 쌓고 조회 설정 하기

이 general_log를 테이블에 쌓게 되면 단점이 한 번씩 비워 줘야 한다는 겁니다.

 

물론 general_log는 스토리지 엔진 자체가 csv 엔진을 사용하기 때문에 외부 데이터를 빠르게 적재할 수 있고, 로깅에 최적화되어 있지만 인덱싱을 통한 접근이 어렵기 때문에 아주 많이 커지지 않도록 한 번씩 비워주면 쓸데없는 지나간 데이터들을 굳이 가지고 있지 않아도 되게 됩니다.

 

그래서 저 같은 경우는 1주일에 한 번씩 general_log를 다른 테이블로 rename 하고, 새로운 general_log 테이블을 만들어 주는 방법으로 관리를 하고 있습니다.

 

항상 아래와 같이 3개의 general_log 테이블이 존재를 하게 됩니다.

general_log 백업 로직

당연히 이 중에서 현재 로그가 쌓이는 테이블은 general_log 테이블입니다.

 

지난 1주일의 로그는 1 week_general_log에 쌓입니다.

 

1주 전의 지난 1주일의 로그는 2 week_general_log 테이블에 쌓여 있는 것 이죠.

 

저는 이 general_log의 테이블 스위칭을 매주 일요일 새벽에 실행되도록 해두 었는데요. 

 

아래와 같이 실행이 됩니다.

 

우선, tmp_general_log 테이블을 생성을 하게 됩니다.

temp_general_log 테이블 생성

그러고 나면, 2주 전 데이터인 2 week_general_log 테이블을 drop 합니다.

2week_general_log 테이블 drop

그리고, 아래와 같이 rename을 하게 됩니다.

 

이렇게 하면 다시 새로운 general_log에 이번 주 SQL 로그가 쌓이게 되고 1주일씩 데이터가 뒤로 가서 쌓이고, 지난주에 2 week_general_log에 있는 데이터들은 drop이 되게 됩니다.

 

필요하다면 이런 식으로 2,3,4,5.... 주 늘려가면서 쌓을 수도 있겠죠.

 

general_log backup shell script

shell script는 매우 간단합니다.

 

그저 temp_general_log 생성해주고, 2 week_general_log 테이블을 drop 하고 나머지 것들을 rename 해주면 됩니다.

#!/bin/bash

# rename general log tables
#
# 0. create tmp_general_log table
# 1. 2week_general_log table DROP
# 2. rename 1week_general_log table to 2week_general_log table
# 3. rename general_log table to 1week_general_log table
# 4. rename tmp_general_log table to general_log table

mysql -udba_xxxxxxy -p'xxxxxx' -e "create table mysql.tmp_general_log like mysql.general_log"

mysql -udba_xxxxxxy -p'xxxxxx' -e "drop table mysql.2week_general_log"

mysql -udba_xxxxxxy -p'xxxxxx' -e "rename table mysql.1week_general_log to mysql.2week_general_log, mysql.general_log to mysql.1week_general_log, mysql.tmp_general_log to mysql.general_log"

위 shell script 소스를 저장하고, 해당 쉘을 crontab에 등록해서 매주 일요일, 또는 원하는 날에 실행되도록 설정하면 됩니다.

 

전 위에서도 언급하였듯이 매주 일요일 오전 4시에 작업이 되도록 하였습니다.

 

crotab에는 아래와 같이 등록을 했습니다.

##general_log table weekly backup
0 4 * * 0 /free/shell_script/weekly_general_log_mgmt.sh &

이젠, 손 안 대고, 신경 안 써도 매주 일요일마다 general_log 테이블이 알아서 척척.. 백업이 됩니다.

 

※ 주의사항

general_log는 실제 운영 DB서버에서 설정을 ON 시키는 경우가 흔한것은 아닙니다.

제가 일하는 곳에서도 일부 운영 DB 서버에서만 설정을 하고 있는 상황입니다.

모든 SQL의 로그를 기록하는것 이기에, IO에 부하가 되기 때문 입니다.

slow query log의 경우는 일반적이지만 general log는 일반적인 옵션은 아니라는 점을 참고 하시기 바랍니다!

 

 

감사합니다.

 

 

 

 

by.sTricky