(mysql, mariadb) general_log를 통한 실행된 쿼리 확인 방법

MySQL, MariaDB general_log를 통해 실행된 쿼리 확인 방법

 

mysql이나 mariadb를 사용하다 보면 실행된 쿼리를 확인하고 추적해야 할 때가 있는데요.

그럴 때 'general_log'를 통해 클라이언트로부터 접속된 접속 내용 및 수행된 모든 쿼리를 확인할 수 있으며, 아래 내용을 통해 자세한 방법을 살펴보도록 하겠습니다.

 

 

* general_log 외 log 종류에 대해서 간단하게 살펴보면 아래와 같습니다.

• General Query Log: 클라이언트의 접속 내용 및 수행된 모든 쿼리를 기록합니다.
• Error Log: MySQL 시작, 종료 과정, 비 정상적인 종료 시 발생하는 트랜잭션 메시지, 쿼리 실행 중 발생하는 에러 등을 기록합니다.
• Binary Log: 데이터 조작어(DML) 쿼리를 기록합니다. 정확히는 DML 중에서도 INSERT, UPDATE, DELETE를 기록하며 SELECT는 기록하지 않습니다.
• Relay Log: Replication 구성 시, 마스터 서버에는 Binary Log가 기록되며, 슬레이브 서버에는 Relay Log가 기록됩니다. (Binary Log와 Relay Log의 기록 내용은 동일합니다.)
• Slow Query Log: long_query_time 파라미터(초 단위로 설정)에 설정된 시간보다 수행 시간이 긴 쿼리를 기록합니다. 발생한 에러에 대한 기록은 남기지 않습니다.
• DDL Log: 데이터 정의어(DDL) 쿼리를 기록합니다.

 

---

general_log 확인 방법

 

1. general_log에 대한 설정을 조회

mysql> SHOW VARIABLES LIKE '%general_log%';
+------------------+------------------------------------------------+
| Variable_name    | Value                                          |
+------------------+------------------------------------------------+
| general_log      | OFF                                            |
| general_log_file | /usr/local/var/mysql/general_log.log           |
+------------------+------------------------------------------------+

general_log에 대한 설정은 다음과 같은 쿼리를 통해 확인할 수 있으며, 조회 결과 general_log의 기본 설정 값이 'off'로 비활성화되어 있는 것을 확인할 수 있는데요.

 

활성화 또는 비활성화를 실행하는 쿼리는 아래와 같습니다.

//활성화
SET GLOBAL general_log = ON;
SET GLOBAL general_log = 1;

//비활성화
SET GLOBAL general_log = OFF;
SET GLOBAL general_log = 0;

(요청 시 root 권한으로 mysql 또는 mariadb에 접속된 상태여야 합니다.)

 

 

2. 로그가 출력되는 방식에 대한 설정

mysql> SHOW VARIABLES LIKE '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.01 sec)

log_output은 로그 출력 방식을 의미하며 'file' 또는 'table', 또는 'file' + 'table' 둘 다로 출력이 가능한데요.

 

//출력 방식 설정
SET GLOBAL log_output = 'TABLE';
SET GLOBAL log_output = 'FILE';
SET GLOBAL log_output = 'TABLE,FILE';

위 실행 쿼리를 통해 출력 방식을 설정할 수 있습니다.

 

 

general_log engine csv

이때 출력 방식을 table로 설정하게 되면 general_log table에 기본적으로 설정된 Engine이 'CSV'로 되어있는 것을 확인할 수 있는데요.

데이터베이스 엔진 특성상 'MyIsam'으로 변경해 주는 것이 권장되며, 변경 시에는 general_log 옵션을 off로 변경한 뒤 아래 실행 쿼리를 통해 db engine을 변경해야 합니다.

 

//table engine 확인
SHOW TABLE STATUS LIKE 'general_log'\G
SELECT engine FROM information_schema.TABLES WHERE table_name='general_log';

//table engine 변경
ALTER TABLE mysql.general_log engine=MyIsam;

 

 

general_log file

 

general_log table

file과 table 출력 형식에 대한 출력 결과이며, 테이블 조회의 경우 아래 쿼리문으로 조회가 가능합니다.

SELECT * FROM general_log LIMIT 100;

 

 

3. 설정 유지 방법 (.cnf 파일 변경)

mysql, mariadb 서버를 재실행하면 general_log의 상태값은 다시 'off'로 변경됩니다.

따라서 데이터베이스를 재실행해도 로그가 남도록 하기 위해서는 'my.cnf' 파일의 설정값 변경이 필요한데요.

 

//general_log options
general_log = on
general_log_file = 로그 파일 위치

로그 파일의 위치를 설정하는 경우 해당 로그 파일의 디렉터리 및 파일을 만들어주고 권한을 부여하는 과정이 필요하며, 설정 이후에는 데이터베이스를 재실행해야 해당 설정이 적용됩니다.

 

 

4. 주의할 점

앞서 이야기한 것처럼 general_log는 데이터베이스에 요청되는 모든 쿼리를 다 기록하는데요.

때문에 해당 데이터베이스에 요청이 많은 경우 file 또는 table에 데이터가 급격하게 쌓일 수 있다는 점은 꼭 주의해야 하는 부분입니다.

 

file의 경우 용량이 커져서 서버의 용량을 너무 많이 차지하게 되고 결국 상황에 따라 서버가 다운될 수도 있습니다.

table 역시 데이터가 많아지면 select 시 데이터베이스에 부하가 걸릴 수 있습니다.

 

이러한 문제를 해결하기 위해서는 crontab 또는 procedure를 통한 방법(일정 시간 또는 일정 용량에 따라 파일을 분리하는 등)이 사용되고 있습니다.

 

 

 

< 참고 자료 >

https://hyeonyeee.tistory.com/70

https://myinfrabox.tistory.com/10

https://velog.io/@jsj3282/6.-MySQL-로그-파일