How to Setup MySQL Master-Slave Replication on RHEL 8

designlinux

4 years ago

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 set up a MySQL master-slave replication on an 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

Note: The instructions provided in this article will only work if you have enabled the Red Hat subscription on RHEL 8.

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

1. The latest version of the MySQL 8.x is already included in the default repository of RHEL 8 and you can install it using the following yum command.

# yum -y install @mysql

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.

# systemctl enable mysqld
# systemctl start mysqld

Next, you need to secure your MySQL installation by running the security script that comes with several security-based operations such as setting the root password, removing anonymous users, disallow root login remotely, remove test database and reload privilege.

# mysql_secure_installation

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

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

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';

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

mysql> SHOW MASTER STATUS\G

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

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

First, stop the replication threads.

mysql> STOP SLAVE;

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

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;

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;

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

$ sudo mysql -u root -p

Now list all the databases using the following command.

mysql> SHOW DATABASES;

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 an RHEL 8 Linux.