Percona XtraDB with haproxy load Cluster

This was done before Percona XtraDB Cluster (Configuration See link

http://hans925.blog.51cto.com/542170/1607157)

Now with Haproxy to Percona XtraDB cluster do load balancing, machine or those two machines, (originally do load balancing three machines, but personal notebook run two virtual machine on a bit card, so it used two). The haproxy was installed on the first Percona.

Haproxy

Os:ubuntu 14.04 Server ip:172.16.53.136

Percona-node 1

Os:ubuntu 14.04 Server ip:172.16.53.136

Percona-node 2

Os:ubuntu 14.04 Server ip:172.16.53.137

Both Percona are to start up:

Mysql-u root-p-E "INSERT into Mysql.user (host,user) VALUES (' 172.16.53.136 ', ' Haproxy_check '); FLUSH privileges; "

[Email protected]# mysql-u root-p-E "GRANT all privileges on * * to ' haproxy_root ' @ ' 172.16.53.136 ' identified by ' PASSW Ord ' with GRANT OPTION; FLUSH Privileges "

The database should be able to log on from the Haproxy machine.

Installing Haproxy

Ubuntu Install the method provided in the link below can be installed.

http://haproxy.debian.net/

I'm using the 1.5 version, after the installation is done

Sed-i "s/enabled=0/enabled=1/"/etc/default/haproxy

Configuration:

Vi/etc/haproxy/haproxy.cfg

Global

Log 127.0.0.1 Local0

Log 127.0.0.1 Local1 Notice

Maxconn 4096

UID 99

GID 99

Daemon

#debug

Quiet

Defaults

Log Global

Mode http

Option Tcplog

Option Dontlognull

Retries 3

Option Redispatch

Maxconn 2000

Contimeout 5000

Listen Mysql-cluster 0.0.0.0:3307 (default is 3306 if you want to get rid of this port with two machine tests)

Mode TCP

Balance Roundrobin

Option Tcpka

#option Httpchk If the HTTP service is not installed on the machine, the option Httpchk is commented or deleted (without opening port 80).

Server db01.com 172.16.53.136 Check Port 9200 Inter 12000 rise 3 Fall 3

Server db02.com 172.16.53.137:3306 Check Port 9200 Inter 12000 rise 3 Fall 3

Listen stats 0.0.0.0:8080

Mode http

Option Httpchk

Balance Roundrobin

Stats URI/

Stats Refresh 10s

Stats Realm Haproxy\ Statistics

Stats Auth test:123456 This account is the account of the login Haproxy page

Edit on the database machine (two machines are plus):

Vi/usr/bin/clustercheck (This should have execute permission)

The database user name that was set before mysql_username=haproxy_root (this should be able to connect from remote)

mysql_password=123456

If there is no other way to copy the specific method on the Internet search (and also have mysqlchk this file. All two files must have execute permission)

Vi/etc/services finally add:

Mysqlchk 9200/tcp # Mysqlchk

Start xinetd (if Apt-get install xinetd is not installed)

Service xinetd Restart

Start Haproxy

Services Haproxy Start

Or

Haproxy-f/etc/haproxy/haproxy.cfg-p/var/run/haproxy.pid-d-V (this is for debug start, error will print out)

Percona clust load Balancing with Haproxy:

Always reported:

[WARNING] 020/135012 (2536): Server mysql-cluster/db01.com is down, Reason:layer7 wrong status, code:503, info: "Servic E unavailable ", check duration:9ms. 1 active and 0 backup servers left. 0 Sessions Active, 0 requeued, 0 remaining in queue.

[WARNING] 020/135012 (2536): Server mysql-cluster/db02.com is down, Reason:layer7 wrong status, code:503, info: "Servic E unavailable ", check duration:9ms. 1 active and 0 backup servers left. 0 Sessions Active, 0 requeued, 0 remaining in queue.

[CST 1:58 P.M. 14 sec.] Hans Wang: [ALERT] 020/135018 (2536): Proxy ' mysql-cluster ' has no server available!

Workaround:

If the HTTP service is not installed on the machine, comment or delete the option Httpchk in Mysql-cluster.

Open option Httpchk The machine must have 80 ports open.

Percona XtraDB with haproxy load Cluster