mysql之PXC5.7.18集群系列——2.HAProxy

1. 相关准备

官方网站：http://www.haproxy.org
IP：172.16.3.30

2. 编译安装

# cd /home/soft/haproxy.1.7.8
# make TARGET=linux26 prefix=/home/soft/haproxy
# make install PREFIX=/home/soft/haproxy

备注：uname -r，查看TARGET=linux??  

3. 创建用户

# useradd haproxy

4. 开放端口

# iptables -I INPUT -p tcp -m multiport –dport 80,3306,3307 -j ACCEPT
# service iptables save
# service iptables restart

备注：开放端口目的，配置下一步的haproxy.cfg   

5. 配置haproxy.cfg

# vim /etc/haproxy/haproxy.cfg

global  
        log 127.0.0.1 local0  
        log 127.0.0.1 local1 notice  
        maxconn 4096  
        chroot /usr/share/haproxy  
        user haproxy  
        group haproxy  
        daemon  

defaults  
        log global  
        mode http  
        option tcplog  
        option dontlognull  
        retries 3  
        option redispatch  
        maxconn 2000  
        timeout connect 5s #haproxy端  
        timeout client  8h #mysql client端
        timeout server  1h #mysql server端 

frontend pxc-front  
        bind *:3307  
        mode tcp  
        default_backend pxc-back  

frontend stats-front  
        bind *:80  
        mode http  
        default_backend stats-back  

frontend pxc-onenode-front  
        bind *:3308  
        mode tcp  
        default_backend pxc-onenode-back  

backend pxc-back  
        mode tcp  
        balance leastconn  
        option httpchk  
        server mysql01 172.16.3.31:3306 check port 9200 inter 12000 rise 3 fall 3  
        server mysql02 172.16.3.32:3306 check port 9200 inter 12000 rise 3 fall 3  
        server mysql03 172.16.3.33:3306 check port 9200 inter 12000 rise 3 fall 3  

backend stats-back  
        mode http  
        balance roundrobin  
        stats uri /haproxy/stats  
        stats auth admin:haproxy  

backend pxc-onenode-back  
        mode tcp  
        balance leastconn  
        option httpchk  
        server mysql01 172.16.3.31:3306 check port 9200 inter 12000 rise 3 fall 3  
        server mysql02 172.16.3.32:3306 check port 9200 inter 12000 rise 3 fall 3 backup  
        server mysql03 172.16.3.33:3306 check port 9200 inter 12000 rise 3 fall 3 backup  

备注：1.frontend *** 配置的端口需开放
备注：2.uri /haproxy/stats，配置网页地址  
备注：3.auth admin:haproxy，配置账号密码  

6. 配置service haproxy

# vim /etc/init.d/haproxy

#!/bin/sh  
#  
# haproxy  
#  
# chkconfig:   - 85 15  
# description:  HAProxy is a free, very fast and reliable solution \  
#               offering high availability, load balancing, and \  
#               proxying for TCP and  HTTP-based applications  
# processname: haproxy  
# config:      /etc/haproxy/haproxy.cfg  
# pidfile:     /var/run/haproxy.pid  

# Source function library.  
. /etc/rc.d/init.d/functions  

# Source networking configuration.  
. /etc/sysconfig/network  

# Check that networking is up.  
[ "$NETWORKING" = "no" ] && exit 0  

exec="/home/soft/haproxy/sbin/haproxy"  
prog=$(basename $exec)  

[ -e /etc/sysconfig/$prog ] && . /etc/sysconfig/$prog  

cfgfile=/etc/haproxy/haproxy.cfg  
pidfile=/var/run/haproxy.pid  
lockfile=/var/lock/subsys/haproxy  

check() {  
    $exec -c -V -f $cfgfile $OPTIONS  
}  

start() {  
    $exec -c -q -f $cfgfile $OPTIONS  
    if [ $? -ne 0 ]; then  
        echo "Errors in configuration file, check with $prog check."  
        return 1  
    fi  

    echo -n $"Starting $prog: "  
    # start it up here, usually something like "daemon $exec"  
    daemon $exec -D -f $cfgfile -p $pidfile $OPTIONS  
    retval=$?  
    echo  
    [ $retval -eq 0 ] && touch $lockfile  
    return $retval  
}  

stop() {  
    echo -n $"Stopping $prog: "  
    # stop it here, often "killproc $prog"  
    killproc $prog  
    retval=$?  
    echo  
    [ $retval -eq 0 ] && rm -f $lockfile  
    return $retval  
}  

restart() {  
    $exec -c -q -f $cfgfile $OPTIONS  
    if [ $? -ne 0 ]; then  
        echo "Errors in configuration file, check with $prog check."  
        return 1  
    fi  
    stop  
    start  
}  

reload() {  
    $exec -c -q -f $cfgfile $OPTIONS  
    if [ $? -ne 0 ]; then  
        echo "Errors in configuration file, check with $prog check."  
        return 1  
    fi  
    echo -n $"Reloading $prog: "  
    $exec -D -f $cfgfile -p $pidfile $OPTIONS -sf $(cat $pidfile)  
    retval=$?  
    echo  
    return $retval  
}  

force_reload() {  
    restart  
}  

fdr_status() {  
    status $prog  
}  

case "$1" in  
    start|stop|restart|reload)  
        $1  
        ;;  
    force-reload)  
        force_reload  
        ;;  
    check)  
        check  
        ;;  
    status)  
        fdr_status  
        ;;  
    condrestart|try-restart)  
        [ ! -f $lockfile ] || restart  
        ;;  
    *)  
        echo $"Usage: $0 {start|stop|status|restart|try-restart|reload|force-reload}"  
        exit 2  
esac 

备注：如果启动错误，请仔细查看错误提示，修改行号22-29的文件路径

7.clustercheck

(1) 添加clustercheck权限

mysql> grant process on *.* to 'clustercheckuser'@'localhost' identified by 'clustercheckpassword!';
mysql> flush privileges;
# clustercheck

HTTP/1.1 200 OK

备注：此时，数据库已处于集群状态，因此在任意一个Node下登陆mysql执行即可

(2) xinetd守护进程

# yum -y install xinetd
# vim /etc/services

mysqlchk 9200/tcp #add mysqlchk

# vim /etc/xinetd.d/mysqlchk

# default: on 
# description: mysqlchk 
service mysqlchk 
{ 
# this is a config for xinetd, place it in /etc/xinetd.d/
        disable = no 
        flags           = REUSE 
        socket_type     = stream 
        type            = UNLISTED
        port            = 9200 
        wait            = no 
        user            = nobody 
        server          = /usr/bin/clustercheck
        log_on_failure  += USERID 
        only_from       = 0.0.0.0/0
        #
        # Passing arguments to clustercheck
        # <user> <pass> <available_when_donor=0|1> <log_file> <available_when_readonly=0|1> <defaults_extra_file>"
        # Recommended: server_args   = user pass 1 /var/log/log-file 0 /etc/my.cnf.local"
        # Compatibility: server_args = user pass 1 /var/log/log-file 1 /etc/my.cnf.local"
        # 55-to-56 upgrade: server_args = user pass 1 /var/log/log-file 0 /etc/my.cnf.extra"
        #
        # recommended to put the IPs that need 
        # to connect exclusively (security purposes) 
        per_source      = UNLIMITED 
}

# service xinetd restart

备注：PXC每个Node下安装xinetd   

8.启动haproxy

# /home/soft/haproxy/sbin/haproxy start

9.监控页面

http://172.16.3.30/haproxy/stats

备注：admin,haproxy