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 sync time using NTP in Ubuntu server?

Linux server users might have noticed that after adjusting the system clock to the correct time,...

How to Install FTP on Ubuntu 18.04?

FTP stands for File Transfer Protocol used to share files/folders from one server to another...

how to configure SSH key-based Authentication in Linux VPS or Dedicated Server

This article will guide how to configure SSH key-based Authentication in Linux VPS or Dedicated...

How to create users and groups in CentOS7?

Once the Linux system is configured, adding and removing users is one of the most basic tasks...

How to Hide Your NGINX Server Version.

Sometimes hackers target your server with a technic called banner grabbing. Usually, banner...