mysql log 설정 총 정리

 <MySQL Log 종류>

 

1. 에러로그 - Error_log

2. 제너럴 로그 - General_log

3. 슬로우쿼리 로그 - Slow query_log

4. 바이너리 로그 - Binary log

5. 릴레이 로그 - Relay log

 

 

1. Error log 

- /etc/my.cnf에 따로 지정을 해주지 않으면 보통 /var/log/mysqld.log 로 저장

- /etc/my.cnf 에 "log-error=경로.log" 로 지정 가능

- 또는 mysql data 디렉터리에 .err 형식으로 저장

- MySQL 구동과 모니터링, Query 에러에 관련된 메세지를 포함

 

 

2. General log 설정 ( mysql command history log )

 

- MySQL에서 실행되는 전체 쿼리에 대하여 General log를 활성화 시켜서 저장 가능

- General log를 활성화하면 MySQL이 쿼리 요청을 받을 때 곧 바로 General log에 기록

 

* 실시간으로 general log 활성화하기 ( mysqld 재시작 없이 )

# mysql -u root -p

:

 - general log 상태 확인

 

mysql> show variables like 'general%';

mysql> show variables where Variable_name in ('version', 'log', 'general_log');
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| general_log   | OFF        |
| log           | OFF        |
| version       | 5.1.73-log |
+---------------+------------+
3 rows in set (0.00 sec)

 

- general log 활성화

mysql> set global general_log = ON;

mysql> set global general_log = 1;
Query OK, 0 rows affected (0.01 sec)

 

mysql> show variables where Variable_name in ('version', 'log', 'general_log');
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| general_log   | ON         |
| log           | ON         |
| version       | 5.1.73-log |
+---------------+------------+
3 rows in set (0.00 sec)

 

- general log 비활성화

mysql> set global general_log = OFF;

 

mysql> quit

 

* mysql data 디렉터리로 이동

 

# cd /usr/local/mysql/data

# tail -f localhost.log &
[1] 1983
# /usr/local/mysql/libexec/mysqld, Version: 5.1.73-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
150122  5:58:31     1 Query     show variables where Variable_name in ('version', 'log', 'general_log')
150122  6:00:04     1 Quit

 

# touch /var/log/mysql_history.log

# chown mysql.mysql /var/log/mysql_history.log

 

- general log 영구적으로 적용 및 파일 생성

# vi /etc/my.cnf

general_log = 1

general_log_file = /var/log/mysql_history.log

 

# /etc/init.d/mysqld restart

 

 

 

 

3. Slow Query 설정

 

- slow query log는 long_query_time에 설정된 시간 이상을 소요한, 정상적으로 완료된 쿼리를 모두 기록

- general log는 query 요청을 받고 바로 입력하기 때문에 에러가 발생한 쿼리도 입력이 되지만, slow query log는 쿼리가 완료된 실행된 시간 까지 입력하기 때문에 실행 도중 에러가 발생한 쿼리에 대해서는 로그로 남기지 않는다.

- MySQL 프로세스가 CPU/Memory 자원을 비정상적으로 많이 사용하여 서버의 load average가 급증하거나, 웹페이지 로딩 속도가 현저히 지연될 경우 확인

* slow 관련 설정은 Dynamic으로 set global 명령으로 재시작 없이 수정 가능합니다. 

 

 

 

< slow 로그 설정 확인 방법 >

 

mysql> show variables like 'slow%'; 

mysql> show variables like 'long%'; 

mysql> show variables like 'log%'; 

 

 

 

< slow 로그 ON, OFF >

 

> set global slow_query_log = ON;

> set global slow_query_log = OFF;

 

# vi /etc/my.cnf

slow_query_log_file = /datadir/serverhostname-slow.log

 

 

 

< 롱 쿼리 타임 설정 >

 

> set global long_query_time = 10;

 

# vi /etc/my.cnf

long_query_time = 10.000000

-> 10초 이상 query 를 기록하는 설정

위의 의미는 쿼리타임이 10초를 초과하는 쿼리에 대해 /temp/mysql-slow.log 파일에 로그를 남기라는 의미 입니다.

 

 

 

<로그 출력 타입 설정 >

 

log_output = FILE

 

 

< 인덱스를 사용하지 않는 쿼리 추출용 옵션 변수 ON, OFF >

 

