HaProxy实现mysql负载均衡

一 HaProxy介绍

Haproxy提供高可用性、负载均衡以及基于TCP和HTTP应用的代理，支持虚拟主机，它是免费、快速并且可靠的一种解决方案。Haproxy特别适用于那些负载特大的web站点，这些站点通常又需要会保持或七层处理。Haproxy运行在当前的硬件上，完全可以支持数以万计的并发连接。并且它的运行模式使得它可以很简单安全的整合进您当前的架构中，同时可以保护你的web服务器不被暴露到网络上。

二 HaProxy安装

2.1 实验环境

角色	IP	操作系统
mysql db1	192.168.1.202	CentOS 6
mysql db2	192.168.1.203
haproxy	192.168.1.204(配置一个虚拟端口3307)

通过192.168.1.204的3307端口使之可以均衡地访问192.168.1.202及192.168.1.203的数据库。

2.2 实验步骤

2.2.1 安装HaProxy

wget http://www.haproxy.org/download/1.8/src/haproxy-1.8.25.tar.gz

tar -xvf haproxy-1.8.25.tar.gz

mv haproxy-1.8.25 /usr/local/haproxy

cd /usr/local/haproxy/

make TARGET=linux26  #centos7.x使用linux31

make install PREFIX=/usr/local/haproxy

mkdir /usr/local/haproxy/conf

cp examples/option-http_proxy.cfg /usr/local/haproxy/conf/haproxy.cfg

2.2.2 修改配置文件

2.2.2.1 编辑HaProxy启动文件

vi /etc/init.d/haproxy

添加：

#!/bin/sh

#

# custom haproxy init.d script, by Mattias Geniar

#

# haproxy         starting and stopping the haproxy load balancer

#

# chkconfig: 345 55 45

# description: haproxy is a TCP loadbalancer

# probe: true



# Source function library.

. /etc/rc.d/init.d/functions



# Source networking configuration.

. /etc/sysconfig/network



# Check that networking is up.

[ ${NETWORKING} = "no" ] && exit 0



[ -f /usr/local/haproxy/sbin/haproxy ] || exit 0



[ -f /usr/local/haproxy/conf/haproxy.cfg ] || exit 0



# Define our actions

checkconfig() {

        # Check the config file for errors

        /usr/local/haproxy/sbin/haproxy -c -q -f /usr/local/haproxy/conf/haproxy.cfg

        if [ $? -ne 0 ]; then

                 echo "Errors found in configuration file."

                return 1

        fi



        # We're OK!

        return 0

}



start() {

        # Check config

        /usr/local/haproxy/sbin/haproxy -c -q -f /usr/local/haproxy/conf/haproxy.cfg

        if [ $? -ne 0 ]; then

                echo "Errors found in configuration file."

                return 1

        fi



        echo -n "Starting HAProxy: "

        daemon /usr/local/haproxy/sbin/haproxy -D -f /usr/local/haproxy/conf/haproxy.cfg -p /var/run/haproxy.pid



        RETVAL=$?

        echo

        [ $RETVAL -eq 0 ] && touch /var/lock/subsys/haproxy

        return $RETVAL

}



stop() {

        echo -n "Shutting down HAProxy: "

        killproc haproxy -USR1



        RETVAL=$?

        echo

        [ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/haproxy

        [ $RETVAL -eq 0 ] && rm -f /var/run/haproxy.pid

        return $RETVAL

}



restart() {

        /usr/local/haproxy/sbin/haproxy -c -q -f /usr/local/haproxy/conf/haproxy.cfg

        if [ $? -ne 0 ]; then

                echo "Errors found in configuration file."

                return 1

        fi



        stop

        start

}



check() {

        /usr/local/haproxy/sbin/haproxy -c -q -V -f /usr/local/haproxy/conf/haproxy.cfg

}



rhstatus() {

        status haproxy

}



reload() {

        /usr/local/haproxy/sbin/haproxy -c -q -f /usr/local/haproxy/conf/haproxy.cfg

        if [ $? -ne 0 ]; then

                echo "Errors found in configuration file."

                return 1

        fi



        echo -n "Reloading HAProxy config: "

        /usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/conf/haproxy.cfg -p /var/run/haproxy.pid -sf $(cat /var/run/haproxy.pid)



        success $"Reloading HAProxy config: "

        echo

}





# Possible parameters

case "$1" in

  start)

        start

        ;;

  stop)

        stop

        ;;

  status)

        rhstatus

        ;;

  restart)

        restart

        ;;

  reload)

        reload

        ;;

  checkconfig)

        check

        ;;

  *)

        echo "Usage: haproxy {start|stop|status|restart|reload|checkconfig}"

        exit 1

esac



exit 0

#授权

chmod +x /etc/init.d/haproxy

2.2.2.2 编辑HaProxy配置文件

vi /usr/local/haproxy/conf/haproxy.cfg

添加：

listen mysql_cluster

        bind 192.168.1.204:3307

        mode tcp

        balance roundrobin

        option mysql-check user haproxy_check    #在mysql中创建无任何权限用户haproxy_check，且无密码

        server mysqldb1 192.168.1.202:3306 check

        server mysqldb2 192.168.1.203:3306 check     

 

修改frontend test-proxy下bind的ip为haproxy所在IP:

        bind            192.168.1.204:8080

2.2.3 启动HaProxy

service haproxy start

[root@pc3 ~]# netstat -anpt | grep 3307

tcp        0      0 192.168.1.204:3307          0.0.0.0:*                   LISTEN      3420/haproxy

2.2.4 创建HaProxy巡检用户

create user haproxy_check@'192.168.1.204';

2.2.5 配置keepalive

配置keepalived是为了防止haproxy单点故障。

步骤略,可参考https://blog.csdn.net/yabingshi_tech/article/details/50721841。

2.3 验证负载均衡

cat b.sh

for i in {1..200}

do

    mysql -h 192.168.1.204 -u haproxy_check -P 3307 -e "select @@hostname"

done

[root@pc2 ~]# sh b.sh > b.log

[root@pc2 ~]# cat b.log | grep pc1 | wc -l

101

[root@pc2 ~]# cat b.log | grep pc2 | wc -l

99

 

--本篇文章主要参考了http://blog.itpub.net/25704976/viewspace-1319781/