MariaDB is a community version of MySQL database server. The latest stable version of MariaDB is 10.5. In a database environment, there is always need for disaster recovery (DR). This is usually put in place because database systems require 99.99% uptime. In this guide we will perform the setup MariaDB 10.5 Master-Slave Replication on Rocky Linux 8.

This article will cover how to setup database replication between two or more MariaDB instances where one acts as the primary host and the other nodes secondary nodes.

In our guide, we shall deploy our databases and configure replication between two Rocky Linux 8 instances.

As you already know, MariaDB is a structured database server that uses standard SQL syntax to use and manage the environment.

Let’s quickly dive into the installation steps.

Install MariaDB 10.5 Master on Rocky Linux 8

In my setup, I have two nodes with the following details:

  • Primary server – 192.168.100.42
  • Secondary server – 192.168.100.44

To install MariaDB 10.5 on Rocky Linux, we need to configure MariaDB YUM repository.

Download and install MariaDB repo:

sudo yum install wget
wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
echo "32e01fbe65b4cecc074e19f04c719d1a600e314236c3bb40d91e555b7a2abbfc mariadb_repo_setup" | sha256sum -c -
chmod +x mariadb_repo_setup
sudo ./mariadb_repo_setup  --mariadb-server-version="mariadb-10.5"

The above command install the MariaDB repository and updates your system cache.

Install MariaDB server on Rocky Linux 8.

$ sudo yum install MariaDB-server
MariaDB                                                                   88 kB/s | 595 kB     00:06    
Rocky Linux 8 - AppStream                                                 62 kB/s | 7.8 MB     02:08    
Rocky Linux 8 - BaseOS                                                   391 kB/s | 3.5 MB     00:09    
Rocky Linux 8 - Extras                                                   1.0 kB/s | 3.8 kB     00:03    
Rocky Linux 8 - PowerTools                                               118 kB/s | 2.1 MB     00:18    
Extra Packages for Enterprise Linux Modular 8 - x86_64                   123 kB/s | 664 kB     00:05    
Extra Packages for Enterprise Linux 8 - x86_64                           455 kB/s |  10 MB     00:22    
MariaDB Server                                                           106 kB/s | 595 kB     00:05    
MariaDB MaxScale                                                         1.5 kB/s | 7.1 kB     00:04    
MariaDB Tools                                                            1.2 kB/s | 2.6 kB     00:02    
Remi's Modular repository for Enterprise Linux 8 - x86_64                203 kB/s | 765 kB     00:03    
Safe Remi's RPM repository for Enterprise Linux 8 - x86_64               175 kB/s | 1.9 MB     00:10    
WineHQ packages                                                          405 kB/s | 2.7 MB     00:06    
Dependencies resolved.
=========================================================================================================
 Package                    Architecture       Version                    Repository                Size
=========================================================================================================
Installing:
 MariaDB-backup             x86_64             10.5.11-1.el8              mariadb-main             7.0 M
 MariaDB-server             x86_64             10.5.11-1.el8              mariadb-main              27 M
Installing dependencies:
 MariaDB-client             x86_64             10.5.11-1.el8              mariadb-main              13 M
 MariaDB-common             x86_64             10.5.11-1.el8              mariadb-main              87 k
 MariaDB-shared             x86_64             10.5.11-1.el8              mariadb                  115 k
 galera-4                   x86_64             26.4.8-1.el8               mariadb                   13 M
 socat                      x86_64             1.7.3.3-2.el8              appstream                301 k
.......

When the installation is done, enable and start mariadb-service.

sudo systemctl enable --now mariadb.service

Confirm that the service is up and running

sudo systemctl status mariadb

Harden your database server before you can start using it.

 $ sudo mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP 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,
so you should just press enter here.

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 authorisation.

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 them 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 at 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!

You will need to configure the root password in the above step. This is important as you will need it to login to your database server using the command below.

$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.5.11-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

