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
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'"
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.
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.
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
