How to Setup MySQL Master-Slave Replication on RHEL 8

MySQL Replication is a process where data from one server is automatically copied or replicated onto another backup server in real time. Replication provides redundancy and fault tolerance and gives the user peace of mind that even after a failure in the master server, data can still be recovered.

In this tutorial, you are going to learn how to configure and setup a MySQL master-slave replication on a RHEL 8 Linux.

Prerequisites

In the setup, we are going to have two servers running RHEL 8 with the following IP addresses.

Master = 173.82.120.14
Slave  = 173.82.115.165

Let’s now proceed and see how we can configure the MySQL Master-slave replication setup on RHEL 8 Linux.

Step 1: Install MySQL on Master and Slave Server

RHEL repositories do not include MySQL packages. Thus, we are going to install MySQL from their official Yum Repository. Therefore, we are going to install MySQL on both servers using the following yum command.

$ sudo yum localinstall https://dev.mysql.com/get/mysql57-community-rlease-el7-11.noarch.rpm
Install MySQL Yum Repository

Install MySQL Yum Repository

Next run the command below to install MySQL server on RHEL 8 Linux.

$ sudo yum install mysql-community-server

Step 2: Secure MySQL on Master and Slave Server

After the installation, you should now start the MySQL service you just installed and make it start automatically each time you start the server. Therefore, use the following command.

$ sudo systemctl enable mysqld
$ sudo systemctl start mysqld

You should note that each time a MySQL server is started for the first time on any machine, it provides the root user with a temporary password. Thus to find the initial password, type the following.

$ sudo grep 'temporary password' /var/log/mysqld.log
Find MySQL Password

Find MySQL Password

Next up, you need to harden or secure the MySQL database server by running the following script.

$ sudo mysql_secure_installation

Enter the MySQL root password that you just unearthed in the previous step and hit ENTER. You will then be prompted if you want to change the default password. For convenience, it’s encouraged that you set a strong password that you can recall in the future.

Change MySQL Default Password

Change MySQL Default Password

Proceed with the rest of the prompt and answer Yes to all the questions so set up the server to best security practices.

Secure MySQL Server Installation

Secure MySQL Server Installation

Step 3: Configuring the MySQL Master Server

To start off with the Master server configuration, proceed and open the MySQL configuration file by typing the following command.

$ sudo vim /etc/my.cnf

In the mysqld section, append the lines as shown below.

bind-address =173.82.120.14
server-id = 1
log_bin =mysql-bin
Configure MySQL Master Server

Configure MySQL Master Server

Finally, restart the MySQL service.

$ sudo systemctl restart mysqld

Now we are going to create a replication user. Therefore, log in to your MySQL master server as the root user and provide the password.

$ sudo mysql -u root -p

Now run the following commands to create the replica user while at the same time granting the slave access to the user. Remember to use your machines IP address.

mysql> CREATE USER 'replica'@'173.82.115.165' IDENTIFIED BY 'strong_password';
mysql> GRANT REPLICATION SLAVE ON *.*TO 'replica'@'173.82.115.165';
Create MySQL Replication User

Create MySQL Replication User

Now, you are going to type the following command that will print the binary filename and position.

mysql> SHOW MASTER STATUS\G
Check MySQL Master Replication Status

Check MySQL Master Replication Status

Remember to take note of the resulting filename msql-bin.000002 and its position 939.

Step 4: Configuring the MySQL Slave Server

Just like the process of setting up the master, you should make the following changes to the mysql slave configuration file.

$ sudo vim  /etc/my.cnf

Append the following lines in the configuration file under mysqld section.

bind-address =173.82.115.165
server-id = 2
log_bin =mysql-bin
Configure MySQL Slave Server

Configure MySQL Slave Server

Restart the server.

$ sudo systemctl restart mysqld

Now the next step is to configure the slave server to replicate from the Master server. Log in to MySQL server.

$ sudo mysql -u root -p
<./pre>
First, stop the replication threads.
mysql> STOP SLAVE;
Stop MySQL Slave Server

Stop MySQL Slave Server

Now, run the following query that will configure the slave to replicate from the Master server.

mysql> CHANGE MASTER TO
    -> MASTER_HOST='173.82.120.14' ,
    -> MASTER_USER='replica' ,
    -> MASTER_PASSWORD='[email protected]' ,
    -> MASTER_LOG_FILE='mysql-bin.000002' ,
    -> MASTER_LOG_POS=939;
Configure MySQL Slave to Replicate Data from Master

Configure MySQL Slave to Replicate Data from Master

Make sure you are using the correct IP username and password. Also, use the filename and position that you got from the master server.

Finally, type the following command to start the slave threads.

mysql> START SLAVE;
Start MySQL Slave Server

Start MySQL Slave Server

Step 5: Testing MySQL Master-Slave Replication

At this point, you have completed the configuration of both the master and slave servers. We now need to verify if the configuration is working and if the replication can take place.

To do this, head out to the master server and log in to the MySQL database server.

$ sudo mysql -u root -p

Create a sample database.

mysql> CREATE DATABASE replication_database;
Create MySQL Replication Database

Create MySQL Replication Database

Now head out to the Slave server and again, log in to MySQL database server.

$ sudo mysql -u root -p

Now list all the databases using the following command.

mysql> SHOW DATABASES;
Verify MySQL Master Slave Replication

Verify MySQL Master Slave Replication

If you see the created database, then the MySQL Master Slave Replication setup works.

Conclusion

Replication is a fairly simple process that can be easily done. In this guide, you have learned how you can create a replication of a MySQL master to slave in a RHEL 8 Linux.

Best Affordable Linux and WordPress Services For Your Business
Outsource Your Linux and WordPress Project and Get it Promptly Completed Remotely and Delivered Online.

If You Appreciate What We Do Here On TecMint, You Should Consider:

  1. Stay Connected to: Twitter | Facebook | Google Plus
  2. Subscribe to our email updates: Sign Up Now
  3. Get your own self-hosted blog with a Free Domain at ($3.45/month).
  4. Become a Supporter - Make a contribution via PayPal
  5. Support us by purchasing our premium books in PDF format.
  6. Support us by taking our online Linux courses

We are thankful for your never ending support.

James Kiarie

This is James, a certified Linux administrator and a tech enthusiast who loves keeping in touch with emerging trends in the tech world. When I'm not running commands on the terminal, I'm taking listening to some cool music. taking a casual stroll or watching a nice movie.

Your name can also be listed here. Got a tip? Submit it here to become an TecMint author.

RedHat RHCE and RHCSA Certification Book
Linux Foundation LFCS and LFCE Certification Preparation Guide

You may also like...

Got something to say? Join the discussion.

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.