Configure MariaDB settings on Primary Node

Below are some of the MariaDB configuration files:

  • /etc/my.cnf
  • /etc/my.cnf.d/client.cnf
  • /etc/my.cnf.d/mysql-clients.cnf
  • /etc/my.cnf.d/server.cnf

Custom configuration files can be added in the /etc/my.cnf.d/ directory.

We need to configure the Primary node to make it aware of its role in the cluster. To do this, we shall edit the /etc/my.cnf.d/server.conf file and add the following lines under the [mysqld] section.

$ sudo vi /etc/my.cnf

Add the details below;

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

Save the changes, exit and restart mariadb service.

sudo systemctl restart mariadb

Create a database user that will be used to bind the servers for replication

MariaDB [(none)]> create user [email protected]'%' identified by '[email protected]';
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO [email protected]'%';
MariaDB [(none)]> flush privileges;

Allow MySQL port through the firewall.

$ sudo firewall-cmd --add-port=3306/tcp --zone=public --permanent
$ sudo firewall-cmd --reload

Install MariaDB 10.5 Slave on Rocky Linux 8

We now need to install MariaDB 10.5 on our secondary node. The installation process is similar the one above.

Confgure MariaDB YUM repo:

sudo yum install wget
wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
echo "32e01fbe65b4cecc074e19f04c719d1a600e314236c3bb40d91e555b7a2abbfc mariadb_repo_setup" | sha256sum -c -
chmod +x mariadb_repo_setup
sudo ./mariadb_repo_setup  --mariadb-server-version="mariadb-10.5"

Install MariaDB server:

$ sudo yum install MariaDB-server 
MariaDB                                                                   88 kB/s | 595 kB     00:06    
Rocky Linux 8 - AppStream                                                 62 kB/s | 7.8 MB     02:08    
Rocky Linux 8 - BaseOS                                                   391 kB/s | 3.5 MB     00:09    
Rocky Linux 8 - Extras                                                   1.0 kB/s | 3.8 kB     00:03    
Rocky Linux 8 - PowerTools                                               118 kB/s | 2.1 MB     00:18    
Extra Packages for Enterprise Linux Modular 8 - x86_64                   123 kB/s | 664 kB     00:05    
Extra Packages for Enterprise Linux 8 - x86_64                           455 kB/s |  10 MB     00:22    
MariaDB Server                                                           106 kB/s | 595 kB     00:05    
MariaDB MaxScale                                                         1.5 kB/s | 7.1 kB     00:04    
MariaDB Tools                                                            1.2 kB/s | 2.6 kB     00:02    
Remi's Modular repository for Enterprise Linux 8 - x86_64                203 kB/s | 765 kB     00:03    
Safe Remi's RPM repository for Enterprise Linux 8 - x86_64               175 kB/s | 1.9 MB     00:10    
WineHQ packages                                                          405 kB/s | 2.7 MB     00:06    
Dependencies resolved.
=========================================================================================================
 Package                    Architecture       Version                    Repository                Size
=========================================================================================================
Installing:
 MariaDB-backup             x86_64             10.5.11-1.el8              mariadb-main             7.0 M
 MariaDB-server             x86_64             10.5.11-1.el8              mariadb-main              27 M
Installing dependencies:
 MariaDB-client             x86_64             10.5.11-1.el8              mariadb-main              13 M
 MariaDB-common             x86_64             10.5.11-1.el8              mariadb-main              87 k
 MariaDB-shared             x86_64             10.5.11-1.el8              mariadb                  115 k
 galera-4                   x86_64             26.4.8-1.el8               mariadb                   13 M
 socat                      x86_64             1.7.3.3-2.el8              appstream                301 k
.......

Configure MariaDB 10.5 Slave settings on Rocky Linux 8

We need to configure MariaDB on the secondary node to act as the slave.

Enable and start MariaDB 10.5 on slave node

sudo systemctl enable --now mariadb

