2.4 C
Amsterdam
Friday, January 15, 2021

Creating a Database replication cluster with MariaDB on Ubuntu 20.04

Must read

Please Donate

angeloma
angeloma
Senior Writer and partner

We often use MariaDB in our tutorials. In fact, along with PostgreSQL, they are the most powerful relational database management systems out there. At least, among the community alternatives. Then, we can make hateful comparisons against payment solutions like Oracle or DB2. The point is, MariaDB has very special features that make it quite good. One of those features is the possibility to create our own database cluster using MariaDB. The reality is that it is quite simple. So in this post, I will show you how to create a database replication cluster on Ubuntu 20.04

This is especially useful for making immediate database backups. Because the goal is to have the changes we make on node 1 replicated on node 2 automatically.

Cluster Nodes Specifications

As it is well known, a cluster is the union of several hardware or software nodes that work together as if they were one. In this case, we will use one to do database replication through a network.

So we will use two test nodes although this will work in several nodes.

Node 1:

OS: Ubuntu 20.04
Hostname: osradar1
IP address: 192.168.1.43
RAM Memory: 512 mb
Hard drive: 20gb

Node 2:

OS: Ubuntu 20.04
Hostname: osradar2
IP address: 192.168.1.44
RAM Memory: 512 mb
Hard drive: 20gb

In both cases, we are talking about a clean system with few applications installed. So this is the most reliable method for these cases.

Before starting…

Before we start, there are a few things to do in both nodes.

First, in both nodes, install MariaDB and rsync to perform the replication.

:~$ sudo apt install rsync mariadb-server mariadb-client

Also, it is necessary to secure the installation with the mysql_secure_installation script and define a root password.

Configuring MariaDB for the database replication cluster

Once we have the packages installed correctly it is necessary to make some configurations on the nodes. These configurations are related to the MariaDB cluster.

So, in the first node open the MariaDB configuration file.

:~$ sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

And add the following configuration at the end of the file:

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.43,192.168.1.44"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="galera_cluster"
wsrep_node_address="192.168.1.43"
wsrep_node_name="osradar1"

The most important lines that you have to modify are

wsrep_cluster_address="gcomm://" you have to specify separated by coma the IP addresses of the nodes that form the cluster. As you know, they are 192.168.1.43 and 192.168.1.44

wsrep_node_address="[IP_node1]" in this section you have to put the address of the Node where you are doing the configuration. In this case, is node 1 whose IP address is 192.168.1.43

wsrep_node_name="[hostname]" refers to the host hostname.

The rest of the configurations can be left as they are.

Save the changes to the file and close it.

Now in node 2 open the MariaDB configuration file and add the following configuration:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.43,192.168.1.44"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="galera_cluster"
wsrep_node_address="192.168.1.44"
wsrep_node_name="osradar2"

Again, save the changes and close the editor.

Creating a database replication cluster

The time has come to create the cluster. First, in both nodes stop the service of MariaDB:

:~$ sudo systemctl stop mariadb
1.- Stop the MariaDB service
1.- Stop the MariaDB service

And assign permissions to the temporary folder to avoid problems:

:~$ sudo chmod 777 -R /tmp/

After doing this, in Node 1 it initializes the cluster with the following command:

:~$ sudo galera_new_cluster

This command does not generate any screen output. But we can always check if it worked by running this one:

:~$ sudo mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
2.- Creating a database replication cluster with MariaDB
2.- Creating a database replication cluster with MariaDB

As you can see on the screen, the cluster is active.

You can run the same command on node 2 and you will get a similar result.

3.- configuring the cluster
3.- configuring the cluster

Now all that remains is to test the cluster.

For it, in the node1 I will show the databases that there are:

> SHOW DATABASES;

As you can see, I haven’t created any. Now I’ll create a database called osradar.

> CREATE DATABASE osradar;

And I’ll show the system databases again to check that the database has been created.

> SHOW DATABASES;

Now in node 2, show the system databases

> SHOW DATABASES;

Well, as you can see it’s automatically replicated, and in node 2 is available the database we’ve created in node 1.

4.- The database replication cluster is alive
4.- The database replication cluster is alive

The cluster is working!

Conclusion

This type of cluster is quite useful to help prevent errors or make backups. They are also useful for many situations where it is worth having the databases synchronized.

So, share this post and join our Telegram Channel

More articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here

-

Latest article

x