How to Configure MySQL Primary-Replica Replication on RHEL 7

MySQL replication allows data written to one server (the primary, formerly called master) to be automatically copied to one or more other servers (replicas, formerly called slaves). This is one of the most widely deployed MySQL high-availability techniques: it provides read-scale-out by offloading SELECT queries to replicas, enables near-real-time off-site backups, and forms the foundation for more advanced topologies such as failover clusters. This tutorial covers setting up classic asynchronous binary-log-based primary-replica replication between two RHEL 7 servers running MySQL 5.7.

Prerequisites

  • Two RHEL 7 servers: one designated as primary (db-primary, e.g. 192.168.1.10) and one as replica (db-replica, e.g. 192.168.1.11)
  • MySQL 5.7 installed on both servers via yum
  • MySQL service running on both servers
  • Network connectivity between the two servers on port 3306
  • Root or sudo access on both servers
  • A matching or compatible MySQL version on both nodes (replica version must be >= primary version)

Step 1: Configure Binary Logging on the Primary

Binary logging is the mechanism MySQL uses to record all changes that need to be replicated. Edit /etc/my.cnf on the primary server to enable it and assign a unique server ID:

sudo vi /etc/my.cnf

Add or verify the following lines under the [mysqld] section:

[mysqld]
# Unique integer identifier — must differ on every server in the topology
server-id          = 1

# Enable binary logging; the value is the log file name prefix
log-bin            = /var/lib/mysql/mysql-bin

# Recommended: use ROW format for deterministic replication
binlog_format      = ROW

# Database to replicate (omit to replicate all databases)
# binlog-do-db     = myapp

# Expire binary logs after 7 days to prevent disk exhaustion
expire_logs_days   = 7

Restart MySQL to apply the changes:

sudo systemctl restart mysqld

Step 2: Configure the Replica Server’s my.cnf

On the replica server, assign a different server ID. You do not need to enable binary logging on the replica unless it will itself act as a primary for another downstream replica (chained replication):

sudo vi /etc/my.cnf
[mysqld]
server-id          = 2

# Optional: relay log name prefix (defaults are fine)
relay-log          = /var/lib/mysql/mysql-relay-bin

# Prevent replica from accepting writes accidentally
read_only          = 1
sudo systemctl restart mysqld

Step 3: Create the Replication User on the Primary

Log in to MySQL on the primary server and create a dedicated user account that the replica will use to connect and pull binary log events. Use a strong password:

mysql -u root -p
CREATE USER 'replicator'@'192.168.1.11' IDENTIFIED BY 'Repl!cati0nPass';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.168.1.11';
FLUSH PRIVILEGES;

The REPLICATION SLAVE privilege is the only privilege the replica user needs. Restrict its host to the exact IP of the replica server.

Step 4: Take a Consistent Snapshot of the Primary

Before starting the replica you need to copy the primary’s current data state along with the exact binary log position where replication should begin. Acquire a global read lock to freeze writes, record the position, dump the data, then release the lock:

mysql -u root -p
FLUSH TABLES WITH READ LOCK;

In a separate terminal (keep the first session open to hold the lock), record the binary log file name and position:

SHOW MASTER STATUS;

The output will look similar to:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      1234 |              |                  |
+------------------+----------+--------------+------------------+

Note the File and Position values — you will need them in Step 6. Now dump all databases while the lock is held:

mysqldump -u root -p --all-databases --master-data=2 --single-transaction 
  --routines --triggers > /tmp/primary_full_dump.sql

The --master-data=2 flag embeds the binary log coordinates as a comment inside the dump file, which is useful for reference. After the dump completes, go back to the first terminal and release the lock:

UNLOCK TABLES;

Step 5: Copy the Dump to the Replica and Restore It

Transfer the dump file to the replica server:

scp /tmp/primary_full_dump.sql [email protected]:/tmp/

On the replica server, restore the dump into MySQL:

mysql -u root -p < /tmp/primary_full_dump.sql

This populates the replica with an identical copy of the primary’s data at the moment the dump was taken.

Step 6: Configure Replication on the Replica (CHANGE MASTER TO)

On the replica server, log in to MySQL and issue the CHANGE MASTER TO command, using the binary log file and position recorded in Step 4:

mysql -u root -p
CHANGE MASTER TO
  MASTER_HOST     = '192.168.1.10',
  MASTER_USER     = 'replicator',
  MASTER_PASSWORD = 'Repl!cati0nPass',
  MASTER_LOG_FILE = 'mysql-bin.000003',
  MASTER_LOG_POS  =  1234;

Replace mysql-bin.000003 and 1234 with the exact values from your SHOW MASTER STATUS output.

Step 7: Start the Replica Threads

START SLAVE;

This starts two background threads on the replica: the I/O thread (connects to the primary and downloads binary log events into the relay log) and the SQL thread (reads the relay log and applies events to the local database).

Step 8: Verify Replication with SHOW SLAVE STATUS

Check the replication status immediately after starting:

SHOW SLAVE STATUSG

Look for these key fields in the output:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Last_IO_Error:
Last_SQL_Error:

Both Slave_IO_Running and Slave_SQL_Running must be Yes. The Seconds_Behind_Master value will initially be high while the replica catches up; it should approach zero. Any error messages in Last_IO_Error or Last_SQL_Error indicate a problem that must be resolved.

Step 9: Test Replication is Working

On the primary, create a test database and table:

mysql -u root -p -e "CREATE DATABASE replication_test;"
mysql -u root -p -e "CREATE TABLE replication_test.ping (id INT AUTO_INCREMENT PRIMARY KEY, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);"
mysql -u root -p -e "INSERT INTO replication_test.ping VALUES ();"

On the replica, verify the data appeared:

mysql -u root -p -e "SELECT * FROM replication_test.ping;"

You should see the inserted row. Replication is working correctly.

Step 10: Firewall Configuration

Ensure the primary server’s firewall allows the replica to connect on port 3306:

# On the primary server
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.11" port port="3306" protocol="tcp" accept'
sudo firewall-cmd --reload

MySQL primary-replica replication on RHEL 7 is a proven and relatively simple approach to building read scalability and data redundancy into your database infrastructure. The key steps — enabling binary logging with a unique server-id, creating a restricted replication user, capturing a consistent snapshot, and pointing the replica at the correct log position with CHANGE MASTER TO — form a workflow you can automate and repeat. Once replication is running, monitor SHOW SLAVE STATUSG regularly and set up alerting on Seconds_Behind_Master to catch replication lag before it becomes a problem.