Harden MariaDB installation:

$ sudo mysql_secure_installation

Configure Slave settings for MariaDB. Edit the /etc/my.cnf/d/server.conf file and add the following.

[mysqld]
bind-address=0.0.0.0
server-id=2
binlog-format=ROW

Take note of the server-id above. It should always be a number bigger than the master node ID.

Save the config, then restart Mariadb service

sudo systemctl restart mariadb

Configure MariaDB 10.5 Replication on Rocky Linux 8

The steps involved in configuration of MariaDB 10.5 replication are:

  1. Get the current status of the primary server
  2. Configure database replication on the secondary server

Step 1. Get the current status of the Primary server

Stop the slave user on the Master server:

MariaDB [(NONE)]> STOP SLAVE;

Get the status of the master by running:

SHOW MASTER STATUS;

Obtain the position of the master database, e.g in my case the position is 1218 and the filename, e.g mysql-bin.000001

MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     1218 |              |                  |
+------------------+----------+--------------+------------------+

Unlock tables on the master database:

MariaDB [(NONE)]>  UNLOCK TABLES;

Note that if you have an existing database you will need to create a backup of the same, restore the database on the secondary server before you enable slave replication.

For instance, if I have a database called students:

mysqldump -u root -p student >> students.sql

Send a copy of the database to the secondary server

scp students.sql [email protected]:~/

Login to the secondary server, restore the database:

mysql -u root -p students<students.sql

Step 2. Configure database replication on the secondary server

Login to the secondary database

mysql -u root -p

Stop the slave threads for the secondary server

stop slave;

Configure the secondary node to replicate the master node with the command below:

CHANGE MASTER TO MASTER_HOST = 'your-master-host-ip', MASTER_USER = 'replication', MASTER_PASSWORD = 'your-password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = podition-id;

Example:

CHANGE MASTER TO MASTER_HOST='192.168.100.42', MASTER_USER='dbreplica',
MASTER_PASSWORD='[email protected]', MASTER_LOG_FILE='mysql-bin.000001' ,
MASTER_LOG_POS=1218;

Replace the variables in the above command with the correct details in your environment.

Next, start the slave threads on the secondary node.

start slave;

Verify that the slave replication is has started

MariaDB [(none)]> show slave status \G

You should see an output similar to this below:

Test Database Replication

The last step will be to test and see if the replication is really working.

On the Master node, create a sample database and insert some data into it.

MariaDB [(none)]> CREATE DATABASE students;

MariaDB [(none)]> create table students.student_details (std_no VARCHAR(20), birth_date VARCHAR(20), first_name VARCHAR(20), last_name VARCHAR(20), gender VARCHAR(20));

MariaDB [(none)]> INSERT INTO student.student_details (std_no, birth_date, first_name, last_name, gender, ) VALUES (500000, '1922-07-12', 'vic', 'eng', 'M');

Confirm that you can see the database from the secondary node:

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| students           |
+--------------------+
4 rows in set (0.002 sec)

Confirm if the table created on the master server exists on the secondary node:

MariaDB [(none)]> select * from students.student_details;
+--------+------------+------------+-----------+--------+
| std_no | birth_date | first_name | last_name | gender |
+--------+------------+------------+-----------+--------+
| 500000 | 1922-07-12 | vic        | Null      | M      |
+--------+------------+------------+-----------+--------+
1 row in set (0.001 sec)

This confirms that the replication is working as expected. You can always use the secondary database in case there is a problem with your primary server. This is definitely a life-saver when it comes to disaster recovery.

Please feel free to to reach out in case you had an issue in your deployment.

Check out some other articles on the site:

Install PostgreSQL on Rocky Linux 8 | AlmaLinux 8

Install Microsoft SQL Server 2019 on Rocky Linux 8

How To Install MongoDB 4.x on Rocky Linux 8

LEAVE A REPLY

Please enter your comment!
Please enter your name here