haproxy负载均衡 xtracluster

上一篇为xtracluster的安装（http://xmarker.blog.163.com/blog/static/226484057201472610520306/），本篇将记录下使用haproxy在三个数据节点做负载均衡，haproxy可以安装在数据节点，也可以安装在别的节点，如果安装在数据节点，需要注意端口号不能和mysql的3306冲突

1.haproxy安装（ 仅一个节点即可 ）

下载地址：http://www.haproxy.org/，选择最新稳定版。这里我选择数据节点ddb169作为haproxy的负载均衡服务器

[root@db169 soft]# tar -zxvf haproxy-1.5.3.tar.gz

[root@db169 soft]# cd haproxy-1.5.3

[root@db169 haproxy-1.5.3]# make TARGET=linux2628

[root@db169 haproxy-1.5.3]# make install

[root@db169 haproxy-1.5.3]# mkdir /etc/haproxy;cp examples/haproxy.cfg /etc/haproxy/

[root@db169 haproxy-1.5.3]# groupadd -g 1001 haproxy

[root@db169 haproxy-1.5.3]# useradd -g haproxy haproxy

2.配置haproxy（仅一个节点即可）

配置haproxy.cnf文件

[root@db169 haproxy-1.5.3]# mkdir /etc/haproxy/

[root@db169 haproxy-1.5.3]# cat /etc/haproxy/haproxy.cfg

# this config needs haproxy-1.1.28 or haproxy-1.2.1

global

log 127.0.0.1 local0

log 127.0.0.1 local1 notice

#log loghost local0 info

maxconn 4096

chroot /usr/share/haproxy

uid haproxy

gid haproxy

daemon

#debug

#quiet

pidfile  /var/run/haproxy.pid

defaults

log global

mode http

#option httplog

option dontlognull

retries 3

redispatch

maxconn 2000

contimeout 5000

clitimeout 50000

srvtimeout 50000

frontend pxc-front  #描述允许客户端连接的监听套接字

        bind    *:3307

mode    tcp

default_backend pxc-back #当没有匹配use_backend时，默认的backend

frontend stats-front

  bind    *:8099

mode    http

default_backend stats-back

backend pxc-back #描述进来的连接将转发到哪些后端服务器

mode    tcp

balance leastconn    #负载均衡算法，使用最少连接算法，适合长连接应用

option httpchk #启用HTTP协议检查服务器监控状态，通过调用脚本检查节点的状态

server db169 192.168.1.169:3306 check port 9200 inter 12000 rise 3 fall 3 #fall连续3次检查错误后，将表明服务器死亡，默认为3；inter连续两次检查的间隔时间值，单位为毫秒默认为2s；rise连续3次检查成功，表明服务可用

server db172 192.168.1.172:3306 check port 9200 inter 12000 rise 3 fall 3

server db173 192.168.1.173:3306 check port 9200 inter 12000 rise 3 fall 3

backend stats-back  #开启haproxy的状态页面

mode http

balance roundrobin

stats   uri /haproxy/stats #定义访问统计信息的URI

stats   auth    admin:admin #设置查看统计信息的用户名和密码

3.安装检测脚本（每个节点都需要）

[root@db169 haproxy-1.5.3]# cp /opt/pxc/bin/clustercheck /usr/bin/

[root@db169 haproxy-1.5.3]# cp /opt/pxc/xinetd.d/mysqlchk /etc/xinetd.d/

[root@db169 haproxy-1.5.3]# echo 'mysqlchk 9200/tcp # mysqlchk' >> /etc/services

安装xinetd服务

[root@db169 haproxy-1.5.3]# yum -y install xinetd

[root@db169 haproxy-1.5.3]# /etc/init.d/xinetd restart

停止 xinetd：                                              [失败]

正在启动 xinetd：                                          [确定]

[root@db169 haproxy-1.5.3]# chkconfig --level 2345 xinetd on

创建检查脚本的用户（在任一节点即可）：

grant process on *.* to 'clustercheckuser'@'localhost' identified by 'clustercheckpassword!';

测试检测脚本

[root@db169 haproxy-1.5.3]# clustercheck

HTTP/1.1 200 OK

Content-Type: text/plain

Connection: close

Content-Length: 40

[root@db169 haproxy-1.5.3]# curl -I 127.0.0.1:9200

HTTP/1.1 200 OK

Content-Type: text/plain

Connection: close

Content-Length: 40

要保证状态为200，否则检测不通过，可能是mysql服务不正常，或者环境不对致使haproxy无法使用mysql，

如果不为200，可以手工修改下检测脚本，时期输出到日志，然后排错，比如我的检测就有个错误，一直报503：

[root@db169 examples]# curl -I 192.168.1.172:9200

HTTP/1.1 503 Service Unavailable

Content-Type: text/plain

Connection: close

Content-Length: 44

我修改db172后的检测脚本如下：

[root@db172 data]# cat /usr/bin/clustercheck 

#!/bin/bash 

#

# Script to make a proxy (ie HAProxy) capable of monitoring Percona XtraDB Cluster nodes properly

#

# Authors:

# Raghavendra Prabhu <[email protected]>

