环境准备：

三节点PXC，部署过程见：http://blog.itpub.net/30135314/viewspace-2219505/

192.168.8.51

192.168.8.52

192.168.8.53

haproxy+keepalived

192.168.8.59

192.168.8.61

工具包版本：

percona-xtrabackup-2.4.11-Linux-x86_64.libgcrypt145.tar.gz

Percona-XtraDB-Cluster-5.7.21-rel20-29.26.1.Linux.x86_64.ssl101.tar.gz

keepalived-2.0.5.tar.gz

haproxy-1.8.9.tar.gz

本文只介绍PXC+haproxy+keepalived环境搭建过程，各个工具包安装过程略。

一、添加集群检查用户

grantprocesson*.*to'clustercheckuser'@'localhost'identifiedby'mysql';flushprivileges;selectuser,hostfrommysql.user;

二、修改clustercheck脚本

#!/bin/bash##Scripttomakeaproxy(ieHAProxy)capableofmonitoringPerconaXtraDBClusternodesproperly##Authors:#RaghavendraPrabhu<raghavendra.prabhu@percona.com>#OlafvanZandwijk<olaf.vanzandwijk@nedap.com>##BasedontheoriginalscriptfromUnaiRodriguezandOlaf(https://github.com/olafz/percona-clustercheck)##Grantprivilegesrequired:#GRANTPROCESSON*.*TO'clustercheckuser'@'localhost'IDENTIFIEDBY'clustercheckpassword!';if[[$1=='-h'||$1=='--help']];thenecho"Usage:$0<user><pass><available_when_donor=0|1><log_file><available_when_readonly=0|1><defaults_extra_file>"exitfiMYSQL_USERNAME="${1-clustercheckuser}"MYSQL_PASSWORD="${2-mysql}"AVAILABLE_WHEN_DONOR=${3:-0}ERR_FILE="${4:-/dev/null}"AVAILABLE_WHEN_READONLY=${5:-1}DEFAULTS_EXTRA_FILE=${6:-/mysql/data/3306/my.cnf}#TimeoutexistsforinstanceswheremysqldmaybehungTIMEOUT=10EXTRA_ARGS=""if[[-n"$MYSQL_USERNAME"]];thenEXTRA_ARGS="$EXTRA_ARGS--user=${MYSQL_USERNAME}"fiif[[-n"$MYSQL_PASSWORD"]];thenEXTRA_ARGS="$EXTRA_ARGS--password=${MYSQL_PASSWORD}"fiif[[-r$DEFAULTS_EXTRA_FILE]];thenMYSQL_CMDLINE="/mysql/app/mysql/bin/mysql--defaults-extra-file=$DEFAULTS_EXTRA_FILE-nNE--connect-timeout=$TIMEOUT\${EXTRA_ARGS}"elseMYSQL_CMDLINE="/mysql/app/mysql/bin/mysql-nNE--connect-timeout=$TIMEOUT${EXTRA_ARGS}"fi##Performthequerytocheckthewsrep_local_state#WSREP_STATUS=($($MYSQL_CMDLINE-e"SHOWGLOBALSTATUSLIKE'wsrep_%';"\2>${ERR_FILE}|grep-A1-E'wsrep_local_state$|wsrep_cluster_status$'\|sed-n-e'2p'-e'5p'|tr'\n'''))if[[${WSREP_STATUS[1]}=='Primary'&&(${WSREP_STATUS[0]}-eq4||\(${WSREP_STATUS[0]}-eq2&&$AVAILABLE_WHEN_DONOR-eq1))]]then#Checkonlywhensetto0toavoidlatencyinresponse.if[[$AVAILABLE_WHEN_READONLY-eq0]];thenREAD_ONLY=$($MYSQL_CMDLINE-e"SHOWGLOBALVARIABLESLIKE'read_only';"\2>${ERR_FILE}|tail-12>>${ERR_FILE})if[["${READ_ONLY}"=="ON"]];then#PerconaXtraDBClusternodelocalstateis'Synced',butitisin#read-onlymode.ThevariableAVAILABLE_WHEN_READONLYissetto0.#=>returnHTTP503#Shellreturn-codeis1echo-en"HTTP/1.1503ServiceUnavailable\r\n"echo-en"Content-Type:text/plain\r\n"echo-en"Connection:close\r\n"echo-en"Content-Length:43\r\n"echo-en"\r\n"echo-en"PerconaXtraDBClusterNodeisread-only.\r\n"sleep0.1exit1fifi#PerconaXtraDBClusternodelocalstateis'Synced'=>returnHTTP200#Shellreturn-codeis0echo-en"HTTP/1.1200OK\r\n"echo-en"Content-Type:text/plain\r\n"echo-en"Connection:close\r\n"echo-en"Content-Length:40\r\n"echo-en"\r\n"echo-en"PerconaXtraDBClusterNodeissynced.\r\n"sleep0.1exit0else#PerconaXtraDBClusternodelocalstateisnot'Synced'=>returnHTTP503#Shellreturn-codeis1echo-en"HTTP/1.1503ServiceUnavailable\r\n"echo-en"Content-Type:text/plain\r\n"echo-en"Connection:close\r\n"echo-en"Content-Length:57\r\n"echo-en"\r\n"echo-en"PerconaXtraDBClusterNodeisnotsyncedornon-PRIM.\r\n"sleep0.1exit1fi

