How to set up a MariaDB Galera cluster on Ubuntu 20.04

Setting up a MariaDB cluster with Galera Cluster improves the reliability and simplifies the scaling of your database. In our tutorial, we’ll show you how to install a Galera cluster on Ubuntu 20.04.

The requirements for a Galera Cluster MariaDB

To install a MariaDB Galera cluster on Ubuntu 20.04, you must fulfill the following requirements:

• Multiple servers: At least three servers or virtual machines are required to build a cluster. All servers should be on the same network.
• Root rights: You need root access or at least administrative rights on the servers.

How to set up Galera Cluster MariaDB on Ubuntu 20.04 step by step

A MariaDB Galera cluster is a practical database solution that provides high availability, data integrity and scalability for demanding applications. In this step-by-step guide, we’ll walk you through the process of setting up a MariaDB Galera cluster on Ubuntu 20.04. Before you begin, make sure you have at least three servers or virtual machines available to set up the cluster.

Step 1: Update packages

You should first update your Ubuntu system to the latest version. This is essential so that you have up-to-date packages and security updates.

Open a terminal and execute the following command to update the package sources:

sudo apt update

Install all available updates:

sudo apt upgrade -y

We recommend also removing outdated or unused packages to clean up the servers:

sudo apt autoremove -y

Step 2: Install MariaDB on the servers

Now, install MariaDB on all of your servers. Starting from version 10.1, the Galera packages are bundled with MariaDB servers.

sudo apt install mariadb-server

After installation, you can start the MariaDB service:

sudo systemctl start mariadb

Use the following command to set MariaDB to be activated automatically each time the system is restarted:

sudo systemctl enable mariadb

Check whether MariaDB is running:

sudo systemctl status mariadb

You should also make some basic security settings and configure the database. To do this, start the setup wizard:

sudo mysql_secure_installation

The wizard guides you through the steps, such as setting a root password, removing anonymous users or removing the test database.

Enter current password for root (enter for none): 
Switch to unix_socket authentication [Y/n] n
Change the root password? [Y/n] Y
New password:
Re-enter new password:
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] Y
Reload privilege tables now? [Y/n] Y

The script first asks for the current root password. If you are installing MariaDB on Ubuntu 20.04 for the first time, simply press Enter and then enter a new password.

Step 3: Configure Galera Cluster MariaDB nodes

Create a cnf file in the directory /etc/mysql/conf.d on each node to define the Galera-specific settings.

nano /etc/mysql/conf.d/galera.cnf

This file contains general database settings, such as the binary protocol format and the default storage engine. It also contains configurations for the Galera cluster, including the cluster name and the cluster address.

Insert the following lines for the first node:

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://node_1-ip-address,node_2-ip-address,node_3-ip-address"
# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="node_1-ip-address"
wsrep_node_name="node_1"

• General database settings: This includes settings such as binlog_format=ROW for the format of the binary logs and default-storage-engine=innodb for the default storage engine.
• Galera provider configuration: Settings like wsrep_on=ON are used to enable Galera replication and wsrep_provider=/usr/lib/galera/libgalera_smm.so to specify the path to the Galera library.
• Galera cluster configuration: This includes the cluster name (wsrep_cluster_name) and the cluster address (wsrep_cluster_address), which contains the IP addresses or hostnames of the nodes in the cluster.
• Galera synchronization configuration: Configures the method for the State Snapshot Transfer (SST), e.g., wsrep_sst_method=rsync.
• Galera node configuration: Defines the IP address or hostname of the current node (wsrep_node_address) and the name of the node (wsrep_node_name).

After you’ve saved the file, create one for the second node:

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://node_1-ip-address,node_2-ip-address,node_3-ip-address"
# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="node_2-ip-address"
wsrep_node_name="node_2"

Now continue with the last node:

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://node_1-ip-address,node_2-ip-address,node_3-ip-address"
# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="node_3-ip-address"
wsrep_node_name="node_3"

Step 4: Change the firewall on servers

As the nodes communicate with each other via specific ports, you must adjust the Firewall settings.

Open the following Ports in your firewall:

• Port 3306: This is the standard port for MariaDB. It is used for database communication and requests.
• Galera ports: In addition to the standard port 3306, Galera also uses other ports for internal communication between the nodes. The standard range for Galera ports is usually 4567, 4568 and 4444 for the State Snapshot Transfer (SST).

You can define the Firewall settings on your Ubuntu server with the following command:

sudo ufw allow 3306,4567,4568,4444/tcp
sudo ufw allow 4567/udp

Step 5: Start Galera Cluster MariaDB

Stop the MariaDB service if it’s already running:

sudo systemctl stop mariadb

This command starts the MariaDB server and initializes a new Galera cluster on the first node:

sudo galera_new_cluster

Check the number of nodes in the cluster:

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

You should receive the following output:

+---------------------------+-------------+
| Variable_name        | Value       |
+--------------------------+--------------+
| wsrep_cluster_size | 1              |
+--------------------------+------------ -+

The first node was started successfully.

Activate the second node:

systemctl start mariadb

Check whether the number of nodes has increased:

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

In the console we see:

+---------------------------+-------------+
| Variable_name        | Value       |
+--------------------------+--------------+
| wsrep_cluster_size | 2               |
+--------------------------+--------------+

Now we start the third node:

systemctl start mariadb

Check whether the node is running properly:

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

There should now be three nodes in the cluster:

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

Step 6: Test the replication

Make sure that you can establish a connection to every node in the cluster. Use the MariaDB client to log in as the root user or as another user with sufficient rights.

mysql -u root -p

Create a new test database on one of the nodes in the cluster:

CREATE DATABASE test_db;

Log in to the other nodes and check whether the test database is available:

SHOW DATABASES;

The test database should appear in the list of databases:

+-------------------------------+
| Database                        |
+-------------------------------+
| information_schema   |
| mysql                              |
| performance_schema |
| test_db                          | 
| sys                                  |
+------------------------------+

Add a new test table to the test database:

USE test_db;
CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

Enter some test data in the name column of the test table:

INSERT INTO test_table (name) VALUES ('Alice'), ('Bob'), ('Charlie');

Check on the other nodes to see if the test table and the inserted data have been transferred:

USE test_db;
SELECT * FROM test_table;

The output shows us the list of people with their names and ID:

+----+-----------+
| id | name    |
+----+-----------+
| 1  | Alice     |
| 2  | Bob       |
| 3  | Charlie |
+----+----------+

To update a data record in the test table:

UPDATE test_table SET name = 'David' WHERE name = 'Alice';

Try deleting a data record:

DELETE FROM test_table WHERE name = 'Bob';

Check on the other nodes to see if the updates and deletions have been replicated:

SELECT * FROM test_table;

The changes appear successfully on every node:

+----+------------+
| id | name     |
+----+-----------+
| 1  | David    |
| 3  | Charlie  |
+----+-----------+