MariaDB Master-Slave Replication in CentOS/RHEL 8 with Multiple Databases

Database replication is an inevitable feature for those who want to prevent data loss. The term 'Replication' refers to making copy on precious data. Many companies spend up to millions of dollars to have it back and lose valuable time which could have been spent in other operations and where is the importance of replication comes. 
Master-slave replication for a MySQL database refers to having a secondary MySQL server where any changes made to the main database are replicated (copied) to the secondary MySQL database. It becomes a copy of the main database.

In this blog we can see the various steps for configuring Master Slave Replication for all databases.

Here we are creating testing environments to setup MariaDB Master Slave Replication on CentOS/RHEL 8 server. Both the Master and Slave servers required same version of MariaDB. We are using latest MariaDB version 10.5 here. Let's see the setup
Test environment servers:
Master server: 13.232.198.104
Slave server: 13.235.74.116
MariaDB installation
Need to install latest MariaDB version on both the Master and Slave servers using below steps:  Create a yum repository file as follows.

# vi /etc/yum.repos.d/MariaDB.repo
Add following directives in this file. 

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

Build cache for MariaDB yum repository. 
# dnf makecache 
Install MariaDB 
# dnf install -y MariaDB-server 
# systemctl enable --now mariadb.service 
# systemctl status mariadb.service 
Once you have installed MariaDB, run the mariadb-secure-installation procedure on both master and slave, let’s set up a sample test database in the master machine. 
# mariadb-secure-installation
Configuring Master server
Add these lines to my.cnf under [mysqld] section

[mysqld] 
server_id=1 
binlog-do-db=database_name_1 
binlog-do-db=database_name_2 
binlog-do-db=database_name_3 
Save and exit my.cnf file 
Restart MariaDB server 
# systemctl restart mariadb 
Go to SQL prompt and run below listed queries: 
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%' IDENTIFIED BY 'NewP@ssword'; 
MariaDB [(none)]> FLUSH PRIVILEGES; 
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; 
MariaDB [(none)]> UNLOCK TABLES; 
MariaDB [(none)]> SHOW MASTER STATUS;
> output like:

 Save this info for use in Slave configuration
Configuring Slave server
Add these lines to my.cnf under [mysqld] section

[mysqld]
server_id=2
replicate-do-db=database_name_1
replicate-do-db=database_name_2
replicate-do-db=database_name_3

Save and exit my.cnf file
Restart MariaDB server
# systemctl restart mariadb
Go to SQL prompt and run below listed queries:

 MariaDB [(none)]> SLAVE STOP;

 MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='13.232.198.104',   MASTER_USER='replica_user', MASTER_PASSWORD='NewP@ssword',   MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=342, MASTER_PORT=3306;

 MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS;

Please note to use the MASTER_LOG_FILE and MASTER_LOG_POS we have taken from 'SHOW MASTER STATUS' output on Master server
We will get a output on 'SHOW SLAVE STATUS' query, please make sure below listed ones are showing like same on that output. If both of them showing same and there is no further error then the replication setup is successfull.

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Testing Master-Slave Replication
Created databases on Master server (with same name mentioned while setup)

MariaDB [(none)]> CREATE DATABASE database_name_1;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> CREATE DATABASE database_name_2;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> CREATE DATABASE database_name_3;
Query OK, 1 row affected (0.000 sec)

Same databases replicated fine on Slave server

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| database_name_1 |
| database_name_2 |
| database_name_3 |
| information_schema |
| mysql |
| performance_schema |
+——————–+

All the subsequent databases created and further updates on database tables in the Master will be automatically replicated and changes synched on the Slave node.
Facebook Comments