【MySQL】keepalived+haproxy實現mysql的高可用與負載均衡

⒈haproxy檢測mysql(難點)

 

具體方法：將檢測指令碼提供給xinnetd管理,提供tcp埠（本人採用埠6033）

在/etc/xinetd.d下，建立mysqlchk檔案

[root@gz-22 xinetd.d]# pwd

/etc/xinetd.d

[root@gz-22 xinetd.d]# ls -l mysqlchk

-rw-r--r-- 1 root root 370 Jun  2 13:55 mysqlchk

[root@gz-22 xinetd.d]#

mysql檔案內容如下：

service mysqlcheck

{

        disable         = no

        flags           = REUSE

        socket_type     = stream

        port            = 6033

        wait            = no

        user            = root

        server          = /usr/local/etc/mysqlchk_status.sh#此為提供給xinetd管理的指令碼

       log_type        = FILE /dev/null

       log_on_failure  += USERID

        only_from       = 192.168.0.0/24

}

 

Mysqlchk_status.sh檢測mysql狀態的指令碼如下：

 

[root@gz-22 etc]# more mysqlchk_status.sh

#!/bin/bash

MYSQL_HOST="192.168.0.13"

MYSQL_PORT="3306"

MYSQL_USERNAME="root"

MYSQL_PASSWORD="tianqu"

MYSQL_PATH=mysql #特別注意，這裡的指令碼要採用絕對路徑，本人排錯一天時間

 

STATUS=`$MYSQL_PATH -u$MYSQL_USERNAME -p$MYSQL_PASSWORD -h${MYSQL_HOST} -e "REPAIR TABLE mysql.time_zone\G" 2>/dev/null |awk '/Msg_text/{print $2}'`

 

if [ "$STATUS" = "OK" ];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 ($MYSQL_HOST:$MYSQL_PORT) replication is running.\r\n"

/bin/echo -e "\r\n"

else

# mysql is down, 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 ($MYSQL_HOST:$MYSQL_PORT) replication  is *down*.\r\n"

/bin/echo -e "\r\n"

fi

[root@gz-22 etc]#

 

啟動xinetd服務，為讓配置生效

[root@gz-22 etc]# /etc/init.d/xinetd restart

 

我們檢視6033埠是否生效

 

[root@gz-22 etc]# curl -I http://192.168.0.13:6033

HTTP/1.1 200 OK

 

[root@gz-22 etc]#

也可以用telnet測試，如下：

[root@gz-22 etc]# curl -I http://192.168.0.13:6033

HTTP/1.1 200 OK

 

[root@gz-22 etc]# telnet 192.168.0.13 6033

Trying 192.168.0.13...

Connected to 192.168.0.13.

Escape character is '^]'.

HTTP/1.1 200 OK

Content-Type: Content-Type: text/plain

MySQL (192.168.0.13:3306) replication is running.

Connection closed by foreign host.

[root@gz-22 etc]#

 

如出現HTTP/1.1 200 OK字樣則表示ok

 

 

 

 

⒉Haproxy配置

[root@gz-22 etc]# more /usr/local/haproxy/etc/haproxy.conf

global

       log 127.0.0.1 local0 info

        maxconn 10000

        daemon

#     ulimit-n 65536

       chroot /usr/local/haproxy

        pidfile /usr/local/haproxy/run/haproxy.pid

        user haproxy

        group haproxy

 

defaults

        mode    http

        option  dontlognull

        retries 3

        option redispatch

        maxconn        10000

        contimeout      5000

        clitimeout      30000

        srvtimeout      30000

 

listen  admin_stats

#     stats hide-version

        bind  0.0.0.0:8082

        mode        http

        stats uri   /status

        stats realm     Global\ statistics

        stats auth  test:987(*&

 

 

listen  8684.cn-Msql-proxy 192.168.0.222:3307

        mode tcp

        balance roundrobin

        option httpchk OPTIONS * HTTP/1.1\n200

        server db112  192.168.0.13:3306 weight 1 check port 6033 inter 5s rise 2 fall 2

#埠6033即為上面xinetd定義的埠

        server db125  192.168.0.3:3306 weight 1 check port 6033 inter 5s rise 2 fall 2

[root@gz-22 etc]# 

 

 

 

3.Keepalived的配置

vrrp_script chk_haproxy {

       script "/usr/local/etc/check_haproxy.sh"     # cheaper than pidof

       interval 2                      # check every 2 seconds

       weight 2

}

vrrp_instance VI_1 {

    interface br0

    state MASTER

    virtual_router_id 233

    priority 100

    virtual_ipaddress {

        192.168.0.222/24 dev eth1 label eth1:1

    }

    track_script {

       chk_haproxy   # +2 if process is present

    }

    notify_backup "/etc/init.d/haproxy stop"

    notify_master "/etc/init.d/haproxy start"

}

 

 

關於vrrp_script的weight值，官方沒有文件可供參考，但是本人透過測試

vrrp_script在執行時，就會改變priority的值

Jun  5 14:26:49 node1 Keepalived_vrrp[3848]: VRRP_Script(chk_haproxy) succeeded

例如：主第一次啟動時，就會改變優先順序及priority=weight+priority，以後每interval間隔時間都會做這樣一個優先順序的累加。所以當你配置vrrp_script不生效時，你的考慮下vrrp設定是否正確

 

Vrrp_script中的指令碼：

[root@node1 ~]# more /usr/local/etc/check_haproxy.sh

#!/bin/bash

sleep 1

REL=`/usr/bin/curl -I -s -u test:987\(*\& http://192.168.0.3:8082/status |head -n1|awk '{print $2}'`

if [ -z "$REL" ] || [ "$REL" -ne 200 ];then

    exit 1

else

exit 0

fi

[root@node1 ~]#