log_queries_not_using_indexes = OFF

 

 

 < 파일 내용 >

Time : 쿼리가 종료된 시간

Query_time : 쿼리가 실행된 시간

Lock_time : MySQL 엔진 레벨의 테이블 잠금 대기시간 ( 테이블 Lock 걸린 시간 )

Row_sent : 클라이언트로 보낸 실제 처리 건수 ( 쿼리 처리 결과 Row 수 )

Row_examined : 쿼리 처리를 위해 접근한 레코드 건수 ( 쿼리 처리 대상의 Row 수 )

 

< slow query 설정 >

 

# touch /var/log/slow-query.log

# chown mysql.mysql /var/log/slow-query.log

 

# vi /etc/my.cnf

 

 

[mysqld]

 

 

    log-slow-queries = /usr/local/mysql/var/mysql-slow.log

    long_query_time = 3    --> 위 내용은 쿼리 타임이 '3초'를  초과 하는 쿼리에 대해 /usr/local/mysql/var/mysql-slow.log 파일에 기록 한다는 뜻입니다.

 

 

    log-slow-queries = 로그 파일명

    long_query_time = 쿼리 타임

 

 

 

# /etc/init.d/mysqld restart

 

my.cnf에 설정 및 mysql을 리스타트 한 뒤 운영 하다 보면 slow-query-log가 남게 됩니다.

쿼리의 분석 방법은 EXPLAIN 을 이용하여 분석을 하면 됩니다.

 

explain 을 사용하여 체크

 

(사용법)
      EXPLAIN tbl_name
   or EXPLAIN SELECT select_options

 

 

 

 

 

< 종합 >

slow_query_log = ON show variables like 'log_output'; set global log_output='FILE'; set global log_output='TABLE'; set global log_output='TABLE,FILE'; 

 

 

 

* 설정 확인

mysql> show global variables like '%slow%'; | log_slow_queries | OFF | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /data/dbrepl/m3-31-slow.log | +---------------------+-----------------------------+ mysql> show global variables like '%long%'; +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ mysql> set global log_slow_queries = 1; mysql> show global variables like '%slow%'; | log_slow_queries | ON | | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | /data/dbrepl/m3-31-slow.log | +---------------------+-----------------------------+ mysql> show global variables like '%log%'; mysql> show variables where Variable_name in('version','log','general_log','general_log_file','log_ourput'); +------------------+-----------------------------------+ | Variable_name | Value | +------------------+-----------------------------------+ | general_log | OFF | | general_log_file | /data/dbrepl/m3-31.log | | log | OFF | | version | 5.1.56-ndb-7.1.13-cluster-gpl-log | +------------------+-----------------------------------+ mysql> set global general_log = 1; mysql> show variables where Variable_name in('version','log','general_log','general_log_file','log_ourput');

| general_log | ON | | general_log_file | /data/dbrepl/m3-31.log | | log | ON | | version | 5.1.56-ndb-7.1.13-cluster-gpl-log | +------------------+-----------------------------------+

show variables like 'log_output'; set global log_output='FILE'; set global log_output='TABLE'; set global log_output='TABLE,FILE';
select count(*) from mysql.general_log;
set global max_connections=200;
show variables like '%CONNECT%';
show processlist\G

 

 

4. Binary log & Relay log

- MySQL 쿼리를 수행하면서 쌓는 로그, 추 후 트랜잭션하여 시점 복구 등을 수행하는 역할

- 일반적으로 바이너리 로그는 마스터에서, 릴레이 로그는 슬레이브에서 생성되며, 포맷과 내용은 동일

- slave가 또 다른 slave의 Master가 되어야 할 경우   log_slave_updates   옵션을 ON으로 설정하여 slave에서 바이너리 로그가 생성되도록 설정

- 바이너리 로그는 mysqlbinlog 프로그램을 이용하여 사용자가 읽을 수 있는 파일로 변환할 수 있다.

ex) 

mysqlbinlog --database=DB명 mysql-bin.000001 > 생성파일명.sql

[몇가지 옵션]
--database=DB명
--start-datetime="2014-09-09 10:00:00" --stop-datetime="2014-09-10 10:00:00"
--start-position=100 --stop-position=200

 

< 바이너리 파일을 통한 쿼리 실행 >

 

- 바이너리 파일을 MySQL에서 실행

 

