MariaDB Galera Cluster in VM Instances

May 11, 2020 6-minute read

Galera • MariaDB • Google Cloud

Introduction

A MariaDB Galera Cluster is an active-active multi-node solution that allows you read and write in any cluster node. According to documentation:

MariaDB Galera Cluster is a virtually synchronous multi-master cluster for MariaDB.

… the Server replicates a transaction at commit time by broadcasting the write set associated with the transaction to every node in the cluster.

We are going to build a three-node Galera Cluster using Virtual Machine Instances on Google Cloud Platform.

Create Galera Cluster Instances

Using our customized MariaDB VM Instance Image and Template, create three instances.

Create the Primary instance patomariagm from the Cloud Console:
On Google Cloud Platform - Compute Engine - VM Instances - Create Instance - New VM Instance from template

Select template: patomariadb-template
Name: patomariagm
Networking
- Hostname: patomariagm.databases

and to test the command option, create the other instances patomariag1 and patomariag2 using the Cloud Shell:

$ gcloud compute instances create patomariag1 --hostname patomariag1.databases --source-instance-template patomariadb-template
Created [https://www.googleapis.com/compute/v1/projects/databases-20202/zones/us-central1-f/instances/patomariag1].

$ gcloud compute instances create patomariag2 --hostname patomariag2.databases --source-instance-template patomariadb-template
Created [https://www.googleapis.com/compute/v1/projects/databases-20202/zones/us-central1-f/instances/patomariag2].

Validate all instances are up and running.

$ gcloud compute instances list
NAME          ZONE           MACHINE_TYPE  PREEMPTIBLE  INTERNAL_IP  EXTERNAL_IP     STATUS
patomariag1   us-central1-f  f1-micro                   10.128.0.48  35.223.249.103  RUNNING
patomariag2   us-central1-f  f1-micro                   10.128.0.49  34.66.218.222   RUNNING
patomariagm   us-central1-f  f1-micro                   10.128.0.47  35.226.17.216   RUNNING

Create Objects and Data

The Primary node is expected to be the source of the data we want to replicate in the cluster so let’s create some objects and data for our testing.

Access via ssh your Primary node patomariagm and connect to MariaDB to create a database, a table and insert a row identifying the hostname and time:

pato@patomariagm:~$ mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 36
Server version: 10.3.22-MariaDB-0ubuntu0.19.10.1 Ubuntu 19.10

MariaDB [(none)]> create database taller;
Query OK, 1 row affected (0.040 sec)

MariaDB [(none)]> use taller
Database changed
MariaDB [taller]> create table tg1 (i1 int auto_increment primary key, c2 varchar(20), d3 datetime) ;
Query OK, 0 rows affected (0.018 sec)

MariaDB [taller]> insert into tg1 (c2,d3) values (@@hostname,now());
Query OK, 1 row affected (0.004 sec)

MariaDB [taller]> commit ;
Query OK, 0 rows affected (0.000 sec)

Galera Cluster Configuration

Once we have our 3 instances ready we need to configure them for Galera replication.

Connect to the Primary server patomariagm, stop MariaDB service and create a new Galera configuration file.

pato@patomariagm:~$ sudo systemctl stop mariadb
pato@patomariagm:~$ sudo nano /etc/mysql/conf.d/galera.cnf

and add the following options:

Turn on replication and setup library

[mariadb]
# Galera Replication Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

Define Cluster name and list of member addresses

# Galera Cluster Name and Members
wsrep_cluster_name="patocluster"
wsrep_cluster_address="gcomm://patomariagm,patomariag1,patomariag2"

Identify this member of the Cluster

# Galera Cluster Member
wsrep_node_name="patomain"
wsrep_node_address="patomariagm"

Define Primary node as the source for the initial copy of data

# Galera State Snapshot Transfer Full Data Copy
wsrep_sst_method=rsync
wsrep_sst_donor="patomain"

Enable Global Transaction ID

# Galera GTID Configuration
wsrep_gtid_mode=ON
wsrep_gtid_domain_id=1

Binary log and Storage engine configuration

# MariaDB Configuration
binlog_format=ROW
default-storage-engine=InnoDB
innodb_autoinc_lock_mode=2
log_slave_updates=ON
log_bin=galera-bin

Connect to the other two nodes, stop MariaDB service and create the same configuration file only changing the member identification parameters:

pato@patomariag1:~$ sudo systemctl stop mariadb
pato@patomariag1:~$ sudo nano /etc/mysql/conf.d/galera.cnf

# Galera Cluster Member
wsrep_node_name="patonode1"
wsrep_node_address="patomariag1"

pato@patomariag2:~$ sudo systemctl stop mariadb
pato@patomariag2:~$ sudo nano /etc/mysql/conf.d/galera.cnf

# Galera Cluster Member
wsrep_node_name="patonode2"
wsrep_node_address="patomariag2"

Start Galera Cluster Nodes

In a Galera Cluster the Primary node should be the first one to start in order to initialize the cluster, as described in Galera Cluster documentation

… you will need to start the mysqld daemon on one node, using the –wsrep-new-cluster option. This initializes the new Primary Component for the cluster. Each node you start after that will connect to the component and begin replication.

Starting in MariaDB version 10.4, which includes Galera version 4, you can enter instead the following from the command-line to start MariaDB, Galera, and to establish the Primary Component: galera_new_cluster

So we are going to start each node in order and validate how many members are registered in the cluster.

pato@patomariagm:~$ sudo galera_new_cluster

pato@patomariagm:~$ mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+

pato@patomariag1:~$ sudo systemctl start mariadb
pato@patomariag1:~$ mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+

pato@patomariag2:~$ sudo systemctl start mariadb
pato@patomariag2:~$ mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

OK our Galera cluster is setup and running and all members has joined!

Test Replication on Galera Cluster

Now we are going to test that replication is working.

Validate Initial Replication

First we want to validate that the objects and data created before the cluster setup, were replicated to the new nodes:

pato@patomariag1:~$ mysql

MariaDB [(none)]> use taller
Database changed
MariaDB [taller]> select * from tg1;
+----+-------------+---------------------+
| i1 | c2          | d3                  |
+----+-------------+---------------------+
|  1 | patomariagm | 2020-05-11 17:55:19 |
+----+-------------+---------------------+
1 row in set (0.001 sec)

pato@patomariag2:/var/log/mysql$ mysql

MariaDB [(none)]> use taller
Database changed
MariaDB [taller]> select * from tg1;
+----+-------------+---------------------+
| i1 | c2          | d3                  |
+----+-------------+---------------------+
|  1 | patomariagm | 2020-05-11 17:55:19 |
+----+-------------+---------------------+
1 row in set (0.000 sec)

Our original data was replicated in every node, then let’s test the future data replication.

Validate Replication Node to Node

As a Galera Cluster allows every node to be updated, we are going to insert a row on each node, identifying the instance that issues the insertion with @@hostname and the time of the operation with now():

pato@patomariagm:~$ mysql

MariaDB [taller]> insert into tg1 (c2,d3) values (@@hostname,now());
Query OK, 1 row affected (0.120 sec)

MariaDB [taller]> commit;
Query OK, 0 rows affected (0.000 sec)

pato@patomariag1:~$ mysql

MariaDB [taller]> insert into tg1 (c2,d3) values (@@hostname,now());
Query OK, 1 row affected (0.005 sec)

MariaDB [taller]> commit;
Query OK, 0 rows affected (0.000 sec)

pato@patomariag2:~$ mysql

MariaDB [taller]> insert into tg1 (c2,d3) values (@@hostname,now());
Query OK, 1 row affected (0.004 sec)

MariaDB [taller]> commit;
Query OK, 0 rows affected (0.000 sec)

and check the result also in every node:

pato@patomariagm:~$ mysql -e "select * from taller.tg1"
+----+-------------+---------------------+
| i1 | c2          | d3                  |
+----+-------------+---------------------+
|  1 | patomariagm | 2020-05-11 17:55:19 |
|  4 | patomariagm | 2020-05-11 18:35:42 |
|  5 | patomariag1 | 2020-05-11 18:36:03 |
|  6 | patomariag2 | 2020-05-11 18:37:01 |
+----+-------------+---------------------+

pato@patomariag1:~$ mysql -e "select * from taller.tg1"
+----+-------------+---------------------+
| i1 | c2          | d3                  |
+----+-------------+---------------------+
|  1 | patomariagm | 2020-05-11 17:55:19 |
|  4 | patomariagm | 2020-05-11 18:35:42 |
|  5 | patomariag1 | 2020-05-11 18:36:03 |
|  6 | patomariag2 | 2020-05-11 18:37:01 |
+----+-------------+---------------------+

pato@patomariag2:~$ mysql -e "select * from taller.tg1"
+----+-------------+---------------------+
| i1 | c2          | d3                  |
+----+-------------+---------------------+
|  1 | patomariagm | 2020-05-11 17:55:19 |
|  4 | patomariagm | 2020-05-11 18:35:42 |
|  5 | patomariag1 | 2020-05-11 18:36:03 |
|  6 | patomariag2 | 2020-05-11 18:37:01 |
+----+-------------+---------------------+

As we can see a row was inserted en each node and data was replicated to the other nodes!

Conclusion

We were able to setup a three-node Maria DB Galera Cluster using Virtual Machine Instances on Google Cloud Platform and validated we can update any table in any node and those changes are replicated to every node.