Master‑Slave MySQL Replication: Step‑by‑Step Setup and Synchronization Guide

Introduction

In production environments the importance of data reliability is paramount; a single point of failure can crash the backend database or overload it. Using MySQL master‑slave replication offloads read traffic to a slave, improves high availability, and ensures data reliability.

MySQL Master‑Slave Replication Architecture

Architecture diagram:

Replication Principle

The master records data changes in a binary log. The slave periodically checks this log; when changes are detected it starts an I/O thread to fetch the events, writes them to a relay log, and a SQL thread replays the events to keep data in sync. After processing, both threads sleep until the next change.

Configuration Steps

Requirements:

Both MySQL instances must run the same version (or master version lower than slave).

System clocks must be synchronized.

Master server configuration:

1. Edit /etc/my.cnf and add: log-bin=/mydata/data/binlogs/master-bin 2. Create the directory and set ownership:

mkdir /mydata/binlogs/

chown -R mysql.mysql /mydata/binlogs/

3. Create a replication user:

grant replication slave,replication client on *.* to 'repluser'@'10.1.10.%' identified by 'pass';

flush privileges;

Slave server configuration:

1. Edit /etc/my.cnf, comment out binary logging, enable relay logging, set a unique server‑id:

server-id=11

relay-log=/mydata/relaylogs/relay-bin

2. Create the relay directory and set ownership:

mkdir /mydata/relaylogs/

chown -R mysql.mysql /mydata/relaylogs/

3. Point the slave to the master:

change master to master_host='10.1.10.1',master_user='repluser',master_password='pass';

4. Start replication threads: start slave; Test synchronization by inserting data on the master and verifying it appears on the slave (the slave is read‑only).

Handling Out‑of‑Sync Situations

When the master has accumulated significant data, perform a full backup on the master, stop the slave’s I/O and SQL threads, restore the backup on the slave, then resume replication. The key steps are:

Lock tables on the master and take a complete backup while rolling the binary logs.

Import the backup on the slave.

After these steps, start the I/O and SQL threads on the slave (e.g., start slave) to resume synchronization.

Conclusion

The critical actions are locking the master for a full backup with binary log rotation, and performing a point‑in‑time restore on the slave to bring both servers back into sync.