How to Configure MariaDB Master-Slave Replication on AlmaLinux

Introduction

MariaDB replication is a process that allows you to automatically copy data from one database server to another server. In this article, we are going to discuss how to configure MariaDB Master-Slave replication on an AlmaLinux platform through a comprehensive example.

Let us get started.

Prerequisites

In this example, we are assuming that you have two servers running CentOS 8, which can communicate with each other over a private network. If your hosting provider doesn’t provide private IP addresses, you can use the public IP addresses and configure the firewall to allow traffic on port 3306 only from trusted sources.

We can also create a private network between these servers.

The servers have the following IPs:

• Master Server IP: 192.168.0.101
• Slave Server IP: 192.168.0.102

Installation of MariaDB

Step 1:

Create a file in the /etc/yum.repos.d/ folder and add these lines in the mariadb.repo file:

cd /etc/yum.repos.d/
vim mariadb.repo


[mariadb]
name = MariaDB
baseurl = [http://yum.mariadb.org/10.5/centos8-amd64](http://yum.mariadb.org/10.5/centos8-amd64)
module_hotfixes=1
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Then, save and exit.

Step 2:

Next, we will install dnf install MariaDB-server -y. Once the installation is complete, start the MariaDB service and enable it to automatically start on boot with the following lines of code:

systemctl start mariadb
systemctl enable mariadb

Step 3:

Once the boot code is in place, we will secure the MariaDB Database Server by using the mariadb-secure-installation command as shown:

$ mariadb-secure-installation

Note: Running all parts of this script is recommended for all MariaDB servers in production use. Go through them all carefully.

In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank.

Enter current password for root (enter for none): OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorization.

• Set root password? [Y/n]y New password: Re-enter new password: Password updated successfully! Reloading privilege tables... ... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove this before moving into a production environment.

• Remove anonymous users? [Y/n]y ... Success!

Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess the root password from the network.

• Disallow root login remotely? [Y/n]y ... Success!

By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing and should be removed before moving into a production environment.

• Remove test database and access to it? [Y/n]y
  • Dropping test database... ... Success!
  • Removing privileges on test database... ... Success!

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.

• Reload privilege tables now? [Y/n] y ... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MariaDB installation should now be secure.

Thanks for using MariaDB!

Step 4

Configure the Master Server. You need to edit /etc/my.cnf file. Add following lines in this file

[mysqld]
bind-address=192.168.0.101
server-id=1
log_bin=mysql-bin
binlog-format=ROW

Important Note: - server-id is important

Once done, restart the MariaDB service for changes to take effect

systemctl restart mariadb

Step 5

The next step is to create a new replication user. Log in to the MariaDB server as the root user.

From inside the MariaDB prompt, run the following SQL queries that will create the khalid user and grant the REPLICATION SLAVE privilege to the user.

CREATE USER 'khalid'@'192.168.0.102' IDENTIFIED BY 'strong_password'; GRANT REPLICATION SLAVE ON . TO 'khalid'@'192.168.0.102';

Make sure you change the IP with your slave IP address.

While still inside the MariaDB prompt, execute the following command that will print the binary filename and position.

`SHOW MASTER STATUS\G`


Connection id:    9000633
Current database: *** NONE ***

*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 1021
    Binlog_Do_DB:
Binlog_Ignore_DB: test,information_schema,mysql
1 row in set (0.001 sec)

Take note of file name, mysql-bin.000001 and Position 1021. You’ll need these values when configuring the slave server. These values will probably be different on your server.

Step 6

Now we need to configure the Slave Server

Open the MariaDB configuration file and edit the following lines.

sudo vim  /etc/my.cnf


[mysqld]
bind-address=192.168.0.102
server-id=2
log_bin=mysql-bin
binlog-format=ROW

Restart the MariaDB service.

systemctl restart mariadb

The next step is to configure the parameters that the slave server will use to connect to the master server. Login to the MariaDB shell.

$ mysql -u root -p

First, stop the slave threads:

MariaDB [(none)]> STOP SLAVE;

Run the following query that will set up the slave to replicate the master:

MariaDB [(none)]> CHANGE MASTER TO
MariaDB [(none)]> MASTER_HOST='192.168.0.101',
MariaDB [(none)]> MASTER_USER='khalid',
MariaDB [(none)]> MASTER_PASSWORD='strong_password',
MariaDB [(none)]> MASTER_LOG_FILE='mysql-bin.000001',
MariaDB [(none)]> MASTER_LOG_POS=1427;

Make sure you are using the correct IP address, user name, and password. The log file name and position must be the same as the values you obtained from the master server.

Once done, start the slave threads.

MariaDB [(none)]> START SLAVE;

Step 7

Now, We have a working Master/Slave Replication Setup.

To verify that everything works as expected, we will create a new database on the master server.

Login into Master Server (192.168.0.101)

$ mysql -u root -p

MariaDB [(none)]> CREATE DATABASE geekyants;

Now to verify it, Login into Slave Server (192.168.0.102)

$ mysql -u root -p

MariaDB [(none)]> SHOW DATABASES;

You will notice that the database you created on the master server is replicated on the slave:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| geekyants        |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Conclusion

In this tutorial, we have shown you how to create a MariaDB Master/Slave replication on CentOS 8.

Some Important Notes on Binary Log file

The binary log file stored the data in binary format. That makes it quicker for writing log information. The binary log file only capture Data Changing information, it is also used for replication.

How to Enable Binary log:

In the case of MariaDB server, Please make changes to /etc/my.cnf file

[mysqld]
log-bin=bin.log
log-bin-index=bin-log.index
max_binlog_size=100M
binlog_format=row

How to Read binary log files:

sudo mysqlbinlog binary-log-file-name