OSETC TECH

How to Setup MySQL Master-Slave Replication on CentOS or RHEL Linux

osetc

This post will guide you how to setup MySQL Master-Slave replication on both CentOS or RHEL servers. How do I Configure MySQL Master-Slave replication with one master node and one slave node on CentOS 7 Linux system. If you want to setup Master-Slave replication for Mariadb Database on CentOS 7 Linux, just refer to the same steps with MySQL DB.

What is MySQL Replication?

You should know that MySQL Database is one of the most popular and open source relational database management. And the MySQL or MariaDB Replication is a process that allows your data from one Master server to be copied to another Slave server, so it can make your data safe. It will maintain two or more copies of your MySQL Data.

The Master-Slave replication will have only one Master server which will take all write requests, and other salve servers will be used for read request only.
Of course, there are other replication mode to replicate your data in MySQL database, such as: Master-Master.

This post will explain that how to configure a basic Master-Slave mode of replicating data with one Master node and one Slave node running on CentOS 7 Linux.

Prerequisites

Step1: Install MySQL

The default repository of CentOS 7 or RHEL 7 do not contain MySQL packages, so you need to download and install the repo file of MySQL from its official Yum Repository. Running the following command on both CentOS 7 servers:

$ wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
$ sudo yum localinstall mysql57-community-release-el7-11.noarch.rpm
$ sudo yum install mysql-community-server

Outputs:

[devops@localhost ~]$ wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
--2019-06-17 18:30:52-- https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Resolving dev.mysql.com (dev.mysql.com)... 137.254.60.11
Connecting to dev.mysql.com (dev.mysql.com)|137.254.60.11|:443...
connected.
HTTP request sent, awaiting response... 302 Found
Location: https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm [following]
--2019-06-17 18:30:55-- https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 104.118.77.179
Connecting to repo.mysql.com (repo.mysql.com)|104.118.77.179|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 25680 (25K) [application/x-redhat-package-manager]
Saving to: ‘mysql57-community-release-el7-11.noarch.rpm’

100%[=============================================================================================================>] 25,680 79.4KB/s in 0.3s

2019-06-17 18:30:59 (79.4 KB/s) - ‘mysql57-community-release-el7-11.noarch.rpm’ saved [25680/25680]

[devops@localhost ~]$ sudo yum localinstall mysql57-community-release-el7-11.noarch.rpm
Loaded plugins: fastestmirror, langpacks
Examining mysql57-community-release-el7-11.noarch.rpm: mysql57-community-release-el7-11.noarch
Marking mysql57-community-release-el7-11.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql57-community-release.noarch 0:el7-11 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================================================================
Package Arch Version Repository Size
=======================================================================================================================================================
Installing:
mysql57-community-release noarch el7-11 /mysql57-community-release-el7-11.noarch 31 k

Transaction Summary
=======================================================================================================================================================
Install 1 Package

Total size: 31 k
Installed size: 31 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : mysql57-community-release-el7-11.noarch 1/1
Verifying : mysql57-community-release-el7-11.noarch 1/1

Installed:
mysql57-community-release.noarch 0:el7-11
Complete!

[devops@localhost yum.repos.d]$ sudo yum install mysql-community-server
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-server.x86_64 0:5.7.26-1.el7 will be installed
--> Processing Dependency: mysql-community-common(x86-64) = 5.7.26-1.el7 for package: mysql-community-server-5.7.26-1.el7.x86_64
--> Processing Dependency: mysql-community-client(x86-64) >= 5.7.9 for package: mysql-community-server-5.7.26-1.el7.x86_64
--> Running transaction check
Installed:
mysql-community-libs.x86_64 0:5.7.26-1.el7 mysql-community-libs-compat.x86_64 0:5.7.26-1.el7 mysql-community-server.x86_64 0:5.7.26-1.el7
Dependency Installed:
mysql-community-client.x86_64 0:5.7.26-1.el7 mysql-community-common.x86_64 0:5.7.26-1.el7
Replaced:
mariadb-libs.x86_64 1:5.5.60-1.el7_5
Complete!

