How to add new a node to a Galera Replication Cluster on Ubuntu 16

Galera Cluster is a powerful multi-master synchronous replication library for databases such as MySQL or MariaDB. Using it, you can scale and replicate databases for maximum reliability and minimal risk of data loss.

Unlike MySQL’s built in replication features, each node in a Galera Cluser can be read from and written to synchronously, in what’s known as a master-master setup. Failed nodes will automatically be updated by the other nodes on coming back online through what’s called a quorum, in which a plurality of nodes determines what data should be replicated.

This guide will lead you though the steps of adding a new server, or node, to a pre-existing Galera Replication Cluster.

Getting started

In order to perform the steps of this guide, you will need the following beforehand:
• A server running a fresh installation of Ubuntu 16.04 LTS
• Root access to the server

Step-by-step guide

For the purposes of this guide, our scenario is that we’re adding a node to the actual Galera setup with 2 nodes. This will make a cluster composed of 3 nodes total.

First, install the software-properties package on your system.

apt-get install software-properties-common -y
apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8

We’ll be using MariaDB for our transactional database. In order to install it, you will need to add the MariaDB 10.1 repository.

add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://ftp.utexas.edu/mariadb/repo/10.1/ubuntu xenial main'

Update your repos list. Now you can install MariaDB-Server from the new repository.

apt-get update
apt-get install mariadb-server -y

You don’t want MariaDB to start yet, so make sure to stop it on node3 only. Keep it running on the other nodes.

systemctl stop mysql.service

We will now create the Galera config file. Here’s the example IP addresses we will be using:
• node1: 10.0.0.119
• node2: 10.0.0.120
• node3: 10.0.0.121

Replace with your own IP addresses as necessary.

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

[mysqld]
#mysql settings
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_doublewrite=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0
#galera settings
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="test_cluster"
wsrep_cluster_address=gcomm://10.0.0.119,10.0.0.120,10.0.0.121
wsrep_sst_method=rsync

Make sure to add all the IPs into the config file for the new node. You’ll also need to add the IP for the new node in the config files of node1 and node2.

Now, you can start the MariaDB daemon back up on node3. (Not the other two nodes, because it still should be running on those.)

systemctl start mysql.service

Check if node3 is linked to the cluster.

mysql -u root -p -e "show status like 'wsrep_cluster_size'"

If it is, you should see this output:

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

The final step is to check that the database on the three nodes has been synced. Here are the commands for checking on the different nodes:

On Node1

mysql -u root -p

show databases like ‘galera_test’;
+------------------------+
| Database (galera_test) |
+------------------------+
| galera_test |
+------------------------+
1 row in set (0.00 sec)

On Node2

mysql -u root -p

show databases like ‘galera_test’;
+------------------------+
| Database (galera_test) |
+------------------------+
| galera_test |
+------------------------+
1 row in set (0.00 sec)

On Node3

mysql -u root -p

show databases like ‘galera_test’;
+------------------------+
| Database (galera_test) |
+------------------------+
| galera_test |
+------------------------+
1 row in set (0.00 sec)

Conclusion

Now that you have the basic steps down, you can use them to continue to add nodes to your cluster. It’s recommended to stick with an odd number of nodes as that will prevent conflicts in the case that a plurality cannot be achieved when a node goes down.