环境准备：
创新互联的团队成员不追求数量、追求质量。我们经验丰富并且专业，我们之间合作时就好像一个人，协同一致毫无保留。
成都创新互联公司珍视想法，同时也看重过程转化带来的冲击力和影响力，在我们眼中，任何细节都不容小觑。一直致力于为企业提供从
申请域名、网站策划、网站设计、
商城网站开发、网站推广、网站优化到为企业提供个性化软件开发等基于互联网的全面整合营销服务。
三节点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环境搭建过程，各个工具包安装过程略。
一、添加集群检查用户
grant process on *.* to 'clustercheckuser'@'localhost' identified by 'mysql';
flush privileges;
select user,host from mysql.user;
二、修改clustercheck脚本
#!/bin/bash
#
# Script to make a proxy (ie HAProxy) capable of monitoring Percona XtraDB Cluster nodes properly
#
# Authors:
# Raghavendra Prabhu
# Olaf van Zandwijk
#
# Based on the original script from Unai Rodriguez and Olaf (https://github.com/olafz/percona-clustercheck)
#
# Grant privileges required:
# GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!';
if [[ $1 == '-h' || $1 == '--help' ]];then
echo "Usage: $0 "
exit
fi
MYSQL_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}
#Timeout exists for instances where mysqld may be hung
TIMEOUT=10
EXTRA_ARGS=""
if [[ -n "$MYSQL_USERNAME" ]]; then
EXTRA_ARGS="$EXTRA_ARGS --user=${MYSQL_USERNAME}"
fi
if [[ -n "$MYSQL_PASSWORD" ]]; then
EXTRA_ARGS="$EXTRA_ARGS --password=${MYSQL_PASSWORD}"
fi
if [[ -r $DEFAULTS_EXTRA_FILE ]];then
MYSQL_CMDLINE="/mysql/app/mysql/bin/mysql --defaults-extra-file=$DEFAULTS_EXTRA_FILE -nNE --connect-timeout=$TIMEOUT \
${EXTRA_ARGS}"
else
MYSQL_CMDLINE="/mysql/app/mysql/bin/mysql -nNE --connect-timeout=$TIMEOUT ${EXTRA_ARGS}"
fi
#
# Perform the query to check the wsrep_local_state
#
WSREP_STATUS=($($MYSQL_CMDLINE -e "SHOW GLOBAL STATUS LIKE 'wsrep_%';" \
2>${ERR_FILE} | grep -A 1 -E 'wsrep_local_state$|wsrep_cluster_status$' \
| sed -n -e '2p' -e '5p' | tr '\n' ' '))
if [[ ${WSREP_STATUS[1]} == 'Primary' && ( ${WSREP_STATUS[0]} -eq 4 || \
( ${WSREP_STATUS[0]} -eq 2 && $AVAILABLE_WHEN_DONOR -eq 1 ) ) ]]
then
# Check only when set to 0 to avoid latency in response.
if [[ $AVAILABLE_WHEN_READONLY -eq 0 ]];then
READ_ONLY=$($MYSQL_CMDLINE -e "SHOW GLOBAL VARIABLES LIKE 'read_only';" \
2>${ERR_FILE} | tail -1 2>>${ERR_FILE})
if [[ "${READ_ONLY}" == "ON" ]];then
# Percona XtraDB Cluster node local state is 'Synced', but it is in
# read-only mode. The variable AVAILABLE_WHEN_READONLY is set to 0.
# => return HTTP 503
# Shell return-code is 1
echo -en "HTTP/1.1 503 Service Unavailable\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 "Percona XtraDB Cluster Node is read-only.\r\n"
sleep 0.1
exit 1
fi
fi
# Percona XtraDB Cluster node local state is 'Synced' => return HTTP 200
# Shell return-code is 0
echo -en "HTTP/1.1 200 OK\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 "Percona XtraDB Cluster Node is synced.\r\n"
sleep 0.1
exit 0
else
# Percona XtraDB Cluster node local state is not 'Synced' => return HTTP 503
# Shell return-code is 1
echo -en "HTTP/1.1 503 Service Unavailable\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 "Percona XtraDB Cluster Node is not synced or non-PRIM. \r\n"
sleep 0.1
exit 1
fi
三、xinetd 守护进程（PXC所有节点）
mount /dev/cdrom /media
yum -y install xinetd
yum -y install telnet
echo "mysqlchk 9200/tcp #add mysqlchk" >> /etc/services
vi /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
port = 9200
wait = no
user = nobody
server = /mysql/app/mysql/bin/clustercheck
log_on_failure += USERID
only_from = 0.0.0.0/0
# recommended to put the IPs that need
# to connect exclusively (security purposes)
per_source = UNLIMITED
}
chmod u+x /etc/xinetd.d/mysqlchk
从负载均衡节点测试PXC三个端口状态
[root@node2 bin]# telnet 192.168.8.51 9200
Trying 192.168.8.51...
Connected to 192.168.8.51.
Escape character is '^]'.
HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40
Percona XtraDB Cluster Node is synced.
Connection closed by foreign host.
四、配置haproxy
global
log 127.0.0.1 local0 notice
#user haproxy
#group haproxy
daemon
#quiet
nbproc 1
pidfile /usr/local/haproxy/haproxy.pid
defaults
log global
retries 3
option dontlognull
option redispatch
maxconn 2000
timeout queue 1m
timeout http-request 10s
timeout connect 10s
timeout server 1m
timeout client 1m
timeout http-keep-alive 10s
timeout check 10s
balance roundrobin
listen mysql_pxc_gwpt1_read
bind 192.168.8.98:3307
mode tcp
balance leastconn
stats hide-version
option httpchk
server node1 192.168.8.51:3306 check port 9200 inter 12000 rise 3 fall 3
server node2 192.168.8.52:3306 check port 9200 inter 12000 rise 3 fall 3
server node3 192.168.8.53:3306 check port 9200 inter 12000 rise 3 fall 3
listen mysql_pxc_gwpt1_write
bind 192.168.8.98:3308
mode tcp
balance leastconn
stats hide-version
option httpchk
server node1 192.168.8.51:3306 check port 9200 inter 12000 rise 3 fall 3
server node2 192.168.8.52:3306 check port 9200 inter 12000 rise 3 fall 3 backup
server node3 192.168.8.53:3306 check port 9200 inter 12000 rise 3 fall 3 backup
listen haproxy_stats
mode http
bind *:8888
option httplog
stats refresh 5s
stats uri /haproxy-stat
stats realm www.zdd.com moritor
stats realm Haproxy Manager
stats auth haproxy:haproxy
/etc/rc.d/init.d/haproxy stop
/etc/rc.d/init.d/haproxy start
systemctl stop keepalived
systemctl start keepalived
五、从负载均衡节点访问PXC进行测试
[root@slave2 haproxy]# mysql -uroot -pmysql -h292.168.8.98 -P3308 -e "select @@hostname;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| node1 |
+------------+
[root@slave2 haproxy]# mysql -uroot -pmysql -h292.168.8.98 -P3308 -e "select @@hostname;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| node1 |
+------------+
[root@slave2 haproxy]# mysql -uroot -pmysql -h292.168.8.98 -P3308 -e "select @@hostname;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| node1 |
+------------+
[root@slave2 haproxy]# mysql -uroot -pmysql -h292.168.8.98 -P3308 -e "select @@hostname;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| node1 |
+------------+
[root@slave2 haproxy]# mysql -uroot -pmysql -h292.168.8.98 -P3307 -e "select @@hostname;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| node1 |
+------------+
[root@slave2 haproxy]# mysql -uroot -pmysql -h292.168.8.98 -P3307 -e "select @@hostname;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| node2 |
+------------+
[root@slave2 haproxy]# mysql -uroot -pmysql -h292.168.8.98 -P3307 -e "select @@hostname;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| node3 |
+------------+
[root@slave2 haproxy]# mysql -uroot -pmysql -h292.168.8.98 -P3307 -e "select @@hostname;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| node1 |
+------------+
[root@slave2 haproxy]# mysql -uroot -pmysql -h292.168.8.98 -P3307 -e "select @@hostname;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| node2 |
+------------+
[root@slave2 haproxy]# mysql -uroot -pmysql -h292.168.8.98 -P3307 -e "select @@hostname;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| node3 |
+------------+
可以看到，端口3307监控的是读操作，三个节点为轮询机制，访问3308一直访问到node1，因为node2和node3为backup，只有node1宕掉时候才会被访问到。
六、查看haproxy控制台状态
http://192.168.8.98:8888/haproxy-stat
分享题目：PXC+haproxy+keepalived环境搭建-创新互联
标题URL：
http://bzwzjz.com/article/epiog.html
