How to Setup MySQL Master-Slave Replication on RHEL 8

Table of Contents#

1. Prerequisites
2. Step 1: Configure the Master Server
   • 1.1 Install MySQL on RHEL 8
   • 1.2 Configure Master’s my.cnf
   • 1.3 Restart MySQL & Secure Installation
   • 1.4 Create a Replication User
   • 1.5 Get Master Status
3. Step 2: Configure the Slave Server
   • 2.1 Install MySQL on the Slave
   • 2.2 Configure Slave’s my.cnf
   • 2.3 Restart MySQL
   • 2.4 Connect Slave to Master
   • 2.5 Start Replication & Verify Status
4. Troubleshooting Common Issues
5. Conclusion
6. References

Prerequisites#

Before starting, ensure:

• Two RHEL 8 servers (e.g., master with IP 192.168.1.10, slave with IP 192.168.1.20).
• MySQL 8.0 (or compatible) installed on both servers.
• Network connectivity (ping between servers, port 3306 open if using default MySQL port).
• Root/sudo access on both servers.
• Time synchronization (via chrony or ntp) to avoid replication errors due to clock drift.

Step 1: Configure the Master Server#

1.1 Install MySQL on RHEL 8#

MySQL isn’t in RHEL 8’s default repositories. Use the official MySQL repository:

# 1. Add the MySQL 8.0 repository
sudo dnf install -y https://dev.mysql.com/get/mysql80-community-release-el8-3.noarch.rpm
 
# 2. Install MySQL Server
sudo dnf install -y mysql-community-server
 
# 3. Start and enable MySQL
sudo systemctl enable --now mysqld
 
# 4. Verify status
sudo systemctl status mysqld

1.2 Configure Master’s my.cnf#

Edit the MySQL configuration file (usually /etc/my.cnf.d/mysql-server.cnf or /etc/my.cnf) to enable binary logging and set a unique server-id:

# Edit the config file
sudo nano /etc/my.cnf.d/mysql-server.cnf
 
# Add the following under [mysqld]:
[mysqld]
server-id = 1          # Unique ID (1 for Master, 2+ for Slaves)
log-bin = /var/log/mysql/mysql-bin.log  # Enable binary logging
binlog-do-db = my_database  # (Optional) Replicate only this DB; comment to replicate all
binlog-ignore-db = mysql,information_schema  # (Optional) Ignore system DBs
sync_binlog = 1        # Ensure binary logs are synced to disk (durability)
innodb_flush_log_at_trx_commit = 1  # ACID compliance

Create the log directory and set permissions:

sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql

1.3 Restart MySQL & Secure Installation#

Restart MySQL to apply changes:

sudo systemctl restart mysqld

Secure the MySQL installation (set root password, remove anonymous users, etc.):

sudo mysql_secure_installation

1.4 Create a Replication User#

Log into the MySQL shell and create a user with REPLICATION SLAVE privileges (replace repl_user and strong_password):

mysql -u root -p
CREATE USER 'repl_user'@'192.168.1.%' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.%';
FLUSH PRIVILEGES;
EXIT;

1.5 Get Master Status#

In the MySQL shell, run SHOW MASTER STATUS to get the binary log file and position (needed for the Slave):

mysql -u root -p
SHOW MASTER STATUS;

You’ll see output like:

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      156 | my_database  | mysql,info...    |                   |
+------------------+----------+--------------+------------------+-------------------+

Note the File (e.g., mysql-bin.000001) and Position (e.g., 156)—we’ll use these in the Slave configuration.

Step 2: Configure the Slave Server#

2.1 Install MySQL on the Slave#

Repeat the Master’s MySQL installation steps (Section 1.1) on the Slave server.

2.2 Configure Slave’s my.cnf#

Edit the Slave’s my.cnf (e.g., /etc/my.cnf.d/mysql-server.cnf):

[mysqld]
server-id = 2          # Unique ID (different from Master)
relay-log = /var/log/mysql/mysql-relay-bin.log  # Relay logs for replication
relay-log-index = /var/log/mysql/mysql-relay-bin.index
log-bin = /var/log/mysql/mysql-bin.log  # (Optional) Enable if Slave will act as a Master (chain replication)
read-only = 1          # Set Slave to read-only (except replication threads)
binlog-do-db = my_database  # Match Master’s config (if using)

Create the log directory and set permissions (same as Master):

sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql

2.3 Restart MySQL#

Restart the Slave’s MySQL service:

sudo systemctl restart mysqld

2.4 Connect Slave to Master#

Log into the Slave’s MySQL shell and configure the replication connection (replace MASTER_LOG_FILE and MASTER_LOG_POS with values from Step 1.5):

mysql -u root -p
CHANGE MASTER TO
MASTER_HOST='192.168.1.10',      # Master’s IP/hostname
MASTER_USER='repl_user',         # Replication user
MASTER_PASSWORD='strong_password',  # Replication user’s password
MASTER_LOG_FILE='mysql-bin.000001',  # From Master’s SHOW MASTER STATUS
MASTER_LOG_POS=156;             # From Master’s SHOW MASTER STATUS

2.5 Start Replication & Verify Status#

Start the replication threads:

START SLAVE;

Check the Slave’s status:

SHOW SLAVE STATUS\G

Look for:

• Slave_IO_Running: Yes (Master-Slave connection is active).
• Slave_SQL_Running: Yes (Replication SQL thread is active).
• Seconds_Behind_Master: 0 (No lag; may be higher during initial sync).

Troubleshooting Common Issues#

• Replication Threads Not Running: Check the error log (/var/log/mysqld.log) for clues (e.g., wrong credentials, network issues, or mismatched server-id).
• Replication Lag: High Seconds_Behind_Master? Optimize network, reduce Master load, or adjust innodb_buffer_pool_size on the Slave.
• Permissions Issues: Ensure the repl_user has correct privileges, and log directories (/var/log/mysql) have mysql:mysql ownership.
• Time Drift: Use chrony or ntp to synchronize time between Master and Slave.

Conclusion#

You’ve successfully set up MySQL Master-Slave replication on RHEL 8! This setup provides load balancing, redundancy, and high availability. For advanced use cases:

• Add more Slaves for read scaling.
• Implement Master-Master replication for bidirectional sync.
• Use tools like Orchestrator for automated failover.

References#

• MySQL 8.0 Replication Documentation
• RHEL 8 MySQL Installation Guide
• Chrony Time Synchronization on RHEL 8

This guide is optimized for RHEL 8 and MySQL 8.0. Adjust configurations (e.g., server-id, binlog settings) based on your environment.