mysql 로그 확인 및 저장하기

1.   .mysql_history 파일을 확인

홈디텍토리에 있는 .mysql_history 파일에는 

사용자가 사용했던 query들이 아래와 같이 저장되어 있어 확인이 가능하다. 

로그인된 사용자 별로 확인이 가능하고 전체 sql command 확인은 불가능하다. 

show global status like 'open_%';

show variables like 'max_connections';

show status like 'Max_used_connections';

show global variables like 'open%';

2. mysql 로그 저장하도록 설정하기 

mysql server를 재시작하지 않아도 설정이 변경 가능하다. 

다만 서버를 재시작할 경우 같은 설정을 다시 해줘야한다. 

참고로 대용량의 쿼리 작업을 할 경우 로그가 저장되도록 설정되어 있으면 

속도가 느려질 수도 있으니 주의해야한다. 

# 로그 설정을 확인한다.

show variables where Variable_name in ('version', 'log', 'general_log');

+---------------+-------------------------+

| Variable_name | Value                   |

+---------------+-------------------------+

| general_log   | OFF                     |

| log           | OFF                     |

| version       | 5.5.46-0ubuntu0.14.04.2 |

+---------------+-------------------------+

3 rows in set (0.35 sec)

# OFF로 되어있는 general_log 설정을 1로 설정하여 로그를 저장하도록 변경한다. 

mysql> set global general_log = 1;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables where Variable_name in ('version', 'log', 'general_log');

+---------------+-------------------------+

| Variable_name | Value                   |

+---------------+-------------------------+

| general_log   | ON                      |

| log           | ON                      |

| version       | 5.5.46-0ubuntu0.14.04.2 |

+---------------+-------------------------+

3 rows in set (0.01 sec)

# 저장한 로그를  mysql DB general_log 테이블에 저장하게 한다. 

mysql>  SET GLOBAL log_output = 'TABLE';

# slow log도 같이 설정해둔다. 

mysql> SET GLOBAL slow_query_log = 'ON';

Query OK, 0 rows affected (0.00 sec)

이렇게 저장된 로그는 아래와 같이 general_log 테이블에서 확인이 가능하다. 

<추가>

최근에 mysql 대용량의 쿼리 작업을 하다 반응 속도가 넘 느려서 

혹시 저번 로그 설정해둔 것 때문인가 하고 

관련 테이블 사이즈를 확인해보았다. 

mysql> SELECT TABLE_NAME AS "Tables",

    ->                      round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"

    -> FROM information_schema.TABLES

    -> WHERE table_schema = "mysql"

    -> ORDER BY (data_length + index_length) DESC;

+---------------------------+------------+

| Tables                    | Size in MB |

+---------------------------+------------+

| help_topic                |       0.48 |

| help_keyword              |       0.12 |

| help_relation             |       0.03 |

| db                        |       0.01 |

| proxies_priv              |       0.01 |

| proc                      |       0.00 |

| help_category             |       0.00 |

| tables_priv               |       0.00 |

| columns_priv              |       0.00 |

| procs_priv                |       0.00 |

| user                      |       0.00 |

| user.tmp                  |       0.00 |

| host                      |       0.00 |

| event                     |       0.00 |

| servers                   |       0.00 |

| plugin                    |       0.00 |

| ndb_binlog_index          |       0.00 |

| time_zone_transition_type |       0.00 |

| time_zone_transition      |       0.00 |

| time_zone_name            |       0.00 |

| func                      |       0.00 |

| time_zone_leap_second     |       0.00 |

| time_zone                 |       0.00 |

| slow_log                  |       0.00 |

| general_log               |       0.00 |

+---------------------------+------------+

25 rows in set (0.10 sec)

???? ????????????

테이블 사이즈가 0으로 나와서 확인해보니 

sql에서 로그 csv파일로 접근만 해서 읽어오는 것이라고 한다. 

즉 파일로 가야 제대로 로그 사이즈를 확인할 수 있는것이다. 

The general_log table by default uses the CSV engine, which is literally just a full-blown CSV file on your drive, but can be 

accessed via SQL. This means its size limit is the size limit of files on your file system.

(참고: http://stackoverflow.com/questions/9343001/is-my-mysql-general-log-table-getting-too-big)

그래서 로그가 있는 디렉토리로 갔더니 로그 파일 사이즈만 11기가 OTL 

query문 하나하나마다 이 파일에 저장이 되고 있으니 

아무래도 이녀석이 범인(아니 내가;)인것 같다 ㅠㅠ

/var/lib/mysql/mysql# ll -h *log*

-rw-rw---- 1 mysql mysql   35  4월 15 17:06 general_log.CSM

-rw-rw---- 1 mysql mysql  11G  5월  9 15:27 general_log.CSV

-rw-rw---- 1 mysql mysql 8.6K 11월  3  2015 general_log.frm

-rw-rw---- 1 mysql mysql 8.6K 11월  3  2015 ndb_binlog_index.frm

-rw-rw---- 1 mysql mysql    0 11월  3  2015 ndb_binlog_index.MYD

-rw-rw---- 1 mysql mysql 1.0K 11월  3  2015 ndb_binlog_index.MYI

-rw-rw---- 1 mysql mysql   35  4월 16 01:01 slow_log.CSM

-rw-rw---- 1 mysql mysql 127K  5월  9 15:21 slow_log.CSV

-rw-rw---- 1 mysql mysql 8.8K 11월  3  2015 slow_log.frm

root@stock:/var/lib/mysql/mysql#

일단 general log 설정을 off로 변경하고 관련 파일은 내용을 지웠다. 

mysql> set global general_log = 0;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables where Variable_name in ('version', 'log', 'general_log');

+---------------+-------------------------+

| Variable_name | Value                   |

+---------------+-------------------------+

| general_log   | OFF                     |

| log           | OFF                     |

| version       | 5.5.46-0ubuntu0.14.04.2 |

+---------------+-------------------------+

3 rows in set (0.01 sec)

Unless you're troubleshooting something, in my opinion there is no reason to keep the general log turned on. I would only turn it on if I were troubleshooting an issue, and then I'd turn it off when I was finished. I would suppose that it would improve performance with it off, especially if it's currently writing to a 20 GB logfile.

After you've turned off the general log, just delete that log from /var/lib/mysql

검색하다 찾은 troubleshooting을 하는 것이 아니면     

general log를 켜지 말라는 조언이 꽤 와닿았다. 

(참고: https://forums.cpanel.net/threads/large-general-log-file-how-to-clean-up-safely-and-stop-it-reoccuring.278392/) 

참고: http://www.koreaidc.com/bbs/set_view.php?b_name=idcpds&w_no=184