[转]MySQL-Galera Cluster With HAproxy

micromax

　　When I started working on Open Stack, I had to investigate about the HA of the nova component. Unfortunatly the nova configuration needed a single entry point to connect to the MySQL database. The solution that came to me was to use HAProxy on top of my existing Galera cluster.

I. Introduction
　　Reminder: I will use the configuration of my previous article: MySQL multi-master réplication with Galera, the architecture works perfectly and the communications are established using SSL. But there were some issues:

• There was no load-balancing, the application only use one node and Galera replicates the data accross the cluster
  
• If the node where the application commit goes down, no more replication
  

　　The main goal was:

　　Setup a new feature which will provide load-balancing functionnality and access to my database through a single entry point by using a virtual IP address.

　　You can use some load-balancers for this purpose:

• Pen: a tiny TCP load-balancer
  
• GLB: Galera load-balancer, mainly based on Pen and fix missing functionnality to Pen
  
• MySQL Proxy: the one provides by MySQL
  
• HAProxy: THE load-balancer
  

　　Here a use case:


II. Setup
　　I will re-use my last Galera configuration, here the architecture I set up:

　　NIC configuration, /etc/network/interfaces:

# This file describes the network interfaces available on your system
# and how to activate them. For more information, see interfaces(5).
# The loopback network interface
auto lo
iface lo inet loopback
# The primary network interface
auto eth0
iface eth0 inet dhcp
# The secondary network interface
auto eth1
iface eth1 inet static
address 10.0.0.10
netmask 255.0.0.0

　　Local name resolution, /etc/hosts:

127.0.0.1   localhost
127.0.1.1   haproxy-node01
10.0.0.2    galera-node02
10.0.0.1    galera-node01
10.0.0.3    galera-node03
10.0.0.10   haproxy-node01
10.0.0.11   haproxy-node02

　　HAProxy installation:

ubuntu@haproxy-node01:~$ sudo apt-get install haproxy -y

　　We allow the INIT script to launch HAProxy:

ubuntu@haproxy-node01:~$ sudo sed -i s/0/1/ /etc/default/haproxy

　　HAProxy configuration from scratch:

# this config needs haproxy-1.1.28 or haproxy-1.2.1
global
log 127.0.0.1   local0
log 127.0.0.1   local1 notice
#log loghost    local0 info
maxconn 1024
#chroot /usr/share/haproxy
user haproxy
group haproxy
daemon
#debug
#quiet
defaults
log     global
mode    http
option  tcplog
option  dontlognull
retries 3
option redispatch
maxconn 1024
timeout connect 5000ms
timeout client 50000ms
timeout server 50000ms
listen galera_cluster 0.0.0.0:3306
mode tcp
balance roundrobin
option tcpka
option mysql-check user haproxy
server galera-node01 10.0.0.1:3306 check weight 1
server galera-node02 10.0.0.2:3306 check weight 1
server galera-node03 10.0.0.3:3306 check weight 1

　　Main options details:

• mode tcp: default mode, HAProxy will work at TCP level, a full-duplex connection will be established between the client and the server. Two others options are available: httpand health
  
• balance roundrobin: defines the load-balancing algorythm to use. Round-robin is a loop system, if you have 2 servers, the failover will be something like this: 1,2,1,2
  
• option tcpka: enable the keepalive also know as pipelining
  

　　If you specify a username with the option mysql-check, the check consists of sending two MySQL packet, one Client Authentication packet, and one QUIT packet, to correctly close MySQL session. We then parse the MySQL Handshake Initialisation packet and/or Error packet. It is a basic but useful test which does not produce error nor aborted connect on the server. However, it requires adding an authorization in the MySQL table, like this :

mysql> USE mysql;
mysql> INSERT INTO user (Host,User) values ('10.0.0.10','haproxy');
mysql> FLUSH PRIVILEGES;

　　We run HAProxy:

ubuntu@haproxy-node01:~$ sudo service haproxy start

　　Does HAProxy work?

ubuntu@haproxy-node01:~$ sudo netstat -plantu | grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      3729/haproxy

　　Round-robin effects:

ubuntu@haproxy-node01:~$ mysql -uroot -proot -h127.0.0.1 -e "show variables like 'wsrep_node_name' ;"
+-----------------+---------------+
| Variable_name   | VALUE         |
+-----------------+---------------+
| wsrep_node_name | galera-node01 |
+-----------------+---------------+
ubuntu@haproxy-node01:~$ mysql -uroot -proot -h127.0.0.1 -e "show variables like 'wsrep_node_name' ;"
+-----------------+---------------+
| Variable_name   | VALUE         |
+-----------------+---------------+
| wsrep_node_name | galera-node02 |
+-----------------+---------------+
ubuntu@haproxy-node01:~$ mysql -uroot -proot -h127.0.0.1 -e "show variables like 'wsrep_node_name' ;"
+-----------------+---------------+
| Variable_name   | VALUE         |
+-----------------+---------------+
| wsrep_node_name | galera-node03 |
+-----------------+---------------+

III. Bonus scripts

III.1. HAProxy Hot Reconfiguration
　　Extract from the HAProxy configuration guide.
　　Hot Reconfiguration View on Github

