MySQL 로그 종류 알아보기

서버를 운영하다 보면 어플리케이션, 네트워크, DB 등에서 장애가 발생할 수 있다. 이때 장애의 원인을 파악하기 위해서는 로그를 확인하는 것은 매우 중요하다.

Laravel 을 개발했을 당시 Laravel 로그을 보는것도 문제 해결에 도움이 되었지만, Apache 로그도 무시할 수 없었다. 때문에 로그의 중요성을 알게 되었다.

또한 Express 로 개발된 서비스에 장애가 발생했었는데 async/await 누락으로 Express 의 로그로는 트래킹을 할 수 없어 Postgres 로그를 확인하여 문제를 해결했었다.

 

아래는 MySQL 운영시 로그 정보를 저장하는 방법이다.

MySQL 의 로그 종류

1. 에러 로그(Error Log)
2. 제너럴 로그(General Log - MySQL Command History Log)
3. 슬로우 쿼리 로그 (Slow Query Log)
4. 바이너리 로그 (Binary Log)
5. 릴레이 로그 (Relay Log)

에러 로그(Error Log)

MySQL 구동, 모니터링, 쿼리 에러에 대한 메시지를 포함한 로그이다.

• 설정 경로: /etc/my.cnf
• 기본 경로: /var/log/mysqld.log
• 설정 예시: log-error=/$경로/$파일명.log
• 또는 mysql 데이터 디렉터리에 .err 형식으로 저장

2. 제너럴 로그 (General Log - MySQL Command History Log)

MySQL에서 실행되는 전체 쿼리에 대한 로그를 활성화하여 저장한다. 

때문에 MySQL의 제너럴 로그를 Production 환경에서 활성화하면 서비스 규모에 따라 엄청난 문제가 발생할 수 있다.

1. 성능 저하: 모든 쿼리를 기록하기 때문에 서버에 많은 I/O 작업이 발생한다. 이는 성능 저하의 원인이 된다.
2. 보안 문제: 사용자 이메일, 이름 등의 내용이 로그에 기록된다. 수집된 로그가 외부에 유출될 경우 보안 사고가 발생한다.
3. 디스크 공간 사용: 많은 양의 로그가 디스크에 적제되기 때문에 공간이 사용된다. 

그럼에도 불구하고 디버깅 용도로 Production 환경에서 사용될 수 있는데 이는 어플리케이션 동작을 트래킹하는데 사용할 수 있다.

기본적으로 제너럴 로그는 비활성화되어 있다. 

2-1. 제너럴 로그 설정

# 제너럴 로그 상태 확인 
mysql> show variables like 'general%';
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log      | ON                              |
| general_log_file | /var/lib/mysql/4355c3236fb1.log |
+------------------+---------------------------------+
2 rows in set (0.02 sec)

# 제너럴 로그 활성화
set global general_log = ON;

# 제너럴 로그 비활성화
set global general_log = OFF;

2-2. 슬로우 쿼리 로그 설정 확인

-- 일반 로그 관련 설정 확인
mysql> SHOW GLOBAL VARIABLES LIKE '%log%';
mysql> SHOW VARIABLES WHERE Variable_name IN ('version', 'general_log', 'general_log_file', 'log_output');
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log      | OFF                             |
| general_log_file | /var/lib/mysql/4355c3236fb1.log |
| log_output       | FILE                            |
| version          | 8.2.0                           |
+------------------+---------------------------------+
4 rows in set (0.00 sec)

-- 일반 로그 활성화
mysql> SET GLOBAL general_log = 1;
mysql> SHOW VARIABLES WHERE Variable_name IN ('version', 'general_log', 'general_log_file', 'log_output');
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log      | ON                              |
| general_log_file | /var/lib/mysql/4355c3236fb1.log |
| log_output       | FILE                            |
| version          | 8.2.0                           |
+------------------+---------------------------------+
4 rows in set (0.00 sec)

3. 슬로우 쿼리 로그 (Slow Query Log)

long_query_time 에 설정된 시간을 초과할 경우 로그에 기록한다.

슬로우 쿼리 로그 설정은 동적으로 가능하며, MySQL 프로세스가 CPU/Memory 자원을 비정상적으로 많이 사용할 때 확인하는 용도로 쓰일 수 있다. 이를 주기적으로 모니터링해 데이터베이스 I/O 을 줄여 서버의 비용을 절감시킬 수 있다. 

