Building a Highly Available MariaDB Galera Cluster: A Beginner's Guide

Have you ever worried about your database server crashing and taking your whole application down with it? Enter MariaDB Galera Cluster.

Galera is a synchronous multi-master cluster for MariaDB. In plain English: it links multiple database servers together so that if you write data to one, it instantly copies to all the others. If one server dies, the others keep working seamlessly.

In this tutorial, we will start with one server, grow it to a three-node cluster, test data replication, simulate a server failure, and learn how to read the error logs.

🗺️ The Big Picture

For this tutorial, let's pretend we have three basic Ubuntu Linux servers.

• Node 1 (The Starter): IP 10.0.0.1

• Node 2 (The Addition): IP 10.0.0.2

• Node 3 (The Completer): IP 10.0.0.3

       [ Node 1 ]
      /          \
     /            \
[ Node 2 ] ---- [ Node 3 ]

Graphic: All nodes talk to each other to keep data perfectly synced.

Prerequisite: Ensure you have root (or sudo) access on all servers, and that their firewalls allow traffic on ports 3306, 4567, 4568, and 4444.

Step 1: Setting Up the First Node (Node 1)

First, we need to install MariaDB and start the cluster. We call starting the very first node "bootstrapping."

1. Install MariaDB:

Log into Node 1 and run:

sudo apt update
sudo apt install mariadb-server mariadb-client

2. Configure the Cluster Settings:

Open the Galera configuration file using your favorite text editor (like nano):

sudo nano /etc/mysql/mariadb.conf.d/60-galera.cnf

Add the following basic settings:

[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

# Cluster Configuration
wsrep_cluster_name="my_first_cluster"
wsrep_cluster_address="gcomm://10.0.0.1,10.0.0.2,10.0.0.3"

# Node Configuration
wsrep_node_address="10.0.0.1"
wsrep_node_name="Node1"

Save and close the file.

3. Bootstrap the Cluster:

Stop the normal MariaDB service, then use the special bootstrap command. This tells Node 1, "Hey, you are the leader, create a new cluster!"

sudo systemctl stop mariadb
sudo galera_new_cluster

4. Check Your Work:

Log into MySQL:

sudo mysql -u root -p

Run this command to check the cluster size:

SHOW STATUS LIKE 'wsrep_cluster_size';

Result: You should see the value is 1.

Step 2: Adding the Second Node (Node 2)

Now let's expand.

1. Install MariaDB on Node 2:

sudo apt update
sudo apt install mariadb-server mariadb-client
sudo systemctl stop mariadb

2. Configure Node 2:

Open the same config file on Node 2 (/etc/mysql/mariadb.conf.d/60-galera.cnf) and paste the exact same configuration as Node 1, BUT change the Node Configuration part at the bottom:

# Node Configuration
wsrep_node_address="10.0.0.2"
wsrep_node_name="Node2"

3. Join the Cluster:

Since the cluster already exists (thanks to Node 1), we just start MariaDB normally!

sudo systemctl start mariadb

4. Check Your Work:

Log into MySQL on Node 2 and run SHOW STATUS LIKE 'wsrep_cluster_size';.

Result: The value should now be 2.

Step 3: Adding the Third Node (Node 3)

Galera works best with odd numbers (3, 5, 7) to prevent "split-brain" scenarios (where servers disagree on who has the right data).

Repeat the exact same process from Step 2 on Node 3, but update the config file to:

# Node Configuration
wsrep_node_address="10.0.0.3"
wsrep_node_name="Node3"

Start MariaDB: sudo systemctl start mariadb.

Check the cluster size again. Result: The value should now be 3.

Congratulations! You have a fully functioning 3-node cluster!

Step 4: Demonstrating Data Inserts

Let's prove this actually works.

1. On Node 1: Create a database and insert some data.

CREATE DATABASE test_db;
USE test_db;
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
INSERT INTO users (name) VALUES ('Alice'), ('Bob');

2. On Node 2 or Node 3: Let's see if the data magically appeared there.

USE test_db;
SELECT * FROM users;

Result: You will instantly see Alice and Bob on the other servers. You can write data to Node 3, and it will instantly show up on Node 1. That's the magic of a multi-master cluster!

Step 5: Handling a Faulty Node and Rejoining a Fresh One

Servers break. Hard drives fail. Let's simulate a total disaster on Node 3.

1. Simulate the Crash:

Log into Node 3 and brutally kill the server process (or just stop the service to be nice):

sudo systemctl stop mariadb

If you check wsrep_cluster_size on Node 1, it will now say 2. The cluster survives!

2. Bring in a Fresh Node:

Let's pretend Node 3 is dead forever. We bought a brand new server, Node 4 (IP 10.0.0.4). How do we add it?

• Install MariaDB on Node 4.

• Copy the 60-galera.cnf file from Node 1.

• Update wsrep_cluster_address="gcomm://10.0.0.1,10.0.0.2,10.0.0.4" (Update this on all active nodes when you get a chance).

• Update the Node details:

  wsrep_node_address="10.0.0.4"
  wsrep_node_name="Node4"
  
  

• Start the service: sudo systemctl start mariadb

What happens next?

Node 4 will reach out to Node 1 or 2. It will say, "I am empty, please send me everything." This is called an SST (State Snapshot Transfer). The active nodes will automatically copy all the databases (test_db, etc.) to Node 4. Once finished, the cluster size returns to 3.

Step 6: Finding and Detecting Errors

When things go wrong, don't panic. Here is how to find out what happened.

1. Where are the logs?

By default on Ubuntu, MariaDB errors are logged in the system journal or the syslog.

• To see the live MariaDB log: sudo journalctl -u mariadb -f

• To check the main system log: sudo tail -n 100 /var/log/syslog

2. Key Error Keywords to Look For:

When reading the logs, look for these specific terms:

• [ERROR] WSREP: This indicates a Galera-specific error.

• SST failed: This means a new node tried to download data from the cluster but failed (often a firewall issue blocking port 4444).

• Inconsistent: This means the data on one server doesn't match the others.

3. The Best Troubleshooting Commands:

If a node won't start, run these inside MySQL on an active node to check cluster health:

• SHOW STATUS LIKE 'wsrep_local_state_comment';

  (Good result: "Synced". Bad result: "Desynced" or "Joining")

• SHOW STATUS LIKE 'wsrep_cluster_status';

  (Good result: "Primary". Bad result: "Non-Primary" meaning it lost connection to the majority)

• SHOW STATUS LIKE 'wsrep_ready';

  (Good result: "ON")

Happy clustering! By setting up Galera, you've taken a massive step toward making your applications crash-proof.