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 haproxyConfirm 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.cfgYour 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 haproxyTesting 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:
- Murach’s MySQL (3rd Edition)
- MySQL (5th Edition) (Developer’s Library)
- MySQL Explained: Your Step By Step Guide to Database Design
- 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.
