Master‑Slave Replication in MySQL 5.7: Complete Step‑by‑Step Guide

Environment Preparation

Version requirement: Install MySQL 5.7 on both master and slave (identical versions are ideal).

Network configuration: Ensure network connectivity and open port 3306 on firewalls.

Data consistency: Verify that master and slave data are synchronized before configuring (can be skipped for new setups).

Clock sync: Enable NTP/Chrony to avoid GTID replication errors.

Permission planning: Create a dedicated replication user instead of using root.

Data Synchronization Options

Use these methods when the source database already contains data; otherwise you can skip this step.

Option 1: mysqldump (small databases)

# Master dump
mysqldump --single-transaction --master-data=2 --all-databases > fullbackup.sql
# Import on slave
mysql < fullbackup.sql

Option 2: xtrabackup (recommended for large databases)

Supports online hot backup, avoiding table locks.

Master Server (Master) Configuration

1. Edit configuration file /etc/my.cnf

[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
sync_binlog = 1
expire_logs_days = 7
# GTID (recommended)
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON

2. Restart MySQL

systemctl restart mysqld

3. Create replication user

CREATE USER 'repl'@'10.%.%.%' IDENTIFIED BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.%.%.%';
FLUSH PRIVILEGES;

4. Get master status (only needed if not using GTID)

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;  -- records File and Position
UNLOCK TABLES;

Slave Server (Slave) Configuration

1. Edit configuration file /etc/my.cnf

[mysqld]
server-id = 2
relay_log = mysql-relay-bin
read_only = ON
super_read_only = ON   # prevents superuser writes (5.7.8+)
log_bin = mysql-bin    # optional for chained replication
# GTID (recommended)
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE

2. Restart MySQL

systemctl restart mysqld

3. Configure replication connection

Based on binlog position (traditional method)

CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='repl',
  MASTER_PASSWORD='StrongPassword123!',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

Based on GTID (recommended)

CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='repl',
  MASTER_PASSWORD='StrongPassword123!',
  MASTER_AUTO_POSITION = 1;

Replication Verification

1. Start replication

START SLAVE;

2. Check status

SHOW SLAVE STATUS\G

Confirm the following key fields: Slave_IO_Running = Yes , Slave_SQL_Running = Yes , Seconds_Behind_Master = 0 , and that Retrieved_Gtid_Set matches Executed_Gtid_Set .

Common Troubleshooting

Connection failures : verify firewall rules, bind-address, and that the replication user has proper privileges.

Data inconsistency : run pt-table-checksum and ensure replicate-* filters are not unintentionally excluding tables.

GTID conflicts : make sure each instance has a unique server_uuid.

Replication errors : stop the slave, set sql_slave_skip_counter = 1, then start the slave again.

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

Advanced Settings & Best Practices

Semi‑synchronous replication for higher reliability:

plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_slave_enabled = 1
rpl_semi_sync_master_timeout = 1000

Log cleanup to prevent disk exhaustion:

PURGE BINARY LOGS TO 'mysql-bin.000123';
PURGE BINARY LOGS BEFORE '2025-08-01 00:00:00';

Replication filtering (e.g., replicate-do-db, replicate-wild-do-table) to sync only required databases or tables.

replicate-do-db = mydb
replicate-wild-do-table = mydb%.%

Monitoring & alerting: track Seconds_Behind_Master and Last_IO_Error using Prometheus + mysqld_exporter or Percona PMM, and set latency‑threshold alerts.

Following these steps gives you a MySQL 5.7 master‑slave replication environment that ensures data consistency, fault tolerance, observability, and production‑grade maintainability.