# Olaf van Zandwijk <[email protected]>

#

# Based on the original script from Unai Rodriguez and Olaf (https://github.com/olafz/percona-clustercheck)

#

# Grant privileges required:

# GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!';

if [[ $1 == '-h' || $1 == '--help' ]];then

    echo "Usage: $0 <user> <pass> <available_when_donor=0|1> <log_file> <available_when_readonly=0|1> <defaults_extra_file>"

    exit

fi

MYSQL_USERNAME="${1-clustercheckuser}" 

MYSQL_PASSWORD="${2-clustercheckpassword!}" 

AVAILABLE_WHEN_DONOR=${3:-0}

ERR_FILE="${4:-/tmp/mcl2.log}" 

AVAILABLE_WHEN_READONLY=${5:-1}

DEFAULTS_EXTRA_FILE=${6:-/etc/my.cnf}

#Timeout exists for instances where mysqld may be hung

TIMEOUT=10

echo "MYSQL_USERNAME : $MYSQL_USERNAME" >>/tmp/mcl.log

echo "MYSQL_PASSWORD : $MYSQL_PASSWORD" >>/tmp/mcl.log

echo "AVAILABLE_WHEN_DONOR : $AVAILABLE_WHEN_DONOR" >>/tmp/mcl.log

echo "ERR_FILE : $ERR_FILE" >>/tmp/mcl.log

echo "AVAILABLE_WHEN_READONLY : $AVAILABLE_WHEN_READONLY" >>/tmp/mcl.log

echo "DEFAULTS_EXTRA_FILE : $DEFAULTS_EXTRA_FILE" >>/tmp/mcl.log

然后再测试：

[root@db172 data]# more /tmp/mcl.log

/usr/bin/clustercheck

/usr/bin/clustercheck

MYSQL_USERNAME : clustercheckuser

MYSQL_PASSWORD : clustercheckpassword!

AVAILABLE_WHEN_DONOR : 0

ERR_FILE : /dev/null

AVAILABLE_WHEN_READONLY : 1

DEFAULTS_EXTRA_FILE : /etc/my.cnf

[root@db172 data]# more /tmp/mcl2.log

/usr/bin/clustercheck: line 44: mysql: command not found

看到问题了，原来是找不到mysql这个可执行文件

[root@db172 data]# which mysql

/opt/pxc/bin/mysql

明明是有的，但这个脚本可能找不到，所以把它复制到/usr/bin下面，在测试就通过了

4.启动haproxy：

[root@db169 ~]# haproxy -f /etc/haproxy/haproxy.cfg

[WARNING] 236/190345 (21350) : parsing [/etc/haproxy/haproxy.cfg:22]: keyword 'redispatch' is deprecated in favor of 'option redispatch', and will not be supported by future versions.

[WARNING] 236/190345 (21350) : parsing [/etc/haproxy/haproxy.cfg:24] : the 'contimeout' directive is now deprecated in favor of 'timeout connect', and will not be supported in future versions.

[WARNING] 236/190345 (21350) : parsing [/etc/haproxy/haproxy.cfg:25] : the 'clitimeout' directive is now deprecated in favor of 'timeout client', and will not be supported in future versions.

[WARNING] 236/190345 (21350) : parsing [/etc/haproxy/haproxy.cfg:26] : the 'srvtimeout' directive is now deprecated in favor of 'timeout server', and will not be supported in future versions.

[ALERT] 236/190345 (21350) : [haproxy.main()] Cannot chroot(/usr/share/haproxy).

报错了，解决方法：

[root@db169 ~]# mkdir /usr/share/haproxy

再次启动：

[root@db169 ~]# haproxy -f /etc/haproxy/haproxy.cfg

[WARNING] 236/190744 (21400) : parsing [/etc/haproxy/haproxy.cfg:22]: keyword 'redispatch' is deprecated in favor of 'option redispatch', and will not be supported by future versions.

[WARNING] 236/190744 (21400) : parsing [/etc/haproxy/haproxy.cfg:24] : the 'contimeout' directive is now deprecated in favor of 'timeout connect', and will not be supported in future versions.

[WARNING] 236/190744 (21400) : parsing [/etc/haproxy/haproxy.cfg:25] : the 'clitimeout' directive is now deprecated in favor of 'timeout client', and will not be supported in future versions.

[WARNING] 236/190744 (21400) : parsing [/etc/haproxy/haproxy.cfg:26] : the 'srvtimeout' directive is now deprecated in favor of 'timeout server', and will not be supported in future versions.

[root@db169 ~]# netstat -anp|grep 3307

tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      21401/haproxy

已经在3307端口监听了

5.添加开机自启动脚本：

6.测试

[root@db169 ~]# mysql -p123456 -P3307 -h 192.168.1.169

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 47

Server version: 5.6.19-67.0-25.6-log Percona XtraDB Cluster binary (GPL) 5.6.19-25.6, Revision 824, wsrep_25.6.r4111

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

关闭任意一个节点不影响haproxy的连接。

6.haproxy web端：

http://192.168.1.169:8099/haproxy/stats

输入用户名密码分别为配置文件里的配置即可登录查看负载等情况。