三、xinetd 守护进程（PXC所有节点）

mount/dev/cdrom/mediayum-yinstallxinetdyum-yinstalltelnetecho"mysqlchk9200/tcp#addmysqlchk">>/etc/services

vi /etc/xinetd.d/mysqlchk

#default:on#description:mysqlchkservicemysqlchk{#thisisaconfigforxinetd,placeitin/etc/xinetd.d/disable=noflags=REUSEsocket_type=streamport=9200wait=nouser=nobodyserver=/mysql/app/mysql/bin/clusterchecklog_on_failure+=USERIDonly_from=0.0.0.0/0#recommendedtoputtheIPsthatneed#toconnectexclusively(securitypurposes)per_source=UNLIMITED}

chmodu+x/etc/xinetd.d/mysqlchk

从负载均衡节点测试PXC三个端口状态

[root@node2bin]#telnet192.168.8.519200Trying192.168.8.51...Connectedto192.168.8.51.Escapecharacteris'^]'.HTTP/1.1200OKContent-Type:text/plainConnection:closeContent-Length:40PerconaXtraDBClusterNodeissynced.Connectionclosedbyforeignhost.

四、配置haproxy

globallog127.0.0.1local0notice#userhaproxy#grouphaproxydaemon#quietnbproc1pidfile/usr/local/haproxy/haproxy.piddefaultslogglobalretries3optiondontlognulloptionredispatchmaxconn2000timeoutqueue1mtimeouthttp-request10stimeoutconnect10stimeoutserver1mtimeoutclient1mtimeouthttp-keep-alive10stimeoutcheck10sbalanceroundrobinlistenmysql_pxc_gwpt1_readbind192.168.8.98:3307modetcpbalanceleastconnstatshide-versionoptionhttpchkservernode1192.168.8.51:3306checkport9200inter12000rise3fall3servernode2192.168.8.52:3306checkport9200inter12000rise3fall3servernode3192.168.8.53:3306checkport9200inter12000rise3fall3listenmysql_pxc_gwpt1_writebind192.168.8.98:3308modetcpbalanceleastconnstatshide-versionoptionhttpchkservernode1192.168.8.51:3306checkport9200inter12000rise3fall3servernode2192.168.8.52:3306checkport9200inter12000rise3fall3backupservernode3192.168.8.53:3306checkport9200inter12000rise3fall3backuplistenhaproxy_statsmodehttpbind*:8888optionhttplogstatsrefresh5sstatsuri/haproxy-statstatsrealmwww.zdd.commoritorstatsrealmHaproxyManagerstatsauthhaproxy:haproxy

/etc/rc.d/init.d/haproxystop/etc/rc.d/init.d/haproxystartsystemctlstopkeepalivedsystemctlstartkeepalived

五、从负载均衡节点访问PXC进行测试

[root@slave2haproxy]#mysql-uroot-pmysql-h292.168.8.98-P3308-e"select@@hostname;"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|node1|+------------+[root@slave2haproxy]#mysql-uroot-pmysql-h292.168.8.98-P3308-e"select@@hostname;"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|node1|+------------+[root@slave2haproxy]#mysql-uroot-pmysql-h292.168.8.98-P3308-e"select@@hostname;"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|node1|+------------+[root@slave2haproxy]#mysql-uroot-pmysql-h292.168.8.98-P3308-e"select@@hostname;"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|node1|+------------+[root@slave2haproxy]#mysql-uroot-pmysql-h292.168.8.98-P3307-e"select@@hostname;"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|node1|+------------+[root@slave2haproxy]#mysql-uroot-pmysql-h292.168.8.98-P3307-e"select@@hostname;"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|node2|+------------+[root@slave2haproxy]#mysql-uroot-pmysql-h292.168.8.98-P3307-e"select@@hostname;"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|node3|+------------+[root@slave2haproxy]#mysql-uroot-pmysql-h292.168.8.98-P3307-e"select@@hostname;"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|node1|+------------+[root@slave2haproxy]#mysql-uroot-pmysql-h292.168.8.98-P3307-e"select@@hostname;"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|node2|+------------+[root@slave2haproxy]#mysql-uroot-pmysql-h292.168.8.98-P3307-e"select@@hostname;"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|node3|+------------+

可以看到，端口3307监控的是读操作，三个节点为轮询机制，访问3308一直访问到node1，因为node2和node3为backup，只有node1宕掉时候才会被访问到。

六、查看haproxy控制台状态

http://192.168.8.98:8888/haproxy-stat