【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 ~]#

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30221425/viewspace-1690180/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30221425/viewspace-1690180/