Implementing multi-master replication with SQL Galera Cluster

26 Sep 2023

In today’s era of distributed systems and high availability requirements, implementing multi-master replication in a database cluster is of utmost importance. One popular open-source solution for achieving this is SQL Galera Cluster. In this blog post, we will explore the steps involved in setting up and configuring multi-master replication using SQL Galera Cluster.

What is SQL Galera Cluster?

SQL Galera Cluster is a synchronous multi-master replication solution for MySQL and MariaDB databases. It uses the Galera replication plugin to achieve high availability and data consistency across multiple database nodes. With Galera Cluster, all nodes in the cluster can accept read and write requests, providing a transparent and scalable solution for database replication.

Prerequisites

Before we begin, make sure you have the following prerequisites in place:

A group of servers running MySQL or MariaDB, which will be part of the Galera Cluster.
The same version of MySQL or MariaDB installed on each server.
A stable network connection between the cluster nodes.

Step 1: Installing SQL Galera Cluster

To start, we need to install SQL Galera Cluster on each node of the cluster. You can download the necessary packages from the official Galera Cluster website or use package managers like apt, yum, or dnf to install them.

For example, on a Ubuntu-based system, you can use the following command to install the necessary packages:

sudo apt-get install galera-4 mysql-server-5.7

Step 2: Configuring Galera Cluster

Once installed, we need to configure the Galera Cluster on each node. The configuration file for Galera Cluster is typically located at /etc/mysql/conf.d/galera.cnf. Open this file in a text editor and make the following changes:

Set wsrep_on=ON to enable the Galera replication plugin.
Specify a unique cluster name using wsrep_cluster_name.
Set wsrep_cluster_address to specify the IP addresses or hostnames of all the nodes in the cluster.
Set wsrep_node_address to the IP address or hostname of the current node.
Adjust other parameters such as wsrep_sst_method and wsrep_node_name as per your requirements.

[mysqld]
...
wsrep_on=ON
wsrep_cluster_name=my_cluster
wsrep_cluster_address=gcomm://node1_ip,node2_ip,node3_ip
wsrep_node_address=current_node_ip
...

Step 3: Starting Galera Cluster

After configuring Galera Cluster on each node, you can start the cluster by starting MySQL service on each node. Run the following command:

sudo systemctl start mysql

Step 4: Verifying Cluster Status

To verify the cluster status and ensure that the nodes are connected and synchronized, you can use the galera_new_cluster command on one of the nodes:

sudo galera_new_cluster

You can also use the mysql command-line client to connect to any of the nodes and run the following query to check the cluster status:

SHOW STATUS LIKE 'wsrep_%';

Conclusion

Implementing multi-master replication with SQL Galera Cluster provides a reliable and scalable solution for database replication in a distributed environment. By following the steps outlined in this blog post, you can set up and configure a multi-master replication cluster using SQL Galera Cluster, ensuring high availability and data consistency for your MySQL or MariaDB databases.

#techblog #database #replication #highavailability