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.
- 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 - Once MySQL Server is installed, start and enable it for start on boot.
sudo systemctl enable mysqld
sudo systemctl start mysqld - Find out the temporary mysql server password using below command.
# sudo grep 'temporary password' /var/log/mysqld.log
- Run the below command to harden or secure the MySQL database.
# sudo mysql_secure_installation
- Enter the temporary password we just got in step (3) and change the default password.
- To provide best security, hit y for all the answers.
Step 1 : Configure Master Server
- Open the MySQL configuration file using your favourite editor.
# sudo vim /etc/my.cnf
- Add below line in mysqld section.
# bind-address = Your Master server IP Address
server-id = 1
log_bin =mysql-bin - Restart mysql service.
# sudo systemctl restart mysqld
- To create replication user, login to mysql server using root and provide the password.
# sudo mysql -u root -p
- 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'; - Hit the below command to get the binary filename and position.
mysql> SHOW MASTER STATUS\G
- We will need to notedown master filename mysql-bin.000034 and its position 154.
Step 2 : Configure Slave Server :
- Open the MySQL configuration file using your favourite editor.
sudo vim /etc/my.cnf
- Add below line in mysqld section.
bind-address = Your Slave server IP Address
server-id = 2
log_bin =mysql-bin - Restart mysql service.
sudo systemctl restart mysqld
- Let us Configure the Slave server to replicate from Master Server. Login to MySQL server.
sudo mysql -u root -p
mysql> STOP SLAVE; - 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;
- 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.
- Go to Master server and hit below command.
sudo mysql -u root -p
- Create a test Database.
mysql> CREATE DATABASE replica_database;
- Go to Slave Server and log in to MySQL database server.
sudo mysql -u root -p
- 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.