MariaDB Replication on RHEL 8/ CentOS 8

Disasters happen, and when they do, precious data can go with the wind, never again to be recovered, and when recovered, Companies usually spend up to millions of dollars to have it back and lose valuable time which could have been spent in other operations. And this is where the concept of replication comes in. Replication is simply having multiple copies of a database. Replication ensures that at any time, there are backup copies of the primary database so that in the event that the database goes down, data can still be retrieved from the backup databases ensuring redundancy and high availability. In this tutorial, you will learn how to configure MariaDB master-slave replication on CentOS 8.

Lab Setup

Master Node – 192.168.43.13
Slave Node  – 192.168.43.252

Step 1: Install MariaDB on both Master and Slave servers

To begin with, you need to install MariaDB on both the Master and the Slave. So follow the steps below:

First, log in to the Master server and update the system packages as shown:

After successfully updating your system, now proceed and install MariaDB

Once the installation is successful, you can verify that MariaDB is installed by running the command:

To get more detailed information, execute the command:

Now start the  MariaDB service

Additionally, you may also enable the service to start automatically upon any boot/reboot session.

To verify that the MariaDB database engine is up and running, issue the command:

Perfect! MariaDB is up and running as we expected.

As it is, MariaDB is not secured and any user can log in to the database engine and have access to all the databases and make changes. Of course,  we don’t want that to happen and securing the database should be a top priority. We, therefore, need to secure the database engine by setting a root password. So, run the command below:

What follows is an interactive prompt that will require to set the root password for the database and answer a few questions.

By default, MariaDB runs on port 3306. If you are running a firewall, you need to allow this port so that the database engine can be accessible to external users and services.

To open the port on the firewall, run the following firewall rule:

For the rule to be applied, reload the firewall:

With MariaDB successfully installed and secured on the Master server, repeat the same steps on the slave server.

Step 2: Configure MariaDB on the Master server

We need to configure MariaDB daemon so that our intended Master server acts as a server in the setup. So open the configuration file /etc/my.cnf

Append the configuration below

Save and exit the configuration file. To effect the changes, restart the MariaDB service.

Step 3: Configure the Slave server

Just like the Master server, the slave needs to be configured to act like one. So open the configuration file as before:

Append the configuration below

Be keen to provide a different ‘server_id’ from the Master server, which is 2 in this case. And just like the Master server, the ‘bind_address’ parameter should point to the slave’s IP address.

Save and exit the file.

Step 3: Create a replication user in the Master server

To configure the slave for replication, we need to head back to the Master node and create a replication user.  Log in to the MariaDB database engine.

First, Stop the slave user.

The create a replication user with the commands shown:

Thereafter, check that status of the Master by running:

Carefully note down the values of the filename and the position.  These will be later used to configure the slave for replication.

From the output above, this translates to:

File:         mysql-bin.000001
Position:     1317

Exit the MariaDB engine and create a backup copy of the master server as shown:

Log in back to MariaDB and unlock the tables:

Remember the backup copy we created for the Master database? We ready to copy it to the Slave server. So run the command below:

Step 4: Configure the Slave for replication

 Back on the slave node, import the backup file that we copied from the master to the MariaDB engine.

And then restart MariaDB service

Now log in to the MariaDB database engine and configure the slave as follows:

Remember the values that we said that you should remember and possibly jot them down when displaying the status of the  Master server? These have finally been defined in the MASTER_LOG_FILE and MASTER_LOG_POS  attributes as seen.

Finally, start the slave to initialize the Slave to begin replicating from the Master:

Then check the status of the slave

If the configuration was done perfectly, you should not get the output below free from any errors.

The slave is now ready for replication.

Step 5: Testing database replication

Ultimately, we need to confirm if our setup is working. So log into MariaDB instance in the Master and create a test database as shown

Then confirm the creation of the database

Head back to the Slave node and check if the database exists.

Perfect! Our setup is working! All the subsequent databases created in the Master will be automatically replicated and changes synched on the Slave node. And this brings us to the end of this guide.