MYSQL튜닝기술mysqld_safe 4편

기술문서 : MYSQL튜닝기술mysqld_safe 편<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

 

 

 

 

ㅇ 제작자 : 리눅스포털(www.superuser.co.kr) 수퍼유저코리아 <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><?xml:namespace prefix = st2 ns = "urn:schemas:contacts" />박성수   ㅇ 본강좌의 자세한 정보 : http://www.superuser.co.kr/linuxcommandbible/

 

 

 

 

 

 

 

 

 

 

----------   목                  차  -------------

 

1  : MYSQL 데이터베이스를 실행하는 mysqld_safe에 대하여

2  : mysqld_safe으로 MYSQL 실행하기

3  : MYSQL 시작시 포트번호 지정하여 시작하기

4  :  MYSQL 시작시 grant table권한없이 실행하기

5  : MYSQL의 root패스워드를 잊어버렸을 때 복구하기

6  : 여러가지 옵션들 지정하여 MYSQL 실행하기

7  :“too many connections” 에러발생시 mysqld_safe의 실행옵션으로 해결하는 방법

8  : MYSQL 보안을 위한 MYSQL전용관리계정으로 MYSQL 시작하기

9  : MYSQL 주로그(에러로그)파일 관리하기

10  : MYSQL의 쿼리로그를 남기려면

11  : MYSQL 바이너리(BINARY)로그 파일 활용

12  : MYSQL 튜닝 및 원인분석을 위한 SLOW로그파일 활용—지정된 시간이상 질의를 계속하는 질의문 잡아내기

 

 

 

 

 

 

 

 

 

 

 

 

 

 

10 : MYSQL의 쿼리로그를 남기려면

 

앞의 예에서 보았던 MYSQL의 주로그(에러로그)에는 웹프로그램이나 MYSQL접속후에 사용되는 SQL쿼리(Query)에 대한 로그는 기록되지 않습니다.  만약 쿼리로그를 기록하게 된다면 로그의 크기가 순식간에 커질 수 있으며, 어느 순간에 파일시스템이 꽉 차버리는 시스템장애가 발생할지도 모르기 때문에 MYSQL에서 SQL쿼리로그는 필요할 경우에만 지정된 로그파일에 기록할 수 있도록 선택적으로 사용할 수 있습니다.

 

MYSQL데이터베이스를 사용하는 프로그래머의 입장에서 본다면 SQL쿼리로그가 종종 필요할 경우가 있을 것입니다. 이때 서버관리자에게 SQL쿼리로그를 기록하게 해달라고 요청할 것이며 이런 경우에 서버관리자의 입장에서 “네, 알겠습니다.’라고 준비된 대답을 할 수 있어야 할 것입니다.  아래 설명을 참고하여 그 답을 준비하도록 하십시요.

 

MYSQL 쿼리로그를 기록하려면 “mysqld_safe”으로 MYSQL을 실행하실 때에 다음과 같은 옵션을 추가로 사용하시면 됩니다.

 

쿼리로그옵션형식 : --log=쿼리로그파일명

 

위와 같이 쿼리로그의 형식을 사용하시면 MYSQL의 데이터디렉토리 (예 : /usr/local/mysql/data/)에 “쿼리로그파일명”으로 지정된 로그파일이 생성되며 SQL쿼리로그를 기록하게 됩니다. 

 

만약 위의 형식에서 “쿼리로그파일명”을 생략하고 “--log”라고만 한다면 “호스트명.log”라는 파일명으로 쿼리로그가 기록될 것입니다.

 

아래의 예는 /usr/local/mysql/bin/디렉토리에서 쿼리로그를 기록하기 위하여 아래와 같이 실행한 것입니다.

 

[root@file bin]# pwd /usr/local/mysql/bin [root@file bin]# [root@file bin]# ./mysqld_safe --log=MYSQL_QUERY_LOG & [1] 2882 [root@file bin]# Starting mysqld daemon with databases from /usr/local/mysql/data   [root@file bin]#

 

즉, 위의 예에서 쿼리로그파일명으로 MYSQL_QUERY_LOG라고 지정을 하였기 때문에 /usr/local/mysql/data/디렉토리에는 MYSQL_QUERY_LOG라는 파일명이 생성되어 쿼리로그를 기록할 것입니다.

 

다음은 /usr/local/mysql/data/디렉토리에서 위의 예에서 지정한 쿼리로그를 확인한 것입니다.

 

