PXC+haproxy+keepalived环境搭建

环境准备：

三节点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      "    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}#Timeout exists for instances where mysqld may be hungTIMEOUT=10EXTRA_ARGS=""if [[ -n "$MYSQL_USERNAME" ]]; then    EXTRA_ARGS="$EXTRA_ARGS --user=${MYSQL_USERNAME}"fiif [[ -n "$MYSQL_PASSWORD" ]]; then    EXTRA_ARGS="$EXTRA_ARGS --password=${MYSQL_PASSWORD}"fiif [[ -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 0else     # 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 1fi

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

mount /dev/cdrom /mediayum -y install xinetdyum -y install telnetecho "mysqlchk 9200/tcp #add mysqlchk" >> /etc/services

vi /etc/xinetd.d/mysqlchk

# default: on# description: mysqlchkservice mysqlchk{# this is a config for xinetd, place it in /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# 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 9200Trying 192.168.8.51...Connected to 192.168.8.51.Escape character is '^]'.HTTP/1.1 200 OKContent-Type: text/plainConnection: closeContent-Length: 40Percona XtraDB Cluster Node is synced.Connection closed by foreign host.

四、配置haproxy

globallog 127.0.0.1 local0 notice#user haproxy#group haproxydaemon#quietnbproc 1pidfile /usr/local/haproxy/haproxy.piddefaultslog globalretries 3option dontlognulloption redispatchmaxconn 2000timeout queue 1mtimeout http-request 10stimeout connect 10stimeout server 1mtimeout client 1mtimeout http-keep-alive 10stimeout check 10sbalance roundrobinlisten mysql_pxc_gwpt1_readbind 192.168.8.98:3307mode tcpbalance leastconnstats hide-versionoption httpchkserver node1 192.168.8.51:3306 check port 9200 inter 12000 rise 3 fall 3server node2 192.168.8.52:3306 check port 9200 inter 12000 rise 3 fall 3server node3 192.168.8.53:3306 check port 9200 inter 12000 rise 3 fall 3listen mysql_pxc_gwpt1_writebind 192.168.8.98:3308mode tcpbalance leastconnstats hide-versionoption httpchkserver node1 192.168.8.51:3306 check port 9200 inter 12000 rise 3 fall 3server node2 192.168.8.52:3306 check port 9200 inter 12000 rise 3 fall 3 backupserver node3 192.168.8.53:3306 check port 9200 inter 12000 rise 3 fall 3 backuplisten haproxy_statsmode httpbind *:8888option httplogstats refresh 5sstats uri /haproxy-statstats realm www.zdd.com moritorstats realm Haproxy Managerstats auth haproxy:haproxy

/etc/rc.d/init.d/haproxy stop/etc/rc.d/init.d/haproxy startsystemctl stop keepalivedsystemctl 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