MYSQL튜닝기술mysqld_safe 3편

기술문서 : 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로그파일 활용—지정된 시간이상 질의를 계속하는 질의문 잡아내기

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

정상적으로 잘 돌아가던 MYSQL서버가 어느날 갑자기 "Warning...too many connections...."라는 에러 메시지와 함께 종료되어 버리는 경우가 있습니다. 이에 대한 근본적인 원인은 MYSQL의 환경변수에 설정되어 있는 “max_connections”의 값을 초과하는 쿼리가 발생하였기 때문입니다.  

이에대한 원인을 설명하기 위해서는 php에서 사용하는 MYSQL연결함수인 mysql_connect()와 mysql_pconnect의 차이점 그리고, mysql_close()함수에 대한 정확한 이해가 필요합니다.

간단히 설명해 보자면, mysql_connect()함수를 이용하여 MYSQL에 연결하였다면 해당 스크립트가 종료됨과 동시에 mysql_close()함수를 호출하지 않았다하더라도 자동으로 연결이 종료됩니다.

하지만, mysql_pconnect()함수는 해당스크립트가 종료된 후 mysql_close()함수가 호출되어도 연결이 끊어지지 않은 채로 계속 연결을 유지하고 있습니다.

따라서, 얼핏보기에는 "too many connections"라는 에러메시지는 mysql_pconnect()라는 함수의 사용때문에 발생하는 것 같지만 (물론 그런 이유도 있을 수 있지만) 근본적인 원인은 이와 다를 수 있습니다.  즉, MYSQL의 메뉴얼을 보면 mysql_connect()함수를 사용하면 해당스크립트의 종료와 함께 연결이 종료된다고 되어있지만 앞에서 설명드렸던 바와 같이 ./mysqladmin -u root -p processlist"라는 명령어를 통해서 살펴보면 해당 프로세스가 종료되지 않고 그대로 남아있다는 것을 알 수 있습니다.

[root@file bin]# pwd /usr/local/mysql/bin [root@file bin]# [root@file bin]# ./mysqladmin -u root -p processlist Enter password: ******** +----+------+-----------+-------+---------+------+-------+------------------+ | Id | User | Host      | db    | Command | Time | State | Info    | +----+------+-----------+-------+---------+------+-------+------------------+ | 2  | papa | localhost | papa  | Sleep   | 615   |       |       | | 4  | papa | localhost | papa  | Sleep   | 1025   |       |      | | 5  | papa | localhost | papa  | Sleep   | 109   |       |       | | 6  | papa | localhost | papa  | Sleep   | 384   |       |       | | 7  | papa | localhost | papa  | Sleep   | 455   |       |       | | 8  | root | localhost | mysql  | Sleep   | 347   |       |      | | 12  | root | localhost |       | Query   | 0    |       | show processlist | +----+------+-----------+-------+---------+------+-------+------------------+ [root@file bin]#

결론적으로 "mysqladmin -u -p variables"의 결과로서 볼 수 있었던 MYSQL의 환경변수값들 가운데 "wait_timeout"의 값만큼 서버에 그대로 연결을 유지한 채로 남아 있는 것입니다.  

따라서, 이것이 "too many connections"에러가 발생하는 궁극적인 원인이 되는 것입니다.

 

이를 해결하려면 MYSQL이 빠른 응답을 할 수 있도록 서버 하드웨어 사양(CPU, 메모리등)을 높이는 방법도 있겠지만 급하게 조치 해야하는 경우에는 MYSQL의 필요한 변수의 값을 보다 높게 설정하는 방법이 현명할 것입니다.

앞의 예에서 살펴보셨듯이 “./mysqladmin -u  root -p variables"라고 하면 MYSQL의 전체 환경변수의 값을 확인 할 수 있을 것입니다. 이들 MYSQL 환경변수 가운데 다음 3가지의 값을 현재보다 높게 설정함으로서 “too many connections”에러를 응급조치할 수 있습니다.

max_connections : MYSQL에 connect할 수 있는 최대 개수
table_cache     : MYSQL의 테이블캐쉬크기
wait_timeout    : MYSQL에 connect하여 쿼리를 지속할 수 있는 시간

즉, 다음과 같이 MYSQL의 환경변수 값을 확인하시기 바랍니다.

MYSQL의 max_connections변수값 확인

[root@file bin]# ./mysqladmin -u  root -p variables | grep max_connections max_connections                 100 [root@file bin]#

MYSQL의 table_cache변수값 확인

[root@file bin]# ./mysqladmin -u  root -p variables | grep table_cache table_cache                     64 [root@file bin]#

MYSQL의 wait_timeout변수값 확인

[root@file bin]# ./mysqladmin -u  root -p variables | grep wait_timeout wait_timeout                    28800 [root@file bin]#

 

위의 3가지 MYSQL환경변수값을 확인 하셨다면 보다 적절한 값을 설정하여 다음과 같이 MYSQL을 시작하시면 됩니다.

[root@file bin]# ./mysqld_safe -O max_connections=500 -O table_cache=256 -O wait_timeout=57600 & [root@file bin]#

위의 예는 max_connections값과 table_cache값, 그리고 wait_timeout값을 앞의 경우보다 더 높게 설정하여 MYSQL을 시작한 것입니다.  주의하실 것은 위의 변수들 가운데 wait_timeout값은 높이는 것만이 능사가 아니라는 점입니다. 즉, 경우에 따라서는 이 변수값을 낮추는 것이 효율적인 경우도 있습니다.

이와 같이 실행시킨 후에 다시 "./mysqladmin -u root -p variables"로 새로 설정된 변수값들이  변경되어 있음을 직접확인해 보시기 바랍니다.

 

