How To Configure MySQL Master Slave Replication On Centos 7

This comprehensive Linux guide expects that you run the following commands as root user but if you decide to run the commands as a different user then ensure that the user has sudo access and that you precede each of the privileged commands with sudo

MySQL is an open-source relational database management system.It is based on structured language theory. MySQL is inculcated with an ability that it can run on all platforms including Linux,Unix and Windows.It can be used for wide variety of applications but mostly it is associated with online publishing and web applications.
MySQL replication allows you to copy data from one server to one or more servers.

There are many techniques to accomplish this task. The most widely is master slave replication in which the server acts as a master and others act as slaves .

In this tutorial, we will explain how to set up a MySQL Master/Slave replication with one master and one slave server on CentOS 7. The same steps apply for MariaDB.

Table of Contents

Installing MySQL

We will install MySQL from official yum repository.
Try to install same versions on both the master as well as slave machine to avoid any problems.

yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

yum install mysql-community-server

Once the installation is complete run the following commands to add it to startup

systemctl enable mysqld
systemctl start mysqld

At new installation, a temporary password is created. Use the following command to find the password

grep 'temporary password' /var/log/mysqld.log
2020-04-10T22:36:55.405052Z 1 [Note] A temporary password is generated for root@localhost: roftfEis:2*b

To set a new password use the following command

mysql_secure_installation

Enter the temporary root password and say y to all the questions.
The new password needs to be at least 8-characters long and to contain at least one uppercase letter, one lowercase letter, one number, and one special character.

Configuration Of Master Server

To open MySQL configuration file type the following lines in [mysqld] section to make MySQL listen to specific IP, set a unique server ID and enable binanry logging. Set the MySQL server to listen on the private IP.

You can choose your own static IP Addresses, in this tutorial we are considering 172.168.1.100 as the IP address of Master.

nano /etc/my.cnf

/etc/my.cnf

bind-address           = 172.168.1.100 
server-id              = 1
log_bin                = mysql-bin
exit 0

Restart the server to make the changes take effect.

systemctl restart mysqld

Now you have to create a new replication user. Log in to the MySQL server as the root user using the following command to do so.

mysql -uroot -p

Use the following SQL queries that will create replica user and grant the appropriate replication permissions to the user so that it can replicate data.

Also make sure you enter the correct IP Address of the Slave, which is 172.168.1.101 in my case.

CREATE USER 'replica'@'172.168.1.101' IDENTIFIED BY 'your_password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'172.168.1.101'

Now execute the following commands to show current status

SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 1427
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

Note down the values of Position and file name which are “1427” and “mysql-bin.000001” in my case. You would require these values in slave configuration.

Configuration Of Slave Server

After the installation of MySQL on Slave server as we have done for the master server, we will open the configuration file for slave and set the following parameters.

Make sure you enter the correct IP Address of the Slave which was previously defined during the Master configuration which is 172.168.1.101 in my case.

nano /etc/my.cnf

/etc/my.cnf

bind-address           = 172.168.1.101 
server-id              = 2
log_bin                = mysql-bin

Restart the MySQL service:

systemctl restart mysqld

Open up MySQL Shell and configure the following parameters that the slave server will use to connect to the master server

mysql -uroot -p

Stop the slave threads using following command:

STOP SLAVE;

Run the following command to set up replication. Use the correct IP address, username and password, position and the name of the file from Master server’s configuration.

CHANGE MASTER TO
MASTER_HOST='172.168.1.100',
MASTER_USER='replica',
MASTER_PASSWORD='your_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1427;

After completion, start the slave threads using following command

START SLAVE;

Testing The Configuration

To test the configuration use the following commands to create a new database name “replicatestdatabase” on the Master server.

mysql -uroot -p

CREATE DATABASE replicatestdatabase;

Now on the Slave server, login to the MySQL shell and run the following commands to initialize and test replication

mysql -uroot -p
SHOW DATABASES;

The output on the Slave server would be similar to the output shown below.

mysql -uroot -p
SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| replicatest        |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

You have successfully set up Master Slave replication and the database you created on the Master server is replicated on the Slave