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