Step-by-Step Guide to Setting Up MySQL 5.7 Master‑Slave Replication

1. Environment Preparation (Both Master and Slave)

Configure a static IP address, restart the network service, disable the firewall for testing, and set SELinux to permissive mode.

# 1.1 Configure static IP (example for the master node)
sudo vi /etc/sysconfig/network-scripts/ifcfg-ens33

BOOTPROTO=static    # change to static IP
ONBOOT=yes          # enable at boot
IPADDR=192.168.1.100
NETMASK=255.255.255.0
GATEWAY=192.168.1.1
DNS1=8.8.8.8

Restart network:

sudo systemctl restart network

Disable firewall (testing only):

# Stop and disable firewalld
sudo systemctl stop firewalld
sudo systemctl disable firewalld

Set SELinux to permissive and disable enforcement:

# Disable SELinux
sudo setenforce 0
sudo sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

2. Install MySQL 5.7

Add the official MySQL repository and install the server package.

# Install Yum repository
sudo yum install -y https://repo.mysql.com/mysql57-community-release-el7-11.noarch.rpm

# Install MySQL server
sudo yum install -y mysql-community-server

# Start and enable the service
sudo systemctl start mysqld
sudo systemctl enable mysqld

# Retrieve the temporary root password
sudo grep 'temporary password' /var/log/mysqld.log

Run the security script to set a strong root password and remove insecure defaults.

sudo mysql_secure_installation

3. Master (Primary) Configuration

Edit /etc/my.cnf and add the following under the [mysqld] section:

server-id = 1               # unique ID for the master
log-bin = mysql-bin        # enable binary logging
binlog_format = ROW        # row‑based logging for consistency
expire_logs_days = 7       # auto‑purge logs older than 7 days
# Optional: binlog-do-db = mydb   # replicate only this DB
# Optional: binlog-ignore-db = mysql   # ignore system DB

Restart MySQL to apply changes:

sudo systemctl restart mysqld

Create a replication user with the required privileges:

mysql -u root -p
CREATE USER 'repl'@'%' IDENTIFIED BY 'ReplPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

Record the current binary log file and position:

SHOW MASTER STATUS;

Note the values of File (e.g., mysql-bin.000002 ) and Position (e.g., 154 ) for the slave configuration.

4. Slave (Replica) Configuration

Edit /etc/my.cnf on the slave and add:

server-id = 2               # must differ from master
relay-log = mysql-relay-bin
read_only = 1              # prevent writes on the replica

Restart MySQL:

sudo systemctl restart mysqld

Configure the replication channel using the master’s IP, replication user, binary log file, and position captured earlier:

mysql -u root -p
STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl',
  MASTER_PASSWORD='ReplPass123!',
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=154;
START SLAVE;

Verify the replica status:

SHOW SLAVE STATUS\G

Key fields to check: Slave_IO_Running = Yes, Slave_SQL_Running = Yes, Seconds_Behind_Master = 0, and no errors in Last_IO_Error or Last_SQL_Error .

5. Data Synchronization Test

Create a test database and table on the master, insert rows, then query the same on the slave to confirm replication.

-- On master
CREATE DATABASE sync_test;
USE sync_test;
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO users VALUES (1,'Alice'), (2,'Bob');

-- On slave
SELECT * FROM sync_test.users;

Expected output shows both rows replicated.

6. Troubleshooting Checklist

Master‑Slave connection failure (Slave_IO_Running: No) Test network connectivity: telnet 192.168.1.100 3306 Verify replication user privileges: SHOW GRANTS FOR 'repl'@'%';

Data inconsistency (Slave_SQL_Running: No) Skip a single error (use with caution): STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; Re‑sync the entire dataset using mysqldump on the master and import on the slave.

Writes on the slave causing conflicts SET GLOBAL read_only = 1; # ordinary users read‑only SET GLOBAL super_read_only = 1; # even root read‑only (MySQL 5.7+)

7. Advanced Production‑Ready Configurations

7.1 GTID‑based Replication

Add to my.cnf on both master and slave:

gtid_mode = ON
enforce_gtid_consistency = ON

On the slave, use automatic position:

CHANGE MASTER TO MASTER_AUTO_POSITION = 1;

7.2 Semi‑Synchronous Replication

Install and enable the plugins:

# On master
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;

# On slave
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

7.3 Monitoring Script

Save the following as /opt/mysql_monitor.sh and schedule it via cron (every 5 minutes) to log replication health.

#!/bin/bash
STATUS=$(mysql -u root -p'密码' -e "SHOW SLAVE STATUS\G")
echo "$(date +"%Y-%m-%d %H:%M:%S")" >> /var/log/mysql_replication.log
echo "$STATUS" | grep "Running\|Behind\|Error" >> /var/log/mysql_replication.log

Add to crontab:

*/5 * * * * /bin/bash /opt/mysql_monitor.sh

8. Core Replication Principles

The master writes changes to the binary log; the slave’s I/O thread pulls the binlog and writes it to a relay log; the SQL thread replays the relay log, applying the changes.

Master writes → binlog → Slave I/O pulls → relay log → Slave SQL replays

Binary log formats:

STATEMENT : logs SQL statements (may cause inconsistencies).

ROW : logs actual row changes (recommended).

MIXED : hybrid mode.

End of guide.