DataDog 와 같은 모니터링을 통해서도 Slow Query 을 추적할 수 있다.
하지만 DataDog 는 비싸니  Prometheus + Grafana + MySQL Exporter 을 통해 Slow Query 을 추적할 수 있으며, ELK(Elastic Stack) 을 통해서도 Slow Query 을 추적할 수 있다.
현실적으로 서버에서 Slow Query 을 매번 확인할 수 없으니, 모니터링 + 알림을 통해 추적해야 한다. 

3-1. 슬로우쿼리 로그 설정

# 슬로우쿼리 로그 설정 확인
mysql> show variables like 'slow%'; 
mysql> show variables like 'long%'; 
mysql> show variables like 'log%'; 

# 슬로우쿼리 로그 On/Off
mysql> set global slow_query_log = ON;
mysql> set global slow_query_log = OFF;

vi /etc/my.cnf
slow_query_log_file = /datadir/serverhostname-slow.log

# 롱 쿼리 타임 설정
mysql> set global long_query_time = 10;

# 환경 파일 설정
vi /etc/my.cnf
long_query_time = 10.000000
# -> 10초 이상 query 를 기록하는 설정 : 쿼리타임이 10초를 초과하는 쿼리에 대해 /temp/mysql-slow.log 파일에 로그 기록

# 로그 출력 타입 설정
vi /etc/my.cnf
log_output = $FILE명

# 인덱스를 사용하지 않는 쿼리 추출 옵션 ON, OFF
vi /etc/my.cnf
log_queries_not_using_indexes = OFF

 

로그 설명

• Time : 쿼리가 종료된 시간
• Query_time : 쿼리가 실행된 시간
• Lock_time : MySQL 엔진 레벨의 테이블 잠금 대기시간 ( 테이블 Lock 걸린 시간 )
• Row_sent : 클라이언트로 보낸 실제 처리 건수 ( 쿼리 처리 결과 Row 수 )
• Row_examined : 쿼리 처리를 위해 접근한 레코드 건수 ( 쿼리 처리 대상의 Row 수 )

3-2. 슬로우 쿼리 로그 설정 확인

