Please log in or register to answer this question.

1 Answer

0 like 0 dislike
answered by (13.4k points)  

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
Installed: 1.8.8-1ubuntu0.1
Candidate: 1.8.8-1ubuntu0.1
Version table:
*** 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  galera-haproxy-01   node-01 galera-db-01  node-02 galera-db-02   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 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
    mode tcp
    option tcplog
    default_backend galera_cluster_backend

# Galera Cluster Backend configuration
backend galera_cluster_backend
    mode tcp
    option tcpka
    balance leastconn
    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*       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
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 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)

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer