Go Mysql-galera Cluster with HAproxy

When I started working in 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, came to me is to use HAProxy on top of my existing Galera cluster. I. Introduction

Reminder:i'll use the configuration of my previous Article:mysql multi-master réplication with Galera, the Architectur E works perfectly and the communications are established using SSL. But there were some issues:

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

The main goal was:

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

You can use the 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'll 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 interfaceauto loiface lo inet loopback# The primary network interfaceauto eth0iface eth0 inet dhcp# The secondary network interfaceauto eth1iface eth1 inet staticaddress 10.0.0.10netmask 255.0.0.0

Local name resolution, /etc/hosts :

127.0.0.1   localhost127.0.1.1   haproxy-node0110.0.0.2    galera-node0210.0.0.1    galera-node0110.0.0.3    galera-node0310.0.0.10   haproxy-node0110.0.0.11   haproxy-node02

HAProxy Installation:

[email protected]:~$ sudo apt-get install haproxy -y

We allow the INIT script to launch HAProxy:

[email protected]:~$ sudo sed -i s/0/1/ /etc/default/haproxy

HAProxy Configuration from scratch:

# this config needs haproxy-1.1.28 or haproxy-1.2.1global        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        #quietdefaults        log     global        mode    http        option  tcplog        option  dontlognull        retries 3        option redispatch        maxconn 1024        timeout connect 5000ms        timeout client 50000ms        timeout server 50000mslisten 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 on TCP level, a Full-duplex connection'll be Establishe D between the client and the server. Others options are available: http and health
• balance roundrobin : Defines the load-balancing algorythm to use. Round-robin is a loop system, if you have 2 servers, the failover would be something like this:1,2,1,2
• op tion Tcpka : Enable the keepalive also know as pipelining

If you specify a username with the option mysql-check , the check consists of sending, 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:

[email protected]:~$ sudo service haproxy start

Does HAProxy work?

[email protected]:~$ sudo netstat -plantu | grep 3306tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      3729/haproxy

Round-robin Effects:

  [email  Protected] : ~$ mysql-uroot-proot-h127.0.0.1-e "show variables like ' wsrep_node_name ';" +-----------------+---------------+| variable_name | VALUE |+-----------------+---------------+| Wsrep_node_name | GALERA-NODE01 |+-----------------+---------------+[email protected]:~$ mysql-uroot-proot-h127.0.0.1-e "show Variables like ' wsrep_node_name '; +-----------------+---------------+| variable_name | VALUE |+-----------------+---------------+| Wsrep_node_name | GALERA-NODE02 |+-----------------+---------------+[email protected]:~$ mysql-uroot-proot-h127.0.0.1-e "show Variables like ' wsrep_node_name '; +-----------------+---------------+| variable_name | VALUE |+-----------------+---------------+| Wsrep_node_name | GALERA-NODE03 |+-----------------+---------------+  

Iii. Bonus SCRIPTSIII.1. HAProxy Hot Reconfiguration

Extract from the HAProxy Configuration Guide.

Hot reconfiguration View on Github

  #!/bin/bash#a Hot reconfiguration script would: #Extract from http://haproxy.1wt.eu/download/ 1.3/doc/architecture.txt# Save previous Statemv/etc/haproxy/config/etc/haproxy/config.oldmv/var/run/haproxy.pid/ Var/run/haproxy.pid.oldmv/etc/haproxy/config.new/etc/haproxy/configkill-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 1else 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.P ID mv/etc/haproxy/config/etc/haproxy/config.new mv/etc/haproxy/config.old/etc/haproxy/config Exit 0fi  /pre>

This setup is interesting and not a ready for production. Indeed, putting a HAProxy node on top created a huge SPOF. In the next article, I'll 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-60# descript ION:GLB is a TCP load balancer similar to Pen. # It lacks most of the advanced Pen features, as # The aim is to make a user-space TCP proxy which is # as fast as possible. It can utilize multiple CPU cores. # A List of destinations can configured at runtime. # Destination "draining" is supported. It Features # weight-based connection balancing (which becomes # Round-robin if weights are Equa L). # # # 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 Infopro G= "GLBD" proc=glbdexec=/usr/sbin/glbdlisten_port= "8010" control_port= "8011" threads= "2" DEFAULT_TARGETS= "djdb2 : 8000:0.75 djdb3:8000:0.75 divjobs3:8000:0. 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 STA Rt}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 '] $pro g:failed to query ' GetStats ' from 127.0.0.1: $CONTROL _port "Exit-1}add () {if ["] = = ""]; Then echo $ "Usage: $ add <ip>:<port>[:<weight>]" exit-1 fi if ["' Echo" $ | NC 127.0.0.1 $CONTROL _port ' "= =" OK "]; Then echo "[' Date '] $prog: Added ' $ ' successfully" #getinfo exit 0 fi echo "[' Date '] $prog: Failed to ad  D target ' $ '. Exit-1}remove () {if ["] = =" "]; Then echo $ "Usage: $ remove <ip>:<port>" exit-1 fi If ["' Echo" $1:-1 "| NC 127.0.0.1 $CONTROL _port ' "= =" OK "]; Then echo "[' Date '] $prog: Removed ' $ ' successfully" #getinfo exit 0 fi echo "[' Date '] $prog: Failed to  Remove target ' $ '.  Exit-1}case $ in start) start;  stop) stop;;  restart) restart;;  GetInfo) GetInfo;;  GetStats) getstats;; status) GetInfo;;  Add) add $;;  Remove) remove $;; *) echo $ "Usage: $ start|stop|restart|status|getstats|getinfo|add|remove}" Exit 2esac

[Go]mysql-galera Cluster with HAproxy