#!/bin/bash
#A hot reconfiguration script would look like this :
#Extract from http://haproxy.1wt.eu/download/1.3/doc/architecture.txt
# save previous state
mv /etc/haproxy/config /etc/haproxy/config.old
mv /var/run/haproxy.pid /var/run/haproxy.pid.old
mv /etc/haproxy/config.new /etc/haproxy/config
kill -TTOU $(cat /var/run/haproxy.pid.old)
if haproxy -p /var/run/haproxy.pid -f /etc/haproxy/config; then
echo "New instance successfully loaded, stopping previous one."
kill -USR1 $(cat /var/run/haproxy.pid.old)
rm -f /var/run/haproxy.pid.old
exit 1
else
echo "New instance failed to start, resuming previous one."
kill -TTIN $(cat /var/run/haproxy.pid.old)
rm -f /var/run/haproxy.pid
mv /var/run/haproxy.pid.old /var/run/haproxy.pid
mv /etc/haproxy/config /etc/haproxy/config.new
mv /etc/haproxy/config.old /etc/haproxy/config
exit 0
fi

　　This setup is interesting but not ready for production. Indeed, putting an HAProxy node on top created a huge SPOF. In the next article, I will setup a failover active/passive cluster with Pacemaker.

III.2 Init script GLB
　　First download it from here and install it :)
　　Init script for the Galera Load Balancer daemon.
　　Start/Stop the Galera Load Balancer daemon. Download me

#!/bin/sh
#
# glbd          Start/Stop the Galera Load Balancer daemon.
#
# processname: glbd
# chkconfig: 2345 90 60
# description: GLB is a TCP load balancer similar to Pen. \
#              It lacks most of advanced Pen features, as \
#              the aim was to make a user-space TCP proxy which is \
#              as fast as possible. It can utilize multiple CPU cores. \
#              A list of destinations can be configured at runtime. \
#              Destination "draining" is supported. It features \
#              weight-based connection balancing (which becomes \
#              round-robin if weights are equal).
### BEGIN INIT INFO
# Provides: glbd
# Required-Start: $local_fs
# Required-Stop: $local_fs
# Default-Start:  2345
# Default-Stop: 90
# Short-Description: run glbd daemon
# Description: GLB is a TCP load balancer similar to Pen.
### END INIT INFO
prog="glbd"
proc=glbd
exec=/usr/sbin/glbd
LISTEN_PORT="8010"
CONTROL_PORT="8011"
THREADS="2"
DEFAULT_TARGETS="djdb2:8000:0.75 djdb3:8000:0.75 divjobs3:8000:0.75 divjobs4:8000:1"
stop() {
echo -n "[`date`] $prog: stopping... "
killall $exec &> /dev/null
if [ $? -ne 0 ]; then
echo "failed."
return
fi
echo "done."
}
start() {
if pidof $prog &> /dev/null ; then
echo "[`date`] $prog: already running...";
exit -1
fi
echo "[`date`] $prog: starting..."
wait_for_connections_to_drop
$exec --daemon --control 127.0.0.1:$CONTROL_PORT --threads $THREADS $LISTEN_PORT $DEFAULT_TARGETS
PID=$!
if [ $? -ne 0 ]; then
echo "[`date`] $prog: failed to start."
exit -1
fi
echo "[`date`] $prog: started, pid=$PID"
exit 0
}
restart() {
echo "[`date`] $prog: restarting..."
stop
start
}
wait_for_connections_to_drop() {
while (netstat -na | grep -m 1 ":$LISTEN_PORT" &> /dev/null); do
echo "[`date`] $prog: waiting for lingering sockets to clear up..."
sleep 1s
done;
}
getinfo() {
echo getinfo | nc 127.0.0.1 $CONTROL_PORT && exit 0
echo "[`date`] $prog: failed to query 'getinfo' from 127.0.0.1:$CONTROL_PORT"
exit -1
}
getstats() {
echo getstats | nc 127.0.0.1 $CONTROL_PORT && exit 0
echo "[`date`] $prog: failed to query 'getstats' from 127.0.0.1:$CONTROL_PORT"
exit -1
}
add() {
if [ "$1" == "" ]; then
echo $"Usage: $0 add <ip>:<port>[:<weight>]"
exit -1
fi
if [ "`echo "$1" | nc 127.0.0.1 $CONTROL_PORT`" == "Ok" ]; then
echo "[`date`] $prog: added '$1' successfully"
#getinfo
exit 0
fi
echo "[`date`] $prog: failed to add target '$1'."
exit -1
}
remove() {
if [ "$1" == "" ]; then
echo $"Usage: $0 remove <ip>:<port>"
exit -1
fi
if [ "`echo "$1:-1" | nc 127.0.0.1 $CONTROL_PORT`" == "Ok" ]; then
echo "[`date`] $prog: removed '$1' successfully"
#getinfo
exit 0
fi
echo "[`date`] $prog: failed to remove target '$1'."
exit -1
}
case $1 in
start)
start
;;
stop)
stop
;;
restart)
restart
;;
getinfo)
getinfo
;;
getstats)
getstats
;;
status)
getinfo
;;
add)
add $2
;;
remove)
remove $2
;;
*)
echo $"Usage: $0 {start|stop|restart|status|getstats|getinfo|add|remove}"
exit 2
esac