Master-Slave Replication

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.