In our recent guide on How to Setup MariaDB Galera Cluster on Ubuntu 18.04 with HAProxy, we covered all the steps to install and configure 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 a central control of Galera Cluster nodes for things like maintenance. If you want to remove one of the nodes, you only need to change 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.
$ sudo apt-get update
$ suod apt-get upgrade
$ sudo apt-get install haproxy
Confirm installed version of HAProxy:
# apt policy haproxy
*** 1.8.8-1ubuntu0.1 500
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:
# vim /etc/hosts
10.131.74.92 node-01 galera-db-01
10.131.35.167 node-02 galera-db-02
10.131.65.13 node-03 galera-db-03
HAProxy Destination Selection Policies
HAProxy will select 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
- 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
- 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
- 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.
- 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
Open HAProxy main configuration file:
$ sudo vim /etc/haproxy/haproxy.cfg
Your configuration should have settings similar to below:
# Galera Cluster Frontend configuration
# Galera Cluster Backend configuration
option mysql-check user haproxy
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 <->
You can install MariaDB Clients on the HAProxy server for testing:
$ sudo apt-get install mariadb-client
Try to connect from HAProxy to port 3306:
root@haproxy-01:~# mysql -u test_user -p -h 10.131.69.129
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)