-- 슬로우 쿼리 로그 설정 확인
mysql> SHOW GLOBAL VARIABLES LIKE '%slow%';
+-----------------------------+--------------------------------------+
| Variable_name               | Value                                |
+-----------------------------+--------------------------------------+
| log_slow_admin_statements   | OFF                                  |
| log_slow_extra              | OFF                                  |
| log_slow_replica_statements | OFF                                  |
| log_slow_slave_statements   | OFF                                  |
| slow_launch_time            | 2                                    |
| slow_query_log              | OFF                                  |
| slow_query_log_file         | /var/lib/mysql/4355c3236fb1-slow.log |
+-----------------------------+--------------------------------------+
7 rows in set (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE '%long%';
+----------------------------------------------------------+-----------+
| Variable_name                                            | Value     |
+----------------------------------------------------------+-----------+
| long_query_time                                          | 10.000000 |
| performance_schema_events_stages_history_long_size       | 10000     |
| performance_schema_events_statements_history_long_size   | 10000     |
| performance_schema_events_transactions_history_long_size | 10000     |
| performance_schema_events_waits_history_long_size        | 10000     |
+----------------------------------------------------------+-----------+
5 rows in set (0.00 sec)

-- 슬로우 쿼리 로그 활성화
mysql> SET GLOBAL slow_query_log = 1; -- 슬로우 쿼리 로그 활성화
mysql> SHOW GLOBAL VARIABLES LIKE '%slow%';
+-----------------------------+--------------------------------------+
| Variable_name               | Value                                |
+-----------------------------+--------------------------------------+
| log_slow_admin_statements   | OFF                                  |
| log_slow_extra              | OFF                                  |
| log_slow_replica_statements | OFF                                  |
| log_slow_slave_statements   | OFF                                  |
| slow_launch_time            | 2                                    |
| slow_query_log              | ON                                   |
| slow_query_log_file         | /var/lib/mysql/4355c3236fb1-slow.log |
+-----------------------------+--------------------------------------+
7 rows in set (0.00 sec)

mysql> SET GLOBAL long_query_time = 5; -- 1초 이상의 쿼리를 슬로우 쿼리로 로깅
mysql> SHOW GLOBAL VARIABLES LIKE '%long%';
+----------------------------------------------------------+----------+
| Variable_name                                            | Value    |
+----------------------------------------------------------+----------+
| long_query_time                                          | 1.000000 |
| performance_schema_events_stages_history_long_size       | 10000    |
| performance_schema_events_statements_history_long_size   | 10000    |
| performance_schema_events_transactions_history_long_size | 10000    |
| performance_schema_events_waits_history_long_size        | 10000    |
+----------------------------------------------------------+----------+
5 rows in set (0.00 sec)

4. 바이너리 로그(Binary log) / 릴레이 로그(Relay log)

바이너리 로그(Binary Log)

MySQL에서 발생하는 모든 데이터 변경 작업을 기록하는 한다.

이는 데이터베이스 서버가 실행 중인 동안 발생하는 삽입, 갱신, 삭제 등의 데이터 변경 작업을 기록하며 바이너리 로그는 데이터의 백업과 복구, 복제, 데이터 동기화를 위해 사용된다. 데이터베이스의 상태를 지속적으로 기록함으로써 시스템의 내결함성을 보장하고 데이터의 안정성을 유지한다. 또한 데이터의 변경 히스토리를 추적하거나 데이터베이스 복구 시에도 사용된다.

 

릴레이 로그(Relay log)

MySQL의 Master/Slave Replication 과정에서 사용된다.

릴레이 로그는 슬레이브 서버가 마스터 서버로부터 받은 데이터를 저장하는 임시 파일이다. 이 파일은 슬레이브 서버가 데이터를 받은 후 적용하기 전에 저장되는데, 복제 과정에서 데이터가 정상적으로 전송되었는지 확인하기 위한 보조 역할을 수행한다. 때문에 릴레이 로그는 복제된 데이터를 일시적으로 저장하고, 해당 데이터를 슬레이브 데이터베이스에 적용하기 전에 검증하는 데 사용된다. 그러므로 리플리케이션간의 안정성을 유지하고 데이터 일관성이 보장된다. 

 

요약하자면 MySQL 쿼리를 수행하면서 생성되는 로그이며 트랜잭션하여 시점 복구 등에 사용되며 일반적으로 마스터에서 바이너리 로그, 슬레이브에서 릴레이 로그가 생성된다.

4-1. 바이너리 로그 관리

# /etc/my.cnf 파일 수정
vi /etc/my.cnf

# 바이너리 로그 관련 설정
log-bin = /${데이터 저장 디렉토리}/mysql-log/bin
expire_logs_days = 7    # 7일 이전 로그 삭제
max_binlog_size = 100M  # 100M 이상 로그 생성시 새로운 로그 생성
binlog_format = ROW     # 바이너리 로그 포맷 설정
binlog_cache_size = 32K # 바이너리 로그 캐시 사이즈 설정

4-2. 바이너리 로그 확인

# 바이너리 로그 확인
show binary logs;

# 바이너리 로그 관리
# 'mysql-bin.000012' 는 Master/Slave 설정할 때 알 수 있다. 
purge master logs to 'mysql-bin.000012';

5. OS의 logrotate를 활용하여 로그 관리

서버의 로그를 효율적으로 관리하기 위해 logrotate 가 사용될 수 있다. logrotate는 로그 파일을 주기적으로 백업 및 압축하고, 설정된 기간 이후에 삭제하는 등의 작업을 할 수 있다. 이때 MySQL 로그 관리를 위해 OS의 logrotate 을 사용한다.

# MySQL General 로그 설정
/var/log/mysql_history.log {
    weekly
    rotate 3
    compress
    missingok
    notifempty
    sharedscripts
    create 660 mysql mysql
    postrotate
        /usr/bin/mysqladmin flush-logs
    endscript
}

# MySQL Slow 로그 설정
/var/log/mysql-slow.log {
    weekly
    rotate 3
    compress
    missingok
    notifempty
    sharedscripts
    create 660 mysql mysql
    postrotate
        /usr/bin/mysqladmin flush-logs
    endscript
}

 

logrotate 설정이 완료되면 cron 재시작을 해야한다.

/etc/init.d/crond restart
chkconfig --list | grep crond
chkconfig crond on
ps -ef | grep cron