MariaDB cluster with Galera and HAProxy on Ubuntu 18.04

In this article, we will show you how to install and configure a MariaDB cluster with Galera. We will also add HAProxy to balance the load between 2 MariaDB nodes.
A MariaDB cluster must be set up with an odd number of nodes. The reason is simple: if there are only two nodes and one of them fails, there is no way to know which one is corrupt.

At this point, we can set up our cluster with 3 MariaDB nodes or 2 nodes + Galera Arbitrator. Galera Arbitrator does not participate in replication; it receives the same data as all the other nodes and participates in decision-making. Therefore, if there is a problem with a node, the response from the non-corrupt node + Galera Arbitrator will prevail.

In this article, we will use 4 servers with Ubuntu 18.04:

• kb-mariadb-arbiter 10.20.10.33
• kb-mariadb-01 10.20.10.34
• kb-mariadb-02 10.20.10.35
• kb-haproxy-mariadb 10.20.10.36

Install and configure kb-mariadb-01

To install MariaDB, we will first add the MariaDB repositories for Ubuntu 18.04 and then install MariaDB:

# apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
# add-apt-repository 'deb [arch=amd64] http://mirror.zol.co.zw/mariadb/repo/10.3/ubuntu bionic main'
# apt update && apt install mariadb-server

Now that MariaDB is installed, we will configure the cluster. We will specify our cluster name in wsrep_cluster_name. We will specify the IPs of the other MariaDB node kb-mariadb-02 and kb-mariadb-arbiter in wsrep_cluster_address. Finally, we will configure the IP of kb-mariadb-01 in wsrep_node_address and the name kb-mariadb-01 in wsrep_node_name. We will do all of this in the file /etc/mysql/conf.d/cluster.cnf.

[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
bind-address=0.0.0.0
innodb_file_per_table
collation-server = utf8_general_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8
join_buffer_size = 1M

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"

# Galera Cluster Configuration
wsrep_cluster_name="kb_mariadb_cluster"
wsrep_cluster_address="gcomm://10.20.10.35,10.20.10.33"
 
# Galera Synchronization Configuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass
 
# Galera Node Configuration
# SPECIFY THE LOCAL SERVER DETAILS
wsrep_node_address="10.20.10.34"
wsrep_node_name="kb-mariadb-01"

We will now stop the MariaDB service.

# systemctl stop mariadb

We will configure variables and make MariaDB listen on all IPs. To do this, we will edit  /etc/mysql/my.cnf.

#bind-address = 127.0.0.1
max_connections = 500
expire_logs_days = 5
wait_timeout = 28800
max_allowed_packet = 1G
connect_timeout = 6

Install and configure kb-mariadb-02

To install MariaDB, we will first add the MariaDB repositories for Ubuntu 18.04 and then install MariaDB:

# apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
# add-apt-repository 'deb [arch=amd64] http://mirror.zol.co.zw/mariadb/repo/10.3/ubuntu bionic main'
# apt update && apt install mariadb-server

We will specify our cluster name in wsrep_cluster_name. We will specify the IPs of the other MariaDB node kb-mariadb-01 and kb-mariadb-arbiter in wsrep_cluster_address. Finally, we will configure the IP of kb-mariadb-02 in wsrep_node_address and the name kb-mariadb-02 in wsrep_node_name. We will do all of this in the file /etc/mysql/conf.d/cluster.cnf.

[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
bind-address=0.0.0.0
innodb_file_per_table
collation-server = utf8_general_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8
join_buffer_size = 1M

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"

# Galera Cluster Configuration
wsrep_cluster_name="kb_mariadb_cluster"
wsrep_cluster_address="gcomm://10.20.10.34,10.20.10.33"
 
# Galera Synchronization Configuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass
 
# Galera Node Configuration
# SPECIFY THE LOCAL SERVER DETAILS
wsrep_node_address="10.20.10.35"
wsrep_node_name="kb-mariadb-02"

We will now stop the MariaDB service:

# systemctl stop mariadb

We will now copy the MariaDB configuration files from the kb-mariadb-01 node:

# scp 10.20.10.34:/etc/mysql/debian.cnf /etc/mysql/
# scp 10.20.10.34:/etc/mysql/my.cnf /etc/mysql/

Start the cluster

All the configuration is now ready. We will start the MariaDB cluster by running the following on kb-mariadb-01:

# galera_new_cluster

We will now start MariaDB on the second node kb-mariadb-02:

# systemctl start mariadb

We can see that the cluster is working by running:

# mysql -u root -p -e "SHOW STATUS LIKE 'wsrep%';"

+------------------------------+--------------------------------------+
| Variable_name | Value |
+------------------------------+--------------------------------------+
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 0.000000 |
| wsrep_causal_reads | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_cert_index_size | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_cluster_conf_id | 2 |
| wsrep_cluster_size | 2 |
| wsrep_cluster_state_uuid | 6e923943-9e67-11e9-b2bb-e27d46dd5c77 |
| wsrep_cluster_status | Primary |
| wsrep_cluster_weight | 2 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_connected | ON |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_flow_control_sent | 0 |
| wsrep_gcomm_uuid | 6e912571-9e67-11e9-9021-6b0cd63bb1cd |
| wsrep_incoming_addresses | 10.20.10.34:3306,10.20.10.35:3306 |
| wsrep_last_committed | 0 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_cached_downto | 18446744073709551615 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_commits | 0 |
| wsrep_local_index | 0 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_local_recv_queue_max | 1 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_local_state_uuid | 6e923943-9e67-11e9-b2bb-e27d46dd5c77 |
| wsrep_open_connections | 0 |
| wsrep_open_transactions | 0 |
| wsrep_protocol_version | 9 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 25.3.26(r3857) |
| wsrep_ready | ON |
| wsrep_received | 6 |
| wsrep_received_bytes | 427 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_thread_count | 2 |
+------------------------------+--------------------------------------+
61 rows in set (0.004 sec)

As we can see in “wsrep_cluster_size”, the cluster consists of 2 nodes.

Install and configure arbiter

Now that our 2 nodes are running, we will configure a third one with Galera Arbitrator. As mentioned at the beginning, it will not replicate MySQL data, but it will receive changes, so if an inconsistency occurs on a node, it will help determine which node has no faults. To install Arbitrator, we will run:

# apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
# add-apt-repository 'deb [arch=amd64] http://mirror.zol.co.zw/mariadb/repo/10.3/ubuntu bionic main'
# apt install galera-arbitrator-3

We will now create a configuration file specifying the existing cluster details in /etc/default/garb.

# Copyright (C) 2012 Coedership Oy
# This config file is to be sourced by garb service script.
 
# A space-separated list of node addresses (address[:port]) in the cluster
GALERA_NODES="10.20.10.34:4567 10.20.10.35:4567"
 
# Galera cluster name, should be the same as on the rest of the nodes.
GALERA_GROUP="kb_mariadb_cluster"
 
# Optional Galera internal options string (e.g. SSL settings)
# see http://www.codership.com/wiki/doku.php?id=galera_parameters
GALERA_OPTIONS="pc.wait_prim=no"

We can now start and enable galera arbitrator:

# systemctl start garb
# systemctl enable garb

We can check that arbiter has been added by running the following on either of the 2 nodes with MariaDB:

# mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size';"

+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+

Install and configure HAProxy

HAProxy is software that allows us to balance the load between several servers. It is commonly used to load-balance web services, but it can load-balance any service that runs over the TCP protocol. In this case, we will use HAProxy to balance the load between our 2 MariaDB nodes. To install HAProxy, we will run:

# apt install haproxy

We will now configure HAProxy by adding the following code in vi /etc/haproxy/haproxy.cfg.

listen stats
        bind *:8083
        mode http
        stats enable
        stats uri /stats
        stats realm HAProxy\ Statistics
        stats auth haproxy:TestKB
# Load Balancing for Galera Cluster
listen kb-galera
     bind *:3306
     balance source
     mode tcp
     option tcpka
     option mysql-check user haproxy
     server kb-mariadb-01 10.20.10.34:3306 check weight 1
     server kb-mariadb-02 10.20.10.35:3306 check weight 1

We will create a user so that HAProxy can check the 2 nodes. On one of the 2 nodes, we will create a haproxy user:

MariaDB [(none)]> CREATE USER ‘haproxy’@’10.20.10.36’;

Our MariaDB cluster with load balancing is now running.