Step‑by‑Step Guide to Configure MySQL 5.7 Master‑Slave Replication on CentOS 7.6

Overview

The article explains how to build a MySQL master‑slave replication environment using MySQL 5.7 on two CentOS 7.6 virtual machines (IP 192.168.1.100 for the master and 192.168.1.101 for the slave). It covers installation, configuration of my.cnf, user creation, service management, and verification of replication status.

Prerequisites

Two CentOS 7.6 servers (virtual machines)

MySQL 5.7 installed on both hosts

Root access to edit configuration files and restart services

1. Install MySQL 5.7

# Check if MySQL is already installed
yum list installed mysql
# List RPM packages containing "mysql"
rpm -qa | grep mysql
# Install client package
yum install mysql
# Install server package
yum install mysql-server

2. Open MySQL Port

# Add firewall rule for port 3306
iptables -A INPUT -p tcp --dport 3306 -j ACCEPT
# Restart firewall service
systemctl restart iptables

3. Configure the Master Server

Edit /etc/my.cnf and add the following lines (replace the server‑id with a unique value, e.g., 100):

# Enable binary logging
log-bin=mysql-bin
# Unique server identifier
server-id=100
# Do not replicate internal schemas
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
# Replicate only the required database (example: test)
binlog-do-db=test

Restart MySQL and verify the master status:

service mysqld restart
mysql -u root -p
SHOW MASTER STATUS;

4. Create Replication User on Master

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.101' IDENTIFIED BY 'repl_password';
FLUSH PRIVILEGES;

5. Configure the Slave Server

Edit /etc/my.cnf on the slave with matching binary‑log settings but a different server-id (e.g., 101):

# Enable binary logging on the slave
log-bin=mysql-bin
server-id=101
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
# Slave‑specific options
log-slave-updates
skip-errors=all
slave-net-timeout=60

Restart MySQL on the slave:

service mysqld restart
mysql -u root -p

6. Set Up Replication on the Slave

# Stop any running slave process
STOP SLAVE;
# Point the slave to the master
CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl_password',
  MASTER_LOG_FILE='mysql-bin.000007',
  MASTER_LOG_POS=120;
# Start the slave
START SLAVE;

Important note: MASTER_LOG_FILE and MASTER_LOG_POS must match the values shown by SHOW MASTER STATUS on the master.

7. Verify Replication

# On the master
SHOW MASTER STATUS;
# On the slave
SHOW SLAVE STATUS\G

Key fields to check: Slave_IO_Running and Slave_SQL_Running should be Yes Seconds_Behind_Master should be 0 (or close to 0)

Ensure Master_Host, Master_User, and log positions are correct

8. Common Issues & Solutions

Data inconsistency after DML operations : Make sure you select the correct database with USE db_name; before executing INSERT/UPDATE/DELETE on the master.

Unable to view binary logs : Verify that log_bin is enabled ( SHOW VARIABLES LIKE 'log_bin%';) and that the MySQL user has the necessary privileges.

Cleaning old master logs : Use PURGE MASTER LOGS TO 'mysql-bin.004'; or RESET SLAVE; (caution: this clears slave configuration).

9. Useful Commands

# Show master status
SHOW MASTER STATUS;
# Show slave status (full output)
SHOW SLAVE STATUS\G
# List binary logs
SHOW BINARY LOGS;
# View process list
SHOW PROCESSLIST;
# Reset slave (use with care)
RESET SLAVE;

After completing the steps, you can create a table on the master and query it from the slave to confirm that replication works.