How to Configure MySQL Master-Slave Replication for Data Redundancy and Scalability

Master-slave replication is one of the most reliable techniques for maintaining multiple copies of MySQL data, ensuring data redundancy, and improving scalability for your applications. This guide walks you through the process of setting up MySQL Master-Slave replication between two RHEL (Red Hat Enterprise Linux) servers.

Table of Content

What is MySQL Master-Slave Replication?

MySQL Master-Slave replication allows database administrators to replicate data from one MySQL server (the master) to one or more slave servers. This setup creates a real-time backup of your database, ensuring high availability. If the master server experiences downtime, you can quickly switch to the slave server and keep your application running.

In this guide, we’ll configure MySQL Master-Slave replication on two RHEL nodes, designating one as the master and the other as the slave.

Pre-Requisites for MySQL Replication Setup

Before you begin the replication setup, make sure you have the following:

1. Two Virtual Machines: One for the master server and one for the slave server.
2. Network Connectivity: Ensure that both servers can communicate with each other.
3. MySQL Installed: Both servers must have MySQL Server installed. You can follow the installation steps below if MySQL isn’t installed yet.

How to Install MySQL Server on RHEL

Follow these steps to install MySQL Server on your RHEL nodes:

1. Install MySQL Server using YUM or DNF:

sudo yum install mysql-server

2. Start the MySQL Service:

sudo systemctl start mysqld

3. Verify MySQL is Running:

sudo systemctl status mysqld

If MySQL is up and running, you’ll see confirmation in the output. 

Configuring the Master Node

To configure the master node for replication, follow these steps:

1. Edit MySQL Configuration:

Open the MySQL configuration file /etc/my.cnf and add the following lines under the [mysqld] section:

server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = <DATABASE_NAME_TO_BE_REPLICATED>

‘server-id’: Set a unique ID for the master server (e,g. 1)
‘log-bin’: Enables binary logging, which is required for replication.
‘binlog-do-db’: Specify the name of the database you want to replicate.

The file should look like this following modification. 

2. Restart MySQL:

sudo systemctl restart mysqld

3. Create a Replication User:

Log in to MySQL on the master server and create a replication user:

CREATE USER 'slave_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%';
FLUSH PRIVILEGES;

Replace slave_user and password with your preferred credentials.

4. Check Master Status:

Note the current binary log file name and position as you’ll need this information to configure the slave node:

SHOW MASTER STATUS;

Configuring the Slave Node

Now, let’s configure the slave node to start replication.

1. Edit MySQL Configuration:

Open the MySQL configuration file /etc/my.cnf on the slave server and add this line under the [mysqld] section:

server-id = 2

The file should look like this following modification. 

2. Restart MySQL:

sudo systemctl restart mysqld

3. Set Up Replication:

Log in to MySQL on the slave server and configure the slave to replicate data from the master server:

CHANGE MASTER TO
MASTER_HOST = 'master_ip',
MASTER_USER = 'slave_user',
MASTER_PASSWORD = 'password',
MASTER_LOG_FILE = 'master_log_file',
MASTER_LOG_POS = 'position';

Replace master_ip, slave_user, password, master_log_file, and position with the relevant details from the master node.

4. Start Replication:

START SLAVE;

5. Verify Replication:

To ensure replication is working correctly, check the slave’s status:

SHOW SLAVE STATUS\G;

Both Slave_IO_Running and Slave_SQL_Running should be set to “Yes.”

Monitoring and Troubleshooting MySQL Replication

Once your master-slave replication setup is complete, you’ll want to monitor the replication status regularly to ensure everything is running smoothly. Use the following command to check the replication status:

SHOW SLAVE STATUS\G;

If there are issues with replication, such as data inconsistencies or errors, troubleshoot the replication by checking the log files on both the master and slave servers.

Conclusion

Setting up MySQL Master-Slave replication on RHEL nodes is a powerful way to ensure data redundancy and scalability for your MySQL databases. With replication in place, any changes made on the master server will automatically sync with the slave server, providing a real-time backup. Be sure to monitor the replication status regularly and perform routine backups to guarantee data integrity.