Haproxy - Master/Backup 설정에서 MySQL alive check 방법

Haproxy 의 option mysqlchk 로 DB 의 가용 여부를 점검하는 데에 쓸 수 있으나
정상적인 점검이 정확하게 되지 않으며, MySQL의 connection을 잠식하는 문제점이 있다.
단순히 DB 접속 port만 감시하는 방법도 쓸 수 있으나, DB의 정상 가동여부를 체크하는 방법으로는 적합하지 않다.

xinetd service와, 간단한 db connection & query script를 이용
Assumption: MySQL home - /user/service/mysql
Thanks to Unai Rodriguez(script author) & Sysbible(http://sysbible.org/)

1. script 작성

# cd /user/service/mysql/bin; vi mysql_hc

#!/bin/bash
MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USERNAME="mysql_hc"
MYSQL_PASSWORD="password"

TMP_FILE="/user/service/mysql/var/mysqlchk.out"
ERR_FILE="/user/service/mysql/var/mysqlchk.err"

# a simple query
/user/service/mysql/bin/mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e "show databases;" > $TMP_FILE 2> $ERR_FILE

# Check the output. If it is not empty then everything is fine and we return
# something. Else, we just do not return anything.
#
if [ "$(/bin/cat $TMP_FILE)" != "" ]
then
    # mysql is fine, return http 200
    /bin/echo -e "HTTP/1.1 200 OK\r\n"
    /bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -e "\r\n"
    /bin/echo -e "MySQL is running.\r\n"
    /bin/echo -e "\r\n"
else
    # mysql is fine, return http 503
    /bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
    /bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -e "\r\n"
    /bin/echo -e "MySQL is *down*.\r\n"
    /bin/echo -e "\r\n"
fi

# chown root.mysql mysql_hc; chmod 744 mysql_hc
# mysql -uroot -ppassword
  > GRANT SELECT on mysql.* TO 'mysql_hc'@'%' \

  > IDENTIFIED BY 'password' WITH GRANT OPTION;
  > GRANT SELECT on mysql.* TO 'mysql_hc'@'localhost' \

  > IDENTIFIED BY 'password' WITH GRANT OPTION;

  > flush privileges;

# ./mysql_hc - 실행 결과 확인(HTTP/1.x 200 OK)


2.  vi /etc/services 에 아래 라인 추가

mysql_hc    28001/tcp                      # mysql_hc - mysql health checker

3. vi /etc/xinetd.d/mysql_hc

# default: on

# description: mysql_hc

service mysql_hc

{

        flags           = REUSE

        socket_type     = stream

        port            = 28001

        wait            = no

        user            = root

        server          = /user/service/mysql/bin/mysql_hc

        log_on_failure  += USERID

        disable         = no

        only_from       = 0.0.0.0/0      # recommended to put the IPs that need

                                                 # to connect exclusively (security purposes)

        per_source      = UNLIMITED

}

 
# service xinetd restart

4. 정상 작동 test

# telnet 10.10.0.10 28001

Trying 10.10.0.10...
Connected to 10.10.0.10.
Escape character is '^]'.
HTTP/1.1 200 OK
Content-Type: Content-Type: text/plain

MySQL is running.

Connection closed by foreign host.

5. HaProxy configuration 에 설정 추가

listen RDB-1

        bind 10.10.0.71:28000

        mode tcp

        option httpchk

        timeout connect 5m

        timeout client 30m

        timeout server 30m

        server RDB-1 10.10.0.10:3306 check port 28001 inter 5s rise 1 fall 3

        server RDB-2 10.10.0.30:3306 check port 28001 inter 5s rise 1 fall 3 backup

        log global