How to create a cluster with MariaDB Galera on Ubuntu 18.04?

The data that makes sense of the information must be safeguarded by system administrators around the world. Information management cannot be conceived without high technology that can lead to effective plans to achieve this goal.

One of the technologies available to perform this task is clustering. In short, cluster is a group of multiple computers linked by a high-speed network, so that the whole is seen as a single computer, more powerful than common desktop computers.

The advantages of having servers in clusters are many and range from processing speed to database scalability, allowing database replication.

MariaDB has within its products a software that allows us to do this in an efficient way on Ubuntu 18.04 and that utility is called MariaDB Galera Cluster. In this tutorial we will learn how to make one with two nodes.

Information about the nodes

For this article we will make a cluster with two nodes with the following data:

• OS: Ubuntu Server 18.04
• Hostnames: osradar1, osradar2
• IP address: 192.168.250.6, 192.168.250.7
• RAM Memory: 512mb both
• Hard Drive: 20gb

It is vital to handle this information before we start our work because we will have to handle it well later on.

Installing MariaDB Galera Cluster

As the official documentation tells us, from version 10.1 the utility is self-contained, inside the mariadb-server package. Therefore we proceed to install it:

              sudo -i

When you enter the password, you will be a root user.

Now, we proceed to install the mariadb server and mariadb galera cluster.

             apt install mariadb-server mariadb-client

Then we proceed to install rsync which is a necessary utility for the cluster to work.

             apt install rsync

Note: these commands must be executed on each of the nodes

Configuring MariaDB cluster

In order for the cluster to achieve communication between the nodes, a common configuration file must be created in each of them. We create it with the following command:

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

And we put the following information in:

[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
bind-address=0.0.0.0

#Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
#wsrep_provider_options=”gcache.size=32G”

#Galera Cluster Configuration
wsrep_cluster_name=”test_cluster”
wsrep_cluster_address=”gcomm://first_ip,second_ip,third_ip”

#Galera Synchronization Congifuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass

#Galera Node Configuration
wsrep_node_address=”this_node_ip”
wsrep_node_name=”this_node_name”

Now we have to modify some parameters to adapt them to our tutorial. First the line:

           wsrep_cluster_address=”gcomm://first_ip,second_ip,third_ip”

We must add the ip addresses of our nodes, so that’s how it looks:

           wsrep_cluster_address=”gcomm://192.168.250.6,192.168.250.7″

And in the segment: #Galera Node Configuration, we add our parameters

          wsrep_node_address=”192.168.250.6″
           wsrep_node_name=”osradar1″

I remind you that this same file must be created in each of the nodes, modifying the ip address and computer name parameters.

Once this process is done, we must run the mysql_secure_installation script to define root password and other things:

               mysql_secure_installation

We define the root password and answer the questions: Y,N,Y,Y.

For the cluster to work we must add rules in the firewall allowing access through certain ports.

            ufw enable
            ufw allow 3306/tcp
            ufw allow 4444/tcp
            ufw allow 4567/tcp
            ufw allow 4568/tcp
            ufw allow 4567/udp

Configuring Node 2

Previously we must run the mysql_secure_installation script to configure mariadb on this node:

               mysql_secure_installation

We respond in the same way as in the first node.

As mentioned above, we must create the same configuration file but modifying the IP and hostname criteria.

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

And we also opened the firewall ports:

            ufw enable
            ufw allow 3306/tcp
            ufw allow 4444/tcp
            ufw allow 4567/tcp
            ufw allow 4568/tcp
            ufw allow 4567/udp

As ubuntu is a Debian derivative, we have to copy a configuration file to all nodes. It is important to note that only the file of the first node is useful and should not be modified in the rest of the nodes. That’s why the other nodes must have their own file but must be changed so that it is the same as the first one.

The file is located in /etc/mysql/debian.cfg

Initializing the cluster

Before initializing the cluster we must stop the Mariadb service on all nodes:

             sudo systemctl stop mysql

Now we must initialize the cluster in the first node, to do this:

                galera_new_cluster

With this, you should now start the cluster and the added node 1. To check, we proceed to write:

               mysql -u root -p -e “SHOW STATUS LIKE ‘wsrep_cluster_size'”

When entering the password, we should see something like this:

Now we must go to the second node, and initialize the mariadb service.

              systemctl start mysql

And we return to the first node to verify that the second node has joined the cluster:

mysql -u root -p -e “SHOW STATUS LIKE ‘wsrep_cluster_size'”

Testing the efficacy of the cluster

To prove that the cluster is working in practice is by demonstrating database replication. In the first node, we access mariadb console and create a database:

                mysql -u root -p

next:

               CREATE DATABASE cluster;

and

              SHOW DATABASES;

And in the second node, we show the databases with Show Databases:

This is a tool with great potential, it allows us many possibilities in the segment of database management.

Please share this article through your social networks.