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 Docker on Ubuntu 18.04?

Docker is a container-based tool used to create, deploy, and run the applications by using...

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...

How to Install SHOUTCast Radio Server on Linux VPS?

This tutorial will assist you to install SHOUTcast Distributed Network Audio Server (DNAS 2.0) in...

Can I upgrade my VPS plan without losing any data or settings?

Yes, you can upgrade your existing VPS to higher plan anytime. Your data will remain intact. We...

How to Check Bandwidth Usage from WHM Panel?

Within WHM (Web Host Manager), you can check the bandwidth usage of each website hosted. Here are...