그리고 이와 같은 방법으로 “too many connections”에러가 해결이 되었다면 재부팅시에 MYSQL을 시작하면서 매번 자동 적용을 위하여 /etc/rc.d/rc.local파일에 아래와 같이 MYSQL을 시작할 수 있도록 설정해 두시는 것도 좋은 방법이 됩니다.

/usr/local/mysql/bin/mysqld_safe -O max_connections=500 -O table_cache=256 -O wait_timeout=57600 &

 

 

 

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

MYSQL을 시스템의 root권한으로 실행시키게 되면 보안상 위험 할 수 있습니다. MYSQL의 root암호가 알려질 경우 시스템에 큰 위험이 생길 수 있기 때문입니다. 따라서, 가능하면 root로 실행시키는 것 보다는 MYSQL전용 관리계정을 만들어서 그 계정으로만 실행시키는 것이 좋을 것입니다.

필자의 경우에는 mysql이라는 계정으로 MYSQL을 관리하고 있습니다.  여기서 의미하는 mysql이라는 계정은 MYSQL의 계정이 아니라 MYSQL자체를 관리하기 위한 리눅스 시스템계정을 의미한다 점을 명심하시기 바랍니다.

이와 같이 mysql이라는 MYSQL 관리 시스템계정으로 MYSQL을 시작하려면 MYSQL 홈디렉토리(/usr/local/mysql/)에 존재하는 모든 파일들이  mysql의 소유로 되어 있어야 합니다.

따라서 mysql이라는 시스템계정을 useradd명령어로 생성하신 다음 아래와 같이 /usr/local/mysql/디렉토리 이하의 모든 파일들을 mysql로 변경하시기 바랍니다.

[root@file bin]# chown -R mysql:mysql /usr/local/mysql/

 

이와 같이 하신 다음에 mysql 이라는 계정으로 MYSQL을 시작한 예입니다.

 

[mysql@file bin]$ id uid=521(mysql) gid=521(mysql) groups=521(mysql) [mysql@file bin]$ ./mysqld_safe & Starting mysqld daemon with databases from /usr/local/mysql/data [mysql@file bin]$

 

이상과 같이 root가 아닌 MYSQL전용 관리계정으로 MYSQL을 시작하고 관리한다면 서버보안을 위한 훌륭한 선택이 될 수 있을 것입니다.

 

 

 

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

 

MYSQL뿐아니라 다른 모든 어플리케션의 경우도 그렇지만 로그파일의 활용가치는 에러원인의 결정적인 단서를 제공할 수 있습니다.  MYSQL에는 용도별도 다양한 로그파일을 활용할 수 있습니다.

 

필자의 경우 “mysqld_safe &”를 실행하였으나 실행이 되지않았을 경우 그 원인을 파악하기 위하여 가장 먼저 확인하는 것이 MYSQL의 메인로그파일(에러로그파일)입니다.  필자의 경험으로 보아 에러로그파일에서 에러원인의 90%는 거의 찾을 수 있었습니다. 이와 같이 로그파일의 분석만 잘 해도 장애나 에러원인분석 시간을 굉장히 단축할 수 있습니다.

 

이번 절에서는 MYSQL의 주된 로그파일에 대해서 설명을 드리도록 하겠습니다. 이어서 설명하는 BINARY로그, QUERY로그, SLOW로그도 모두 읽어보시기 바랍니다.

 

먼저, MYSQL을 다음과 같이 실행하면 MYSQL의 주로그가 데이터디렉토리에 호스트이름으로 생성되어 MYSQL의 시작과 종료등에 대한 로그기록과 장애원인등에 대해서 기록하게 됩니다.  MYSQL의 주로그는 특별한 옵션없이 그냥 기본으로 생성되어 기록되므로 로그기록을 위한 옵션이 존재하지 않습니다.

 

다음은 /usr/local/mysql/bin/디렉토리에서 “mysqld_safe &”명령으로 MYSQL을 시작하였습니다.

 

[root@file bin]# pwd /usr/local/mysql/bin [root@file bin]# [root@file bin]# ./mysqld_safe & [1] 3156 [root@file bin]# Starting mysqld daemon with databases from /usr/local/mysql/data   [root@file bin]#

 

위와 같이 실행하면 MYSQL의 데이터가 저장되는 디렉토리, 즉 필자의 경우 /usr/local/mysql/data/에는 “호스트명.err”으로된 MYSQL 메인로그(에러로그)가 생성되어 MYSQL의 시작/종료/장애등에 대한 로그와 에러원인등을 기록합니다.

 

다음은 현재 필자가 사용하고 있는 리눅스 서버의 호스트명을 확인하기 위하여 hostname을 실행한 것입니다.  아래 확인해보시는 바와같이 현재 서버의 호스트명은 “file.superuser.co.kr”입니다.

 

[root@file bin]# hostname file.superuser.co.kr [root@file bin]#

 

따라서 /usr/local/mysql/data/디렉토리에는 “file.superuser.co.kr.err”이라는 MYSQL 주로그(에러로그)가 생성되어 기록될 것입니다.

 

다음은 /usr/local/mysql/data/디렉토리의 에러로그파일을 확인한 것입니다.

 

 

[root@file data]# pwd /usr/local/mysql/data [root@file data]# [root@file data]# ls -l *.err 합계 20 -rw-rw----    1 mysql    mysql         969  2월 16 16:59 file.superuser.co.kr.err [root@file data]#

 

 

실무적인 예를 들어본다면 필자의 경우 어떤 문제에 대한 원인분석을 위하여 “tail -f /usr/local/mysql/data/file.superuser.co.kr.err”이라는 실시간 로그감시 터미널을 실행한 후에 MYSQL을 실행하거나 종료하면서 문제원인을 손쉽게 확인하곤 합니다. 

 

 

 

 

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