How to Configure MySQL Master-Slave Replication on CentOS 7

Introduction

This tutorial will be showing you how to set up MariaDB master-slave replication on CentOS 7. MariaDB is a mature, stable, open-source relational database forked from MySQL. Replication is a feature in MariaDB that allows data on one server to be mirrored on another server.

MariaDB replication is based on the binary log (binlog). The purpose of binary log is to allow replication, backup and restoration of databases.

Prerequisites

To use this tutorial, make sure you are logged into your server with a sudo user.

Our testing environment consists of the following machines (both are CentOS 7):

Master: 192.168.0.20
Slave: 192.168.0.30

Step 1 - Installing MariaDB

To install the latest version of MariaDB, we will need to add their repositories to our servers. If you’re using an older version of MariaDB, like say 5.5, consider upgrading to the latest 10.4.5 version using this tutorial.

Create a file named MariaDB.repo inside /etc/yum.repos.d with the following contents on both Master and Slave systems:

vi /etc/yum.repos.d/MariaDB.repo

# MariaDB 10.4.5 CentOS repository list - created 2019-May-21 11:42:15
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4.5/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Save the file and install MariaDB on both servers using yum:

yum install mariadb-server mariadb-client

Then, start and enable MariaDB service:

systemctl start mariadb
systemctl enable mariadb

Step 2 - Setting Up a Sample MySQL Database on Master

We will now set up the exampledb database on the master server:

mysql -u root -p

MariaDB [(none)]> CREATE DATABASE exampledb;
MariaDB [(none)]> USE exampledb;
MariaDB [(none)]> CREATE TABLE authors (id INT, name VARCHAR(20), email VARCHAR(20));
MariaDB [(none)]> exit;

Step 3 - Configuring MySQL Server on Master

To configure the master, edit the /etc/my.cnf file. Under the [mysqld] section, add the following four lines:

vi /etc/my.cnf

[mysqld]
log-bin
server_id=1
replicate-do-db=exampledb
bind-address=192.168.0.20

Then, restart MariaDB service:

systemctl restart mariadb

Log on to the MariaDB server as root, create the user slave and assign the necessary grants:

mysqld -u root -p

MariaDB [(none)]> CREATE USER 'slave'@'localhost' IDENTIFIED BY 'SlavePassword';
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO slave IDENTIFIED BY 'SlavePassword' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
MariaDB [(none)]> SHOW MASTER STATUS;
MariaDB [(none)]> exit;

Example Output:

MariaDB [(none)]> CREATE USER 'slave'@'localhost' IDENTIFIED BY 'SlavePassword';
Query OK, 0 rows affected (0.006 sec)

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO slave IDENTIFIED BY 'SlavePassword' WITH GRANT OPTION;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.006 sec)

MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 |      812 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> exit
Bye

Note down the file master-bin.000003 and position number 812, you need these values later.

Step 4 - Backup Master server database and transfer it to the Slave

Enter the following command to dump exampledb databases and save them. We will transfer these databases to Slave server later:

mysqldump exampledb -u root -p > exampledb.sql

This will create a file called exampledb.sql in your current working directory. This will take some time depending upon the databases size.

Again login to MySQL as root user:

mysql -u root -p

MariaDB [(none)]> UNLOCK TABLES;
MariaDB [(none)]> quit;

Copy the exampledb.sql file to your Slave server.

Here, I am going to copy this file to the /root/ folder of my slave server. So the command will be:

scp exampledb.sql root@192.168.0.30:/root/

Run the mysql_upgrade procedure to upgrade the system tables (you will be prompted to enter the MariaDB root password):

mysql_upgrade -u root -p

Remember, 192.168.0.30 is your MariaDB slave server IP address.

Step 5 - Configuring MySQL Server on Slave

To configure the slave, connect to the local MariaDB server with:

mysql -u root -p

Once connected to the database server, create the user and an empty database, and grant permissions:

MariaDB [(none)]> CREATE DATABASE exampledb;
MariaDB [(none)]> CREATE USER 'slave'@'localhost' IDENTIFIED BY 'SlavePassword';
MariaDB [(none)]> GRANT ALL PRIVILEGES ON exampledb.* TO 'slave'@'localhost' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;

Exit the MariaDB prompt with exit; and load the dump created in the master server:

mysql -u root -p exampledb < exampledb.sql

Edit the /etc/my.cnf file to assign a server ID to the slave under the [mysqld] section. Note that it needs to be a different integer than 1, as we used 1 in the master:

[mysqld]
server_id=2
replicate-do-db=exampledb

Restart the database server:

systemctl restart mariadb

Run the mysql_upgrade procedure to upgrade the system tables (you will be prompted to enter the MariaDB root password):

mysql_upgrade -u root -p

Once the dump has been imported to the slave, we need only a few steps to begin replicating. Log on to the database and run the following commands in the MariaDB prompt. Pay special attention to the MASTER_LOG_FILE and MASTER_LOG_POS variables, which should match the values returned by SHOW MASTER STATUS in STEP 3 of "Configuring MySQL Server on Master" above.

mysql -u root -p

MariaDB [(none)]> CHANGE MASTER TO
  MASTER_HOST='192.168.0.20',
  MASTER_USER='slave',
  MASTER_PASSWORD='SlavePassword',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master-bin.000003',
  MASTER_LOG_POS=812,
  MASTER_CONNECT_RETRY=10,
  MASTER_USE_GTID=current_pos;

Start the slave and check its status without exiting the MariaDB prompt:

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

Step 6 - Test MySQL/MariaDB Database Replication

Connect to the Master MariaDB server:

mysql -u root -p

Add a record to the exampledb table in the master server:

MariaDB [(none)]> use exampledb;
MariaDB [(none)]> INSERT INTO authors (id, name, email) VALUES (1, 'Your Name', 'email@example.com');
MariaDB [(none)]> exit;

Then verify that this change was replicated in the slave:

MariaDB [(none)]> USE exampledb;
MariaDB [(none)]> SELECT * FROM authors WHERE id=1;

Example Output:

MariaDB [exampledb]> SELECT * FROM authors WHERE id=1;
+------+-----------+-------------------+
| id   | name      | mail              |
+------+-----------+-------------------+
|    1 | Your Name | email@example.com |
+------+-----------+-------------------+
1 row in set (0.001 sec)

As you can see, replication is working correctly from master to slave.

Conclusion

In this tutorial we have explained how to install the latest version of MariaDB in CentOS 7, and discussed how to set up master-slave replication with GTIDs. For more information, you may want to refer to the MariaDB Replication Guide

How to Configure MySQL Master-Slave Replication on CentOS 7

Author

Published

Time to read

Table of Contents