In this blog post, we’re going to explore How to Setup MariaDB Galera Cluster on Ubuntu 22.04|20.04|18.04 using 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 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.
Setup MariaDB Galera Cluster on Ubuntu 22.04|20.04|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.
1. Map IP addresses in /etc/hosts
In preparation for this, let’s populate /etc/hosts with correct short hostnames and IP addresses for all servers.
$ sudo vim /etc/hosts
10.131.74.91 galera-haproxy-01
10.131.74.92 galera-db-01
10.131.74.93 galera-db-02
10.131.74.94 galera-db-03You 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.
2. Install MariaDB database server on all nodes
We will start by Installing MariaDB on all Galera cluster nodes:
- galera-db-01
- galera-db-02
- galera-db-03
Install MariaDB database server package.
curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s --
sudo apt update
sudo apt install vim mariadb-server mariadb-client3. 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.
sudo vim /etc/mysql/mariadb.conf.d/50-server.cnfUncomment these lines:
character-set-server = utf8
character_set_server = utf8Then add Galera specific configurations:
sudo vim /etc/mysql/mariadb.conf.d/galera.cnfAdd below on galera-db-01 Galera configuration file:
[mysqld]
bind-address=0.0.0.0
default_storage_engine=InnoDB
binlog_format=row
innodb_autoinc_lock_mode=2
# Galera cluster configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.131.74.92,10.131.74.93,10.131.74.94"
wsrep_cluster_name="mariadb-galera-cluster"
wsrep_sst_method=rsync
# Cluster node configuration
wsrep_node_address="10.131.74.92"
wsrep_node_name="galera-db-01"Remember to replace 10.131.74.92,10.131.74.93,10.131.74.94 with Galera cluster nodes IP Addresses.
4. Configure other Galera Cluster Nodes
We now need to add configurations to Galera cluster node 2 and node 3.
For galera-db-02:
root@galera-db-02:~# vim /etc/mysql/mariadb.conf.d/galera.cnf
[mysqld]
bind-address=0.0.0.0
default_storage_engine=InnoDB
binlog_format=row
innodb_autoinc_lock_mode=2
# Galera cluster configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.131.74.92,10.131.74.93,10.131.74.94"
wsrep_cluster_name="mariadb-galera-cluster"
wsrep_sst_method=rsync
# Cluster node configuration
wsrep_node_address="10.131.74.93"
wsrep_node_name="galera-db-02"For galera-db-03:
root@galera-db-03:~# vim /etc/mysql/mariadb.conf.d/galera.cnf
[mysqld]
bind-address=0.0.0.0
default_storage_engine=InnoDB
binlog_format=row
innodb_autoinc_lock_mode=2
# Galera cluster configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.131.74.92,10.131.74.93,10.131.74.94"
wsrep_cluster_name="mariadb-galera-cluster"
wsrep_sst_method=rsync
# Cluster node configuration
wsrep_node_address="10.131.74.94"
wsrep_node_name="galera-db-03"You must have noted that what changes on each node is Cluster node configuration
5. Start Galera Cluster on Ubuntu
Stop mariadb on all three nodes:
sudo systemctl stop mariadbNow start new Galera cluster on node 1 – galera-node-01:
sudo galera_new_clusterCheck Galera status if it’s running:
$ mysql -u root -p -e "show status like 'wsrep_%'"
Enter password:
+------------------------------+--------------------------------------+
| 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 | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | 9f957c6d-76b4-11e8-a71a-17cc0eca13f1 |
| wsrep_cluster_status | Primary |
| 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 | 9f945140-76b4-11e8-84c6-a66f9e2978f6 |
| wsrep_incoming_addresses | 10.131.74.92: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 | 9f957c6d-76b4-11e8-a71a-17cc0eca13f1 |
| wsrep_protocol_version | 8 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <[email protected]> |
| wsrep_provider_version | 25.3.23(r3789) |
| wsrep_ready | ON |
| wsrep_received | 2 |
| wsrep_received_bytes | 148 |
| 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 |
+------------------------------+--------------------------------------+Initial cluster size should be 1
root@galera-db-01:~# 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:
sudo systemctl start mariadbCheck 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:
sudo systemctl start mariadbCheck 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.
6. 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:
root@galera-node-01:~# mysql -u root -p
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)7. Install and Configure HAProxy
For installation and Configuration of HAProxy as Load Balancer for MariaDB Galera Cluster, refer to our guide:
If working with MySQL command line is not your thing, then consider installing a Database Tool to help you. Check out our guide below:
Install and Configure DBeaver on Ubuntu / Debian
That’s all for today. Please subscribe to get the latest updates directly on your email.
Search Tags:
- How to Install MariaDB Galera Cluster on Ubuntu
- MariaDB Galera Cluster on Ubuntu
- MariaDB Galera Cluster on Ubuntu
- Galera Cluster High Availability on Ubuntu
- MariaDB Setup on Ubuntu with Galera Cluster
Whats the point of configuring the hostnames in /etc/hosts if you have to configure MariaDB with IPs?
For cluster operations not necessary when IPs are used. But if you choose to use names it is a requirement.