Steps to configure Galera cluster in MariaDB

 Clustering adds high availability to your database by distributing changes to different servers. In the event that one of the instances fails, others are quickly available to continue serving.

Clusters come in two general configurations, active-passive and active-active. In active-passive clusters, all writes are done on a single active server and then copied to one or more passive servers that are poised to take over only in the event of an active server failure. Some active-passive clusters also allow SELECT operations on passive nodes. In an active-active cluster, every node is read-write and a change made to one is replicated to all.

We will configure an active-active MariaDB Galera cluster.

What we need?

• 3x Ubuntu 16.04 Servers in the same network, so that all nodes can access each other using private IPs

Let’s Start

We will start step by step to setup the cluster.

Step 01: Adding MariaDB Repository to the servers and Install it

MariaDB is not available by default from apt-get . So we need to add manually MariaDB repository. Although, you can also install from source.

sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://nyc2.mirrors.digitalocean.com/mariadb/repo/10.1/ubuntu xenial main'sudo apt-get update# Install MariaDB
sudo apt-get install mariadb-server

Althogh, rsync comes with MariaDB server installations, but make sure it is installed by:

sudo apt-get install rsync

Step 02: Configuring nodes

Each node should have exact configuration to be in cluster. So we will make changes in first node and copy it to rest 2 nodes.

By default, MariaDB is configured to check the /etc/mysql/conf.d directory to get additional configuration settings for from ending in .cnf. We will write our configurations in s/etc/mysql/conf.d/galera_cluster.cnf.

[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  # Galera Cluster Configuration 
wsrep_cluster_name="<name-of-cluster>" wsrep_cluster_address="gcomm://<first-pvt-ip>,<second-pvt-ip>,<third-pvt-ip>"  # Galera Synchronization Configuration 
wsrep_sst_method=rsync  # Galera Node Configuration 
wsrep_node_address="<this-node-ip>" 
wsrep_node_name="<this-node-name>"

Change the variables in <> according to needs and use private IPs in the configuration.

Step 03: Starting cluster

To bring up the first node, we’ll need to use a galera commands. Without using the galera_new_cluster script that allows systemd to pass the the --wsrep-new-cluster parameter, a normal systemctl start mysql would fail because there are no nodes running for the first node to connect with.

sudo galera_new_cluster

Now for rest of the two nodes, start them by:

sudo systemctl start mysql

Hurray! Your MariaDB Cluster is setup completed successfully. You can test the cluster by this:

$ mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+$

Now you can read/write in any nodes and it will be replicated to other nodes.