Note: you need to press Y key and hit enter key to continue the installation. And you should be better to install the same version of MySQL package on both Master and Slave servers.
Once the installation of MySQL is complete, you need to start the MySQL service and also need to enable MySQL service to start it when system boot up. Type:

$ sudo systemctl start mysqld
$ sudo systemctl enable mysqld

Step2: Configure Master Server

On Master Server, you need to modify the configuration file (/etc/my.cnf )of MySQL server with your text editor:

$ sudo vim /etc/my.cnf

Then you need to find the line bind-address, and replace the ip address with the IP address of Master server(192.168.3.43).

bind-address=192.168.3.43

Then you need to set a unique server ID in the [mysqld] section, for example, you can set the server ID as number 1.
server-id = 1
then add the following lines:

log-bin=mysql-bin

Save and close the configuration file and restart the MySQL service for the changes to take effect with the following command:

$ sudo systemctl restart mysqld

Step3: Create MySQL User for the Slave Server

When you installed MySQL package on your server, it will generate a temporary password for the MySQL root user, you can use the following command to get the password:

$ grep 'temporary password' /var/log/mysqld.log

Outputs:

[devops@localhost ~]$ grep 'temporary password' /var/log/mysqld.log
2019-06-17T11:15:54.698577Z 1
 [Note] A temporary password is generated for root@localhost: (1<CBm*hrCgz

Then you can set your new root password by running mysql_secure_installation command, it also can be used to improve the security of the MySQL server:

 $ mysql_secure_installation

You need to login to the MySQL server as user root and create a user that will be used by the salve server for Replication. Type the following MySQL command to login the terminal of MySQL server:

$ mysql -u root -p

And then running the following SQL statement to create one user called “replica”:

create user 'replica'@'%' identified by 'password#'; 
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
 flush privileges;

Note: you need to replace “password#” with your own password.

Outputs:

mysql> create user 'replica'@'%' identified by 'password#';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;

Then you need to execute the following command to print the binary filename and position that are going to be used for the slave server. Type:

Show master status;

Outputs:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 1241 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

So the filename is mysql-bin.000002, and the position is 1241.

Step4: Configure Slave Server

You need to modify the configuration file of MySQL server on the Slave server. Like for the Master server above. Just add bind-address variable, and set the unique server ID for slave server. Adding the following lines in the [mysqld] section.

bind-address=192.168.3.54
server-id=2
relay-log = mysql-relay-bin
log-bin = mysql-bin

Save and close the configuration file, and restarting the MySQL server on Slave server for the changes to take effect with the following command:

$ sudo systemctl restart mysqld

Then you can configure the parameters that the slave server will use to connect to the Master server. Just login to MySQL server as user root to run the following sql statements:

$ mysql -u root -p
Mysql>stop slave;
Mysql> CHANGE MASTER TO MASTER_HOST = '192.168.3.43', MASTER_USER = 'replica', MASTER_PASSWORD = 'PASSWORD', MASTER_LOG_FILE = 'mysql-bin.000002', MASTER_LOG_POS = 1241;
Mysql>start slave;
Mysql> quit;

Note: you need to replace MASTER_LOG_FILE and MASTER_LOG_POS with the information that you get it in the Step 3. You also need to make sure that you are using the correct IP address of Master server, Master user and Master password.

Step5: Test the Configuration

If you want to check if the replication works well, you can create a new database from the Master server, and then check if the database you created can be listed on the Slave server.
On Master server:

Mysql>create database replicaTest;

On Slave server:

Mysql>show databases;

Outputs:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| replicaTest |
| sys |
+--------------------+
5 rows in set (0.01 sec)

Conclusion

You should know that how to configure the MySQL Master-Slave replication on CentOS or RHEL Linux. If you want to get more information on how to replication Master configuration you can get if on MySQL’s official website.