HAProxy + Percona XtraDB Cluster
- by rottmanj
I am attempting to setup HAproxy in conjunction with Percona XtraDB Cluster on a series of 3 EC2 instances. I have found a few tutorials online dealing with this specific issue, but I am a bit stuck.
Both the Percona servers and the HAproxy servers are running ubuntu 12.04. The HAProxy version is 1.4.18,
When I start HAProxy I get the following error: Server pxc-back/db01 is DOWN, reason: Socket error, check duration: 2ms.
I am not really sure what the issue could be. I have verified the following:
EC2 security groups ports are open
Poured over my config files looking for issues. I currently do not see any.
Ensured that xinetd was installed
Ensured that I am using the correct ip address of the mysql server.
Any help with this is greatly appreciated.
Here are my current config
Load Balancer
/etc/haproxy/haproxy.cfg
global
log 127.0.0.1 local0
log 127.0.0.1 local1 notice
maxconn 4096
user haproxy
group haproxy
debug
#quiet
daemon
defaults
log global
mode http
option tcplog
option dontlognull
retries 3
option redispatch
maxconn 2000
contimeout 5000
clitimeout 50000
srvtimeout 50000
frontend pxc-front
bind 0.0.0.0:3307
mode tcp
default_backend pxc-back
frontend stats-front
bind 0.0.0.0:22002
mode http
default_backend stats-back
backend pxc-back
mode tcp
balance leastconn
option httpchk
server db01 10.86.154.105:3306 check port 9200 inter 12000 rise 3 fall 3
backend stats-back
mode http
balance roundrobin
stats uri /haproxy/stats
MySql Server
/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 = /usr/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
}
MySql Server
/etc/services
Added the line mysqlchk 9200/tcp # mysqlchk
MySql Server
/usr/bin/clustercheck
# GNU nano 2.2.6 File: /usr/bin/clustercheck
#!/bin/bash
#
# Script to make a proxy (ie HAProxy) capable of monitoring Percona XtraDB Cluster nodes properly
#
# Author: Olaf van Zandwijk <[email protected]>
# Documentation and download: https://github.com/olafz/percona-clustercheck
#
# Based on the original script from Unai Rodriguez
#
MYSQL_USERNAME="testuser"
MYSQL_PASSWORD=""
ERR_FILE="/dev/null"
AVAILABLE_WHEN_DONOR=0
#
# Perform the query to check the wsrep_local_state
#
WSREP_STATUS=`mysql --user=${MYSQL_USERNAME} --password=${MYSQL_PASSWORD} -e "SHOW STATUS LIKE 'wsrep_local_state';" 2>${ERR_FILE} | awk '{if (NR!=1){print $2}}' 2>${ERR_FILE}`
if [[ "${WSREP_STATUS}" == "4" ]] || [[ "${WSREP_STATUS}" == "2" && ${AVAILABLE_WHEN_DONOR} == 1 ]]
then
# Percona XtraDB Cluster node local state is 'Synced' => return HTTP 200
/bin/echo -en "HTTP/1.1 200 OK\r\n"
/bin/echo -en "Content-Type: text/plain\r\n"
/bin/echo -en "\r\n"
/bin/echo -en "Percona XtraDB Cluster Node is synced.\r\n"
/bin/echo -en "\r\n"
else
# Percona XtraDB Cluster node local state is not 'Synced' => return HTTP 503
/bin/echo -en "HTTP/1.1 503 Service Unavailable\r\n"
/bin/echo -en "Content-Type: text/plain\r\n"
/bin/echo -en "\r\n"
/bin/echo -en "Percona XtraDB Cluster Node is not synced.\r\n"
/bin/echo -en "\r\n"
fi
