Galera Cluster for MySQL/MariaDB

Overview: Galera Cluster is a synchronous multi-master database cluster designed for MySQL and MariaDB. It ensures high availability, redundancy, and scalability, making it ideal for distributed database systems.

Prerequisites

Three Debian-based servers (nodes) with private IPs.
MariaDB or MySQL installed on each node.
A firewall allowing ports 3306 (MySQL) and 4567, 4568, and 4444 (Galera ports).

Steps

Step 1: Install MariaDB or MySQL on Each Node

1. Update and install MariaDB on each node:

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

2. Confirm MariaDB version (Galera works with MariaDB version 10.1 and higher):

mysql -V

Step 2: Configure MariaDB for Galera on Each Node

1. Open the MariaDB configuration file:

sudo nano /etc/mysql/my.cnf

2. Add the following configurations under the [mysqld] section:

[mysqld]
bind-address=0.0.0.0
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://IP_NODE1,IP_NODE2,IP_NODE3"
wsrep_cluster_name="galera_cluster"
wsrep_node_name="node1" # Change accordingly for each node (node1, node2, node3)
wsrep_node_address="IP_NODE1" # Change accordingly for each node
wsrep_sst_method=rsync

3. Replace placeholders with actual IP addresses of each node.

Step 3: Start the Cluster on the First Node

1. Start MariaDB on the first node with Galera configurations:

sudo systemctl stop mariadb
sudo galera_new_cluster

2. Check the MariaDB status to confirm the cluster started:

sudo systemctl status mariadb

Step 4: Join the Second and Third Nodes to the Cluster

1. On node 2 and node 3:

Stop MariaDB:

sudo systemctl stop mariadb

Start MariaDB without initializing a new cluster:

sudo systemctl start mariadb

2. Verify Cluster Status:

On each node, log into MariaDB and check the cluster size:

SHOW STATUS LIKE 'wsrep_cluster_size';

You should see the cluster size as 3 if all nodes are connected.

Step 5: Test the Galera Cluster

1. Create a Database and Test Replication:

On any node, create a test database:

CREATE DATABASE test_db;

Verify on other nodes to ensure test_db appears, confirming replication.

Step 6: Automate Startup

1. Ensure MariaDB starts automatically with the server:

sudo systemctl enable mariadb

Clusters