[root@file data]# pwd /usr/local/mysql/data [root@file data]# [root@file data]# ls -l MYSQL_QUERY_LOG -rw-rw----    1 mysql    mysql         157  2월 16 16:09 MYSQL_QUERY_LOG [root@file data]#

 

 

참고로 MYSQL의 로그파일을 특별한 위치에 저장하고자 한다면 다음과 같이 디렉토리경로와 함께 MYSQL을 시작하시면 됩니다.

 

[root@file bin]# ./mysqld_safe --log=/디렉토리경로/MYSQL_QUERY_LOG &

 

위와 같이 하시면 지정된 “/디렉토리경로/에 “MYSQL_QUERY_LOG”파일이라는 쿼리로그파일을 저장하게될 것입니다.

 

 

 

11 : MYSQL 바이너리(BINARY)로그 파일 활용

 

다음은 MYSQL의 데이터가 변경될 때에 그 내역을 기록하는 로그파일에 대한 설명입니다. 예전에는 MYSQL의 데이터변경로그를 UPDATE로그라고 하여 텍스트형태로 기록을 하였습니다. 하지만 현재는 MYSQL자체 보안과 실행속도 향상을 위하여 바이너리(BINARY)형태로 기록을 하며 로그파일명 또한 BINARY로그라고 부르고 있습니다.

 

즉, MYSQL의 바이너리(BINARY) 로그파일은 MYSQL의 변경된 데이터내역을 기록하기 위한 목적으로 사용되며 다른 로그파일과는 달리 텍스트형태가 아닌 BINARY로 기록하는 이유는 로그를 기록하는 속도를 향상시켜 실행속도를 빠르게 하기위한 것과 데이터의 변경기록을 아무나 볼 수 없도록하여 자체 보안을 위한 것이 그 목적이라고 할 수 있습니다.

 

바이너리로그옵션형식 : --log=바이너리로그파일명

 

위와 같이 바이너리로그의 형식을 사용하시면 MYSQL의 데이터디렉토리 (예 : /usr/local/mysql/data/)에 “바이너리로그파일명”으로 지정된 바이너리로그파일이 생성되며 MYSQL데이터의 변경내역을 기록합니다.

 

다음은 /usr/local/mysql/bin/디렉토리에서 바이너리로그를 기록하기 위하여 아래와 같이 실행한 것입니다.

 

[root@file bin]# pwd /usr/local/mysql/bin [root@file bin]# [root@file bin]# ./mysqld_safe --log-bin=MYSQL_BINARY_LOG & [1] 2991 [root@file bin]# Starting mysqld daemon with databases from /usr/local/mysql/data   [root@file bin]#

 

즉, 위의 예에서 바이너리로그파일명으로 MYSQL_BINARY_LOG라고 지정을 하였기 때문에 /usr/local/mysql/data/디렉토리에는 MYSQL_BINARY_LOG라는 파일명이 생성되어 바이너리로그를 기록할 것입니다.

 

다음은 /usr/local/mysql/data/디렉토리에서 위의 예에서 지정한 바이너리로그를 확인한 것입니다.

 

[root@file data]# pwd /usr/local/mysql/data [root@file data]# [root@file data]# ls -l MYSQL_QUERY_LOG -rw-rw----    1 mysql    mysql         157  2월 16 16:09 MYSQL_QUERY_LOG [root@file data]#

 

 

참고로 MYSQL의 바이너리파일을 특별한 위치에 저장하고자 한다면 다음과 같이 디렉토리경로와 함께 MYSQL을 시작하시면 됩니다.

 

[root@file bin]# ./mysqld_safe --log-bin=/디렉토리경로/MYSQL_BINARY_LOG &

 

위와 같이 하시면 지정된 “/디렉토리경로/에 “MYSQL_BINARY_LOG”파일이라는 바이너리로그파일이 저장될 것입니다.

 

 

 

12 : MYSQL 튜닝 및 원인분석을 위한 SLOW로그파일 활용(지정된 시간이상 질의를 계속하는 질의문 잡아내기)

 

이번에 설명드릴 MYSQL의 로그파일은 MYSQL 질의속도에 대한 튜닝을 하기위한 결정적인 단서를 잡을 수 있는 아주 훌륭한 방법이 될 수 있습니다.

 

