MySQL 로그와 파라미터 설정 정리

DBMS에서 Log의 중요성은 DB를 관리하고 사용하는 사람이면 그 누구도 부정할 수 없을 것이다. 로그기록을 생성하면 백업이 가능하고, 이제까지 진행한 작업들을 한눈에 분석할 수 있다. 데이터베이스에서는 Table 생성, Insert, update 등 트랜잭션이 일어나는 모든 작업들이 로그에 기록이 되기 때문이다. 이런 Log들이 왜 필요한가? 바로 데이터베이스의 무결성을 위한 것이다. 그럼 지금부터 MySQL에서 제공하는 Log들에 대해서 하나하난 콕콕 집어보겠습니다.

 

0. MySQL 로그는 어떤 종류가 있을까요?

0.1 Error Log (에러 로그)

- 말그대로 DB에서 발생하는 error들을 기록하는 로그이다.

- DB가 시작하는 과정과 관련된 정보와 비정상적인 종료시 발생할 수 있는 트랜잭션 메세지 등 쿼리 도중에 발생하는 모든 에러들이 기록된다고 생각하면된다.

- InnoDB의 상태조회 명령의 결과 메세지도 기록되어 진다.

- log_error 파라미터를 정의하여 설정을 진행한다.

 

0.2 General Log (제너럴 로그)

- DB 서버에서 실행되는 모든 쿼리가 기록된다.

- 모든 쿼리가 기록되는만큼 도중에 발생하는 에러들도 기록된다.

 

0.3 Slow Query Log (슬로우 쿼리 로그)

- General Log와는 달리 에러가 발생된 쿼리는 기록이되지 않고, 오직 쿼리가 정상적으로 완료된 것들만 기록이 된다.

- Time 파라미터를 통해서 초 단위로 설정가능하며 설정된 시간 이상 소요된 쿼리만 기록하도록하는 기능을 한다.

- 이 로그를 사용하면 비정상적으로 부하가 생기는 부분를 체크할 수 있다.

 

0.4 Binary Log와 Relay Log (바이너리 로그와 릴레이 로그)

- 이 로그들에는 DB의 많으 쿼리 중에 DML(Data Manipulation Language) 쿼리만 기록되는 로그이다.

    ※ DML 쿼리 (Select, Insert, Update, Delete) 단, 위으 로그에는 Select은 기록이 안된다)

- Binary Log와 Relay Log에 기록되는 내용은 동일하고 마스터서버에는 Binary Log가 슬레이브서버에는 Relay Log가 기록된다.

 

1. 로그 디렉토리 생성

로그를 저장할 디렉토리를 알맞은 곳에 생성해둔다. 디렉토리를 생성할때 root로 생성되서 권한이 root로 되어있을 것이다. chown으로 권한을 mysql로 변경해준다.

mkdir -p /var/log/mysql/binary
mkdir -p /var/log/mysql/error
mkdir -p /var/log/mysql/general
mkdir -p /var/log/mysql/relay
mkdir -p /var/log/mysql/slow
 
chown -R mysql:mysql /var/log/mysql
 
[root@mariadb-master mysql]# ls -la
total 4
drwxr-xr-x   7 mysql mysql   68 Jun 15 13:17 .
drwxr-xr-x. 11 root  root  4096 Jun 15 13:15 ..
drwxr-xr-x   2 mysql mysql    6 Jun 15 13:16 binary
drwxr-xr-x   2 mysql mysql    6 Jun 15 13:16 error
drwxr-xr-x   2 mysql mysql    6 Jun 15 13:16 general
drwxr-xr-x   2 mysql mysql    6 Jun 15 13:17 relay
drwxr-xr-x   2 mysql mysql    6 Jun 15 13:17 slow

 

2. MySQL log 관련 파라미터 설정

2.1 log_output

쿼리가 기록되는 방식을 Table 혹은 File 형식으로 기록되도록 설정하는 파라미터이다. default 값은 File형식으로 되어 있으며 File의 경우에는 우리가 평소에 많이 사용하던 편집기나 명령어로 로그를 확인 할 수 있다. 익숙한 방법으로 편리하게 사용이 가능할 것이다. Table 방식에 경우에는 SQL 쿼리를 통해서 내용을 확인할 수 있다. 이 경우의 장점은 개발자들이 DB를 조회할때 서버에 접근하여 로그파일을 열어서보는 것이 아닌 쿼리를 통해 조회하면 확인을 할 수 있는 장점이 있다.

MariaDB [(none)]> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

 

2.2 general_log & general_log_file

- general_log는 해당 로그를 사용 유무를 설정하는 파라미터이다. '1'은 ON을 의미하며 '0'은 OFF를 의미한다.

- general_log_file은 제너럴로그을 어떤 파일이름과 어떤 디렉토리에 기록할지 설정하는 파라미터입니다.

- 설정은 Global로 가능하지만 영구적으로 설정값을 유지하려면 /etc/my.cnf에 설정하면 된다. => 아래에 모아서 설정하겠습니다 :)

※ 아래와 같이 디테일한 설정화면은 다음 파라미터부터는 제외하겠습니다. 예시로 한번보셨으면 바로 이해되실거에요 :)

MariaDB [(none)]> show variables like 'general%';
+------------------+------------------------------------------+
| Variable_name    | Value                                    |
+------------------+------------------------------------------+
| general_log      | OFF                                      |
| general_log_file | mariadb-master.log                       |  # /var/lib/mysql 기본경로
+------------------+------------------------------------------+
 
MariaDB [(none)]> set global general_log = 1;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> set global general_log_file = '/var/log/mysql/general/mysql_general.log';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> show variables like 'general%';
+------------------+------------------------------------------+
| Variable_name    | Value                                    |
+------------------+------------------------------------------+
| general_log      | ON                                       |
| general_log_file | /var/log/mysql/general/mysql_general.log |
+------------------+------------------------------------------+
2 rows in set (0.00 sec)
 
[root@mariadb-master mysql]# cat /var/log/mysql/general/mysql_general.log
/usr/sbin/mysqld, Version: 10.2.32-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
200615 13:42:12    16 Query     show variables like 'general%'
200615 13:42:46    16 Query     set global general_log = 1
/usr/sbin/mysqld, Version: 10.2.32-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
200615 13:44:12    16 Query     set global general_log_file = '/var/log/mysql/general/mysql_general.log'
/usr/sbin/mysqld, Version: 10.2.32-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
200615 13:44:30    16 Query     show variables like 'general%'
200615 13:44:42    16 Quit 

 

2.3  slow_query_log & slow_query_log_file & long_query_time

- slow_query_log는 해당 로그를 사용 유무를 설정하는 파라미터이다. '1'은 ON을 의미하며 '0'은 OFF를 의미한다.

- slow_query_log_file은 슬로우로그을 어떤 파일이름과 어떤 디렉토리에 기록할지 설정하는 파라미터입니다.

- long_query_time은 쿼리가 수행되는 시간이 설정한 값 이상의 시간이 소요가 될 경우 로그가 기록되도록하는 설정 파라미터이다. 단위는 '초'입니다. /etc/my.cnf에 설정값을 넣어주면된다.

MariaDB [(none)]> show variables like 'slow_query%';
+---------------------+-------------------------+
| Variable_name       | Value                   |
+---------------------+-------------------------+
| slow_query_log      | ON                      |
| slow_query_log_file | mariadb-master-slow.log |
+---------------------+-------------------------+
2 rows in set (0.00 sec)
 
MariaDB [(none)]> show variables like 'long%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
 
MariaDB [(none)]> set global slow_query_log_file = '/var/log/mysql/slow/mysql_slow.log';
Query OK, 0 rows affected (0.00 sec)
 
# my.cnf 설정 
[root@mariadb-master ~]# vi /etc/my.cnf 
[mysqld]
long_query_time = 5
 
MariaDB [(none)]> show variables like 'slow_query%';
+---------------------+------------------------------------+
| Variable_name       | Value                              |
+---------------------+------------------------------------+
| slow_query_log      | ON                                 |
| slow_query_log_file | /var/log/mysql/slow/mysql_slow.log |
+---------------------+------------------------------------+
2 rows in set (0.00 sec)
 
MariaDB [(none)]> show variables like 'long%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

 

2.4  error log

- mysql을 실행할 때 --log-error=/var/log/mysql/.../... 과 같은 형식의 옵션을 주지않으면 hostname.err로 작성이 된다. 특정 디렉토리에 기록을 하고자한다면 꼭 옵션값을 넣어주자.

- 고정으로 로그의 위치를 잡으려면 /etc/my.cnf에 설정값을 넣어주면 된다.

[root@mariadb-master ~]# vi /etc/my.cnf 
[mysqld]
log-error = /var/log/mysql/error/mysql_error.log
 
MariaDB [(none)]> show variables like 'log_error';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| log_error     | /var/log/mysql/error/mysql_error.log |
+---------------+--------------------------------------+

 

2.5  binary log & relay log

- 이진데이터 로그는 복제와 데이터 복구에 사용되므로 매우 중요한 로그파일이라 할 수 있다.

- 이 곳에는 select이나 show와 같은 로그는 기록되지 않습니다.

- 데이터가 변경되는 데이터 조작명령가 기록된다고 생각하면된다.

- log-bin / max_binlog_size / expire_logs_days 와 같은 파라미터에 영향을 받는다.

[root@mariadb-master ~]# vi /etc/my.cnf 
[mysqld]
log-bin=/var/log/mysql/binary/mysql_bin.log   # 로그 위치
binlog_cache_size=2M                          # 로그 캐시 사이즈
max_binlog_size=512M                          # 로그 최대 사이즈
expire_logs_days=7                            # 보관 기관

 

※ 모든 로그파일은 앞서 지정한 디렉토리에 모두 잘 생성되어 기록이 되고 있을 것이다. :)