Implementation of load Balancing of Percona xtradb cluster using Haproxy

1. Foreword

Reference Http://www.oschina.net/p/haproxyHAProxy,HAProxy provides high availability, load balancing. and proxies based on TCP and HTTP applications to support virtual hosts, which is a free, fast, and reliable solution. Haproxy is especially useful for Web sites that are heavily loaded, which typically require session maintenance or seven-tier processing. Haproxy is running on the current hardware and can support tens of thousands of concurrent connections altogether. and its operating mode makes it easy and secure to integrate into your current architecture while protecting your Web server from being exposed to the network. The following figure is the HAPROXY structure model:

2. Installation Steps

1) Haproxy Installation Environment

Os:redhat 6.5 32-bit

Hostname:mysql-repl01

IP addr:192.168.163.8

Software:haproxy-1.5.3.tar.gz

2) Percona XTRADB cluster environment

Os:redhat 6.5 32-bit

cluster:percona-xtradb-cluster5.6.22-25

Master node:

Hostname:mysql-pxc01

IP addr:192.168.163.6

Standby node:

Hostname:mysql-pxc02

IP addr:192.168.163.7

3 Unzip the file and enter into the directory

[Root@mysql-rep01 haproxy]# Tar xf haproxy-1.5.3.tar
[root@mysql-rep01 software]# CD haproxy/

4) Install GCC

[Root@mysql-rep01 haproxy-1.5.3]# yum Install gcc

5) Installation Haproxy

[Root@mysql-rep01 haproxy-1.5.3]# make target=linux2628 arch=i686
[root@mysql-rep01 haproxy-1.5.3]# make install

6 Create a Haproxy configuration file

[Root@mysql-rep01 haproxy-1.5.3]# mkdir/haproxy
[root@mysql-rep01 haproxy-1.5.3]# cd/haproxy/
[ ROOT@MYSQL-REP01 haproxy]# Touch Haproxy.cfg

Add in Haproxy.cfg

Global log 127.0.0.1 local0 log 127.0.0.1 local1 notice maxconn 4096 user Nobody group nobody daemon defaults log global m Ode HTTP option tcplog option dontlognull retries 3 option Redispatch Maxconn/timeout Connect 5000 Timeout client 500 Timeout server 50000 frontend pxc-front bind *:3307 mode tcp default_backend pxc-back frontend stats-front bind *:80 mo De http default_backend stats-back frontend pxc-onenode-front bind *:3306 mode tcp default_backend pxc-onenode-back Backen d pxc-back mode TCP balance roundrobin option Httpchk server mysql-pxc01 192.168.163.6:3306 Check Port 9200 Inter 12000 RI SE 3 Fall 3 server MYSQL-PXC02 192.168.163.7:3306 Check Port 9200 Inter 12000 rise 3 Fall 3 backend stats-back mode HTTP b
Alance roundrobin stats uri/haproxy/stats stats auth Pxcstats:secret backend pxc-onenode-back mode TCP balance Leastconn Option Httpchk Server mysql-pxc01 192.168.163.6:3306 Check Port 9200 Inter 12000 Rise 3 Fall 3 server MYSQL-PXC02 192.168. 163.7:3306 Check Port 92Inter 12000 rise 3 Fall 3 

7 because Haproxy cannot automatically identify the status of each node in Percona xtradb cluster, it is necessary to invoke Percona's own monitoring script to confirm the node state. Create a monitoring user in the cluster.

Mysql> GRANT PROCESS on *.* to ' clustercheckuser ' @ ' localhost ' identified by ' clustercheckpassword! ';
Query OK, 0 rows affected (0.21 sec)

8 Verify cluster node status monitoring is normal.

[Root@mysql-pxc01 bin]#/usr/bin/clustercheck
http/1.1 OK
content-type:text/plain
connection:close
content-length:40
Percona xtradb Cluster Node is synced.

Standby node:

[Root@mysql-pxc02 data1]#/usr/bin/clustercheck
http/1.1 content-type:text/plain
Connection: Close
content-length:40
Percona xtradb Cluster Node is synced.

8 Add the Monitor program to the service xinetd of the cluster primary standby node, add the Mysqlchk file in/etc/xinetd.d, add the following

# default:on
# description:mysqlchk
service mysqlchk
{
# is ' a config for xinetd, place it in/etc/x inetd.d/
disable = no
flags = reuse
Socket_type = stream
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
}
  

9) To add a monitoring port in the/etc/services of the cluster primary standby node

Mysqlchk 9200/tcp # Mysqlchk

10 Restart the cluster primary standby XINETD service to see if 9200 ports are started

[ROOT@MYSQL-PXC01 xinetd.d]# service xinetd Restart
stopping xinetd: [FAILED]
starting xinetd: [OK]
[ ROOT@MYSQL-PXC01 xinetd.d]# netstat-an|grep-i 9200
TCP 0 0:: 9200:::* LISTEN

11) Start Haproxy

[Root@mysql-rep01 haproxy]# haproxy-f/haproxy/haproxy.cfg

12) View the status of the Haproxy

Log in to the URL we specified http://192.168.163.8/haproxy/stats, username/password for Pxcstats/secret

.

Haproxy configuration complete.