How to Setup MySQL Master-Slave Replication on RHEL 7?

In MySQL Replication process, one server data is automatically copied to another backup server in real-time. Database Replication provides fault tolerance and redundancy. If master will be failed, your data will be recovered from another server.

Following are the steps to configure MySQL master-slave replication on RHEL 7. To do this we require 2 servers with the different IP addresses.

  1. Download and install MySQL server on both the server using the Yum command.

    # wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
    # sudo rpm -ivh mysql57-community-release-el7-9.noarch.rpm # sudo yum install mysql-server
  2. Once MySQL Server is installed, start and enable it for start on boot.

    sudo systemctl enable mysqld
    sudo systemctl start mysqld
  3. Find out the temporary mysql server password using below command.

    # sudo grep 'temporary password' /var/log/mysqld.log
    
  4. Run the below command to harden or secure the MySQL database.

    # sudo mysql_secure_installation
    
  5. Enter the temporary password we just got in step (3) and change the default password.



  6. To provide best security, hit y for all the answers.

Step 1  : Configure Master Server

  1. Open the MySQL configuration file using your favourite editor.

    # sudo vim /etc/my.cnf
    
  2. Add below line in mysqld section.

    # bind-address = Your Master server IP Address
    server-id = 1
    log_bin =mysql-bin


  3. Restart mysql service.

    # sudo systemctl restart mysqld
    
  4. To create replication user, login to mysql server using root and provide the password.

    # sudo mysql -u root -p
    
  5. We will create the replica user with below command and grant the slave access to the user. Make sure you use your server IP address.

    mysql> CREATE USER 'replica'@'173.248.174.102' IDENTIFIED BY '[email protected]';
    mysql> GRANT REPLICATION SLAVE ON *.*TO 'replica'@'173.248.174.102';


  6. Hit the below command to get the binary filename and position.

    mysql> SHOW MASTER STATUS\G
    


  7. We will need to notedown master filename mysql-bin.000034 and its position 154.

Step 2 : Configure Slave Server :

  1. Open the MySQL configuration file using your favourite editor.

    sudo vim /etc/my.cnf
    
  2. Add below line in mysqld section.

    bind-address = Your Slave server IP Address
    server-id = 2
    log_bin =mysql-bin
  3. Restart mysql service.

    sudo systemctl restart mysqld
    
  4. Let us Configure the Slave server to replicate from Master Server. Login to MySQL server.

    sudo mysql -u root -p
    mysql> STOP SLAVE;


  5. We will run the below query to configure the slave server to replicate the Master server.

mysql> CHANGE MASTER TO
    -> MASTER_HOST='173.248.174.101',
    -> MASTER_USER='replica',
    -> MASTER_PASSWORD='[email protected]',
    -> MASTER_LOG_FILE='mysql-bin.000034',
    -> MASTER_LOG_POS=154;

          

  1. Start the Slave Server.

    mysql> START SLAVE;
    

Step 3 : Test the MySQL Master-Slave Replication :

We are done with the configuration of master and slave server. Now, we will test replication on both the server.

  1. Go to Master server and hit below command.

    sudo mysql -u root -p
    
  2. Create a test Database.

    mysql> CREATE DATABASE replica_database;
    
  3. Go to Slave Server and log in to MySQL database server.

    sudo mysql -u root -p
    
  4. List Database using the below command.

    mysql> SHOW DATABASES;
    

If all the configuration is correct, you will see the database created in master there. This is it. Our MySQL master/slave replication works fine.

  • 0 Users Found This Useful

Was this answer helpful?

Related Articles

How to install Virtualmin / Webmin control panel in Ubuntu 16/18?

Before installing Virtualmin/Webmin on Ubuntu 16. We need to make sure that our system is up to...

Do You Provide TUN/TAP On VPS Hosting?

Typically, TUN/TAP is used when you want to utilize VPN services from your VPS. Windows VPS...

Fix :: Linux SSH error "connection refused"

Problem Statement Can't connect to a Linux server(Debian Flavour) via SSH. Error Message...

How to run ClamAV scan from SSH in cPanel server?

In this article, we'll discuss how to run ClamAV scan via SSH in cPanel/WHM server. ClamAV is one...

How To install Nginx on CentOS 7?

Nginx is a Web server that can be installed on Linux Server. You may have heard about Apache Web...