mysqlbinlog mysql-bin.000001 | mysql -u root -p
sql 파일로 생성 한 뒤 실행
mysqlbinlog mysql-bin.000001 > test.sql
SOURCE test.sql

 

 

< bin log 관리 > - Binary log 관리

 

- bin log 설정

 

# vi /etc/my.cnf

 

 

log-bin=/home/mysql_log/bin_log/bin                 # 바이너리 로그 저장 설정 및 저장할 디렉토리 지정
binlog_cache_size = 2M                                     # 바이너리 로그 cache 사이즈 
max_binlog_size = 50M                                     # 바이너리 로그 최대 파일 사이즈 
expire_logs_days = 10                                         # 보관기간

 

 

- Binary log 삭제 : MySQL Binary Log 는 add, delete, insert, update 등의 query 가 저장되어 있는 파일로서 MySQL 을  설치하게 되면 기본적으로 MySQL Binary Log 가 생성됩니다.

 Binary Log 를 쌓지 않아도 되는 MySQL 구동 환경에서는 Binary Log 를 삭제하므로, 디스크 공간 확보 가능합니다.

 

ex1) MySQL Replication 환경에서 지우기

 - MySQL Replication MASTER 서버
    shell> mysql -u root -p
    mysql> RESET MASTER;

 

  - MySQL Replication SLAVE 서버
    shell> mysql -u root -p
    mysql> RESET MASTER;

 

 

ex2) MySQL Binary Log sequence number 또는 특정 일자로 지우기

> show binary logs;

| mysql-bin.000182 | 1073741883 |
| mysql-bin.000183 | 1073741988 |
| mysql-bin.000184 | 1073741999 |
| mysql-bin.000185 | 1073741927 |
| mysql-bin.000186 | 1073742098 |

 

> purge master logs to 'mysql-bin.000186';                    --> 해당 파일 이전의 파일들을 모두 삭제

 

> set global expire_logs_day=2;                                    --> 바이너리 로그를 저장할 주기 설정

 

ex3) mysqladmin flush-logs 명령어를 통해서 지우기

 shell> mysqladmin -u root -p flush-logs

 

ex4) MySQL Binary Log 생성을 방지 하는 방법

/etc/my.cnf 파일에서 아래 라인을 주석 처리

  log-bin

 

ex5) MySQL Binary Log를 특정 1주일까지만 생성 및 보관하기

  /etc/my.cnf 파일에서 아래 라인을 추가
  expire_logs_days = 7

 

 

 

6. logrotate 활용하여 로그 관리 하기

 

# vi /etc/logrotate.d/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
}

 

 

 

# vi /etc/logrotate.d/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
}

 

# /etc/init.d/crond restart

# chkconfig --list | grep crond

# chkconfig crond on

# ps -ef | grep cron

 

 MySQL Binary Log 는 add, delete, insert, update 등의 query 가 저장되어 있는 파일로서 MySQL 을  설치하게 되면 기본적으로 MySQL Binary Log 가 생성됩니다.
 Binary Log 를 쌓지 않아도 되는 MySQL 구동 환경에서는 Binary Log 를 삭제하므로, 디스크 공간
 여유 공간을 확보할 수 있는데, MySQL Binary Log 를 지우는 방법에 대해서 알아보겠습니다.

 

 

1. MySQL Replication 환경에서 지우기

  - MySQL Replication MASTER 서버
    shell> mysql -u root -p
    mysql> RESET MASTER;

  - MySQL Replication SLAVE 서버
    shell> mysql -u root -p
    mysql> RESET MASTER;

 

2. MySQL Binary Log sequence number 또는 특정 일자로 지우기

  shell> mysql -u root -p
  mysql> PURGE BINARY LOGS TO 'mysql-bin.000015';
  shell> mysql -u root -p
  mysql> PURGE BINARY LOGS BEFORE '2009-05-01 00:00:00';

 

3. mysqladmin flush-logs 명령어를 통해서 MySQL Binary Log 지우기

   shell> mysqladmin -u root -p flush-logs

 

4. MySQL Binary Log 생성을 방지하는 방법

  /etc/my.cnf 파일에서 아래 라인을 주석 처리
  log-bin

 

5. MySQL Binary Log 를 특정 1주일까지만 생성 및 보관하기
  /etc/my.cnf 파일에서 아래 라인을 추가
  expire_logs_days = 7