How to Setup MariaDB Galera Cluster on Ubuntu 18.04 with HAProxy
In this blog post, we’re going to explore How to Setup MariaDB Galera Cluster on Ubuntu 18.04 with HAProxy as Load Balancer. If you have been working with MariaDB – In place replacement of MySQL, you should definitely be aware of MariaDB Galera Cluster. If you are new to these terms, MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB with support for XtraDB/InnoDB storage engines.
Top features of MariaDB Galera Cluster are:
It provides active-active multi-master topology
You can read and write to any cluster node
It has automatic node joining
Automatic membership control, failed nodes drop from the cluster
Has true parallel replication, on row level
Direct client connections
From these features, you get the benefits like no loss in transactions or weird slave lag normally seen in database replication since all servers have up-to-date data. By using MariaDB Galera Cluster on Ubuntu 18.04 server, you also get scalability for both reads and writes with small latencies from connecting clients.
To get fair load balancing based on Roundrobin or least number of connections on the servers, we will make use of HAProxy which is a production-grade open source Load Balancer.
How to Setup MariaDB Galera Cluster on Ubuntu 18.04
When setting up MariaDB Galera Cluster, It is advisable to have an odd number of servers. Minimum being three servers. My Lab environment will be based on the diagram below:
I prefer using hostnames as opposed to hard-coded IP addresses in all my configurations. In preparation for this, let’s populate /etc/hosts with correct short hostnames and IP addresses for all servers.
You must have noticed that my servers are not on the same subnet. This is recommended to ensure there is no single point of failure on the networking side. You can test that everything is working using ping.
Step 1: Install MariaDB database server on Galera nodes
We will start by Installing MariaDB on all Galera cluster nodes:
galera-db-01
galera-db-02
galera-db-03
Use our previous guide Install MariaDB 10.3 on Ubuntu 18.04 and CentOS 7 to install MariaDB on Ubuntu 18.04. Once you are done with the installation of MariaDB database server on all nodes, proceed to next step.
Step 2: Configure First Galera Cluster node
When setting up Galera Cluster, you need to start with one node which will assume the role of master. Edit first node main configuration to configure default character set.
[email protected]:~# mysql -u root -p -e "show status like 'wsrep_cluster_size'"
Enter password:
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 1 |
+--------------------+-------+
On galera-node-02, start mariadb service:
# systemctl start mariadb
Check cluster size again, it should have changed to 2
# mysql -u root -p -e "show status like 'wsrep_cluster_size'"
Enter password:
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 2 |
+--------------------+-------+
Start mariadb on galera-db-03:
# systemctl start mariadb
Check cluster size again, it should be 3
# mysql -u root -p -e "show status like 'wsrep_cluster_size'"
Enter password:
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
If everything was set correctly, you can start testing.
Step 5: Test Galera Cluster Operation
To test our Galera cluster, we’re going to create a test database on one server and confirm that it has been replicated on other nodes. Log in to any member of Galera cluster as root user:
[email protected]:~# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 50
Server version: 10.3.7-MariaDB-1:10.3.7+maria~bionic-log mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> create database galera_test;
Query OK, 1 row affected (0.004 sec)
Log in to the other node and check if the database exists.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| galera_test |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
