How to Setup MariaDB Master-Slave Replication on CentOS/RHEL 7

Replication is a feature allowing the contents of one or more servers (called masters) to be mirrored on one or more servers (called slaves).

The main mechanism used in replication is the binary log. Slaves read the binary log from each master in order to access the data to replicate. A relay log is created on the slave server, using the same format as the binary log, and this is used to perform the replication.

Purpose of using Replication

• High Availability
• Scale-out
• Backup servers
• Disaster Recovery
• Reporting servers

In this article we will cover how to set up master-slave replication in MariaDB on CentOS 7 / RHEL 7.
For this tutorials we are going to use 2 nodes of CentOS 7 Servers where we will install MariaDB.

Master Configuration

– Installation of MariaDB

[root@sqlmaster ~]# yum install mariadb-server mariadb -y
[root@sqlmaster ~]# systemctl enable mariadb
[root@sqlmaster ~]# systemctl start mariadb

– Allowing the mysql ports on the firewall

[root@sqlmaster ~]# firewall-cmd --permanent --add-service=mysql
[root@sqlmaster ~]# firewall-cmd --reload

– Once you finish the installation, execute the mysql_secure_installation to setup the root password

[root@sqlmaster ~]# mysql_secure_installation

– For the purpose of testing the replication, we are going to setup a Sample MySQL Database from github

[root@sqlmaster ~]# git clone https://github.com/datacharmer/test_db
[root@sqlmaster ~]# cd test_db/
[root@sqlmaster ~]# mysql -u root -p < employees.sql

- Edit the /etc/my.cnf file and under the [mysqld] section, add the following four lines:

[root@sqlmaster ~]# vi /etc/my.cnf
[mysqld]
server_id=1
log-basename=master
log-bin
binlog-format=row
binlog-do-db= employees #databasename

- Restarting the MariaDB Service after editing /etc/my.cnf file.

 
[root@sqlmaster ~]# systemctl restart mariadb

- Creating A replication user:

[root@sqlmaster ~]# mysql -u root -p
MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'Password';
MariaDB [(none)]> FLUSH PRIVILEGES;

- Now we need to get the current position of binary log where the slave will use it to start the replication, for that we need to prevent any changes to the data while we are getting the binary log position using this following commands:

MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
MariaDB [(none)]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 |      245 | employees    |                  |
+--------------------+----------+--------------+------------------+
MariaDB [(none)]> EXIT;

- Before start configuring the slave, let's backup and copy the data from the master to the slave using mysqldump tool.

[root@sqlmaster ~]# mysqldump -u root -p employees > employees-backup.sql
[root@sqlmaster ~]# scp employees-backup.sql [email protected]:/root/

Slave Configuration

- Installation of MariaDB

[root@sqlslave ~]# yum install mariadb-server mariadb -y
[root@sqlslave ~]# systemctl enable mariadb
[root@sqlslave ~]# systemctl start mariadb

- Once you finish the installation execute the mysql_secure_installation to setup the root password

[root@sqlmaster ~]# mysql_secure_installation

- Connect to the Slave database server, create the user and an empty database, and grant permissions to the 'repl' user

MariaDB [(none)]> CREATE DATABASE employees;
MariaDB [(none)]> GRANT ALL PRIVILEGES ON employees.* TO 'repl'@'localhost' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;

- Before starting the slave configuration, let's import the dump created earlier in the master server:

[root@sqlslave ~]# mysql -u root -p employees < /root/employees-backup.sql

- Edit now the /etc/my.cnf file and under the [mysqld] section, add the following lines:

[root@sqlslave ~]# vi /etc/my.cnf
[mysqld]
server-id = 2
replicate-do-db=employees # name_databe_to_replicate

- Restart the MariaDB Service using the following command:

[root@sqlslave ~]# systemctl restart mariadb

- Once the data has been imported, now we need to run the command CHANGE MASTER TO where we going to add some settings: MASTER_HOST, MASTER_USER, ..., MASTER_LOG_FILE and MASTER_LOG_POS returned by the SHOW MASTER STATUS command in master .

 
[root@sqlslave ~]# mysql -u root -p
MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.1.13',
MASTER_USER='repl',
MASTER_PASSWORD='Password',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245,
MASTER_CONNECT_RETRY=10;

- Now start the slave with the START SLAVE command and if you want want to check the status of slave use SHOW SLAVE STATUS command:

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

Test MariaDB Database Replication

- In the master server, add a record to the employees table using the following command:

MariaDB [(none)]> INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (500000, '1986-07-14', 'Lotfi', 'Waderni', 'M', '2017-01-01');

MariaDB [(none)]> INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (500001, '1986-08-04', 'Mario', 'rossi', 'M', '2017-01-01');

- After adding some records in master server, go to slave server and verify if the data is replicated or not :

MariaDB [(none)]> SELECT * FROM employees.employees WHERE emp_no=500000;

To check the status of slave server replication use SHOW SLAVE STATUS command:

MariaDB [(none)]> SHOW SLAVE STATUS\G;

 
We hope this tutorial was enough Helpful. If you need more information, or have any questions, just comment below and we will be glad to assist you!