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 run ClamAV and Maldet together on cPanel server?

You may be familiar with ClamAV and Maldet scanner (aka Linux Malware Detect). They're widely...

What if I exceed the bandwidth limit I am assigned with VPS?

Our system continually monitors the bandwidth usage of all our VPS. In case if your total monthly...

R1Soft : An error occurred while loading the hcpdriver module

R1soft CDP backup allows for full server backup and you can use it to perform the bare metal...

How to Setup NTP Server in CentOS?

NTP enables the automatically sync your system time with a remote server. With the help of NTP...

How to install Mongodb in CentOS?

MongoDB is a Free and open-source cross-platform document-oriented database program. Classified...