즉,  MYSQL의 질의에 대한 응답속도가 현저하게 떨어지거나 MYSQL 데이터베이스와 연동되는 특정 웹프로그램이 시스템자원을 현저하게 많이 차지하는 등의 문제가 발생하였다면 이번 예에서 설명드리는 SLOW로그에서 어떤 웹프로그램이 문제인가를 파악해 보시기 바랍니다.

 

즉, SLOW로그란 MYSQL의 환경변수인 “long_query_time”의 설정시간(초단위) 이상의 쿼리시간을 가지는 특정SQL 쿼리문에 대한 로그만을 기록하는 로그파일입니다.

 

따라서 SLOW로그파일을 활용한다면 어떤 웹프로그램이 쿼리시간(실행시간)을 많이 차지하는가를 확인 할 수 있습니다.  즉,  어느날 갑자기 MYSQL의 응답속도가 현저하게 떨어진다면 SLOW로그로 쿼리응답속도가 일정시간 이상 지속되는 쿼리문을 찾아서 조치하면 될 것입니다.

 

SLOW쿼리옵션형식 :  --log-slow-queries=슬로우쿼리파일명

 

아래는 이와 같은 MYSQL의 SLOW로그파일의 활용에 대한 설명입니다.

 

먼저, /usr/local/mysql/bin/디렉토리에서 “mysqld_safe”으로 MYSQL을 실행시킬 때에  “--log-slow-queries=”옵션을 사용하여 SLOW쿼리로그를 기록하도록 지정 하였습니다.

 

[root@file bin]# pwd /usr/local/mysql/bin [root@file bin]# [root@file bin]# ./mysqld_safe --log-slow-queries=SLOW_QUERY_LOG & [1] 3116 [root@file bin]# Starting mysqld daemon with databases from /usr/local/mysql/data   [root@file bin]#

 

즉, 위의 예에서 SLOW로그파일명으로 SLOW_QUERY_LOG라고 지정을 하였기 때문에 /usr/local/mysql/data/디렉토리에는 SLOW_QUERY_LOG라는 파일명이 생성되어 SLOW쿼리로그를 기록할 것입니다.

 

다음은 /usr/local/mysql/data/디렉토리는 위의 예에서 지정한 SLOW쿼리로그를 확인한 것입니다.

 

[root@file data]# pwd /usr/local/mysql/data [root@file data]# [root@file data]# ls -l SLOW_QUERY_LOG -rw-rw----    1 mysql    mysql         157  2월 16 16:44 SLOW_QUERY_LOG [root@file data]#

 

그리고 위의 “SLOW_QUERY_LOG”파일에는 MYSQL의 환경변수 “long_query_time”에 초단위로 지정된 시간보다 초과하는 쿼리문만을 기록합니다.  아래는 MYSQL의 환경변수 “long_query_time”의 값을 확인하는 방법을 나타낸 것입니다. 즉, mysqladmin이라는 MYSQL유틸리티를 이용하여 MYSQL의 환경변수들을 모두 출력하여 grep으로 “long_query_time”에 대한 부분만을 확인한 것입니다. (mysqladmin에 대한 자세한 설명은 이 책의 “mysqladmin”편을 참조하시기 바랍니다.)

 

[root@file data]# pwd /usr/local/mysql/data [root@file data]# [root@file bin]# ./mysqladmin -u root -p variables | grep long_query_time Enter password: ******** | long_query_time  |   10   | [root@file bin]#

 

위의 결과를 보시면 “long_query_time”의 변수값이 10(초)로 설정되어 있기 때문에 MYSQL의 SQL쿼리문이 10초이상 지속될 때에는 그 쿼리문의 내역을 /usr/local/mysql/data/디렉토리에 있는 SLOW_QUERY_LOG파일에 기록할 것입니다.  따라서 이 로그파일을 확인해 본다면 MYSQL의 과부하를 일으키는 웹프로그램파일(SQL쿼리문)을 찾을 수 있을 것입니다.

 

 

결론적으로 이 SLOW로그파일을 활용하시면 어떤 쿼리문에서 MYSQL 과부하를 일으키는지 정확하게 알 수 있기 때문에 필자의 경우에 MYSQL 튜닝방법으로 굉장히 애용하는 방법입니다.

 

 

 

ㅇ 제작자 : 리눅스포털(www.superuser.co.kr) 수퍼유저코리아 박성수   ㅇ 본강좌의 자세한 정보 : http://www.superuser.co.kr/linuxcommandbible/