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:

$ sudo dnf update

After successfully updating your system, now proceed and install MariaDB

$ sudo dnf install mariadb-server

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

$ rpm -qa | grep mariadb

To get more detailed information, execute the command:

$ rpm -qi mariadb-server

Now start the MariaDB service

$ sudo systemctl start mariadb

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

$ sudo systemctl enable mariadb

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

$ sudo systemctl status mariadb

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:

$ sudo mysql_secure_installation

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:

$ sudo firewalld-cmd –add-port=3306/tcp –zone=public –permanent

For the rule to be applied, reload the firewall:

$ sudo firewalld-cmd –reload

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

$ sudo vim /etc/my.cnf

Append the configuration below

[mysqld]
bind–address=192.168.43.13
server–id=1
log_bin=mysql–bin
binlog–format=ROW

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

$ sudo systemctl restart mariadb-server

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:

$ sudo vim /etc/my.cnf

Append the configuration below

[mysqld]
bind–address=192.168.43.252
server–id=2
log_bin=mysql–bin
binlog–format=ROW

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.

$ mysql –u root –p

First, Stop the slave user.

MariaDB [(NONE)]> STOP SLAVE;

The create a replication user with the commands shown:

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO ‘replica_user’ @‘192.168.43.252’
IDENTIFIED BY ‘P@ssword123’;
Query OK, 0 rows affected (0.06 sec)
MariaDB [(none)]> FLUSH PRIVILEGES ;
Query OK, 0 rows affected (0.04 sec)
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> EXIT;
Query OK, 0 rows affected (0.02 sec)

Thereafter, check that status of the Master by running:

MariaDB [(NONE)]> SHOW MASTER STATUSG

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:

$ sudo mysqldump –all-databases -u root -p > masterdatabase.sql

MariaDB [(NONE)]> UNLOCK TABLES;
MariaDB [(NONE)]> EXIT;

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

$ scp masterdatabase.sql root@192.168.43.13:/root/

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.

$ mysql –u root –p < masterdatabase.sql

And then restart MariaDB service

$ systemctl restart mariadb

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

MariaDB [(NONE)]> STOP SLAVE;

MariaDB [(NONE)]> CHANGE MASTER TO MASTER_HOST=‘192.168.43.13’, MASTER_USER=‘replica_user’,
MASTER_PASSWORD=‘P@ssword123’, MASTER_LOG_FILE=‘mysql-bin.000001’ ,

MASTER_LOG_POS=1317;

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:

MariaDB [(NONE)]> START SLAVE;

Then check the status of the slave

MariaDB [(NONE)]> SHOW SLAVE STATUS;

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

MariaDB [(NONE)]> CREATE DATABASE replica_db;

Then confirm the creation of the database

MariaDB [(NONE)]> SHOW DATABASES;

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.