Galera Cluster High Availability With HAProxy on Ubuntu 18.04 / CentOS 7

In our recent guide on How to Setup MariaDB Galera Cluster on Ubuntu with HAProxy, we covered all the steps to install and configure a Galera Cluster on Ubuntu 18.04. In this guide, we will introduce load balancing of created Galera Cluster using HAProxy.

Having three or above Galera cluster nodes, it becomes hard to have a true load balancing without using a load balancer like HAProxy. It also means you’ll have to configure your application to point to one of the Galera nodes, and this introduces a single point of failure in your infrastructure.

By setting up HAproxy, you’ll ensure your Database service is always available, with central control of Galera Cluster nodes for things like maintenance. If you want to remove one of the nodes, you only need to change the backend configuration of HAProxy.

Galera Cluster High Availability With HAProxy

We will start by ensuring the HAProxy package is installed on our Ubuntu 18.04 system.  Update your system packages and install HAProxy.

# Ubuntu / Debian
sudo apt-get update
sudo apt-get install haproxy

# CentOS
sudo yum -y install epel-release
sudo yum -y install haproxy

Confirm the installed version of HAProxy:

$ apt policy haproxy
$ rpm -qi haproxy

Configure HAProxy to Load Balance Galera Cluster

Now that HAProxy has been installed, configure haproxy to do load balancing of Galera cluster database servers at the TCP layer.

Configure /etc/hosts file with Galera nodes:

$ sudo vim /etc/hosts
10.131.74.92    galera-db-01
10.131.35.167  galera-db-02
10.131.65.13    galera-db-03

HAProxy Destination Selection Policies

HAProxy will select a backend server to route traffic to depending on the destination route-policy configured. This is a complete list of Destination Selection Policies available in HAProxy:

• Round Robin Directs new connections to the next destination in a circular order list, modified by the server’s weight. Enable it with balance roundrobin
• Static Round Robin Directs new connections to the next destination in a circular order list, modified by the server’s weight. Unlike the standard implementation of round robin, in static round robin, you cannot modify the server weight on the fly. Changing the server weight requires you to restart HAProxy. Enable it with balance static-rr
• Least Connected Directs new connections to the server with the smallest number of connections available, which is adjusted for the server’s weight. Enable it with balance leastconn
• First Directs new connections to the first server with a connection slot available. They are chosen from the lowest numeric identifier to the highest. Once the server reaches its maximum connections value, HAProxy moves to the next in the list. Enable it with.balance first
• Source Tracking Divides the source IP address by the total weight of running servers. Ensures that client connections from the same source IP always reach the same server. Enable it with balance source

Open HAProxy main configuration file:

sudo vim /etc/haproxy/haproxy.cfg

Your configuration  should have settings similar to below:

# Galera Cluster Frontend configuration
frontend galera_cluster_frontend
    bind 10.131.69.129:3306
    mode tcp
    option tcplog
    default_backend galera_cluster_backend

# Galera Cluster Backend configuration
backend galera_cluster_backend
    mode tcp
    option tcpka
    balance leastconn
    server db-server-01 galera-db-01:3306  check weight 1
    server db-server-02 galera-db-02:3306  check weight 1
    server db-server-03 galera-db-03:3306  check weight 1

Here is the explanation of options use:

balance – This defines the destination selection policy used to select a server to route the incoming connections to.
mode tcp – Galera Cluster uses TCP type of connections.
option tcpka – Enables the keepalive function to maintain TCP connections.
option mysql-check user <username> – Define backend database server check, to determine whether the node is currently operational. This was created in How to Setup MariaDB Galera Cluster on Ubuntu 18.04 with HAProxy
server <server-name> <IP_address> check weight 1 – Defines the nodes you want HAProxy to use in routing connections.

Restart haproxy and test connection:

sudo systemctl restart haproxy

Testing DB access via HAProxy

Confirm that HAProxy has a bind on port 3306:

# ss -tunelp | grep 3306
tcp    LISTEN   0        128         10.131.69.129:3306          0.0.0.0:*       users:(("haproxy",pid=24226,fd=5)) ino:87300 sk:2 <->  

Try to connect from HAProxy to port 3306:

root@haproxy-01:~# mysql -u test_user -p -h 10.131.69.129
Enter password: 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 743
Server version: 10.3.7-MariaDB-1:10.3.7+maria~bionic-log mariadb.org binary distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| galera_test        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

Recommended MySQL/SQL Study books:

• Getting Started With SQL – A Hands-On Approach for Beginners – a simple, to-the-point introductory read that’ll touch on the practical implications of SQL. Here, a reader gets introduced concisely to all the basics of the language;
• Head First SQL – Your Brain on SQL – A Learner’s Guide;
• SQL Cookbook: Query Solutions and Database Techniques for Database Developers – a book is full of hacks and tips that can be applied in day-to-day database management;
• Teach Yourself MS SQL Server – a fairly old book, yet, it covers all the aspects of SQL Server on a high level;
• Effective SQL – an easy-to-read guide book that explores SQL features. Keep in mind that you might need some SQL knowledge to apply the ideas that have been laid out.