HAProxy + Percona XtraDB Cluster

Question

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:

1. EC2 security groups ports are open
2. Poured over my config files looking for issues. I currently do not see any.
3. Ensured that xinetd was installed
4. 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

Solution 1

I had problem where telnet 127.0.0.1 9200 gave 503 Server unavailable response, but when I executed /usr/bin/clustercheck as root it showed that everything was fine.

With this command I was able to execute clustercheck as user nobody and got real MySQL error to /tmp/cluster.log:

sudo -H -u nobody bash -c "/usr/bin/clustercheck clustercheckuser clustercheckpassword! 0 /tmp/cluster.log"

And /tmp/cluster.log contained:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (13)

Problem was that directory where mysql.sock was located didn't have execute (+x) flag on it and therefore wasn't readably by user nobody and fix was:

chmod o+x /var/run/mysql

Solution 2

Please ensure the following steps and post the results here.

1. xinetd is running.
2. Create the mysql test user testuser as you mentioned
3. verify listening tcp port(9200) by netstat -nlt
4. If you have enabled iptables, please allow port 9200
5. if the port is open and in listen state, install telnet client in percona node and try to telnet by telnet 127.0.0.1 9200
6. If the xinetd is working, you should see the http response.
7. if not, execute /usr/bin/clustercheck

Once you done all this, please post the results here.

Comments

[carillonator]

what does curl -I localhost:9200 return when run on one of the db cluster nodes? HAproxy will look for a 200 HTTP return code when running that check in order for it to mark the server up.