본문 바로가기

Technical/DBMS

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


반응형