Complete MySQL Master‑Slave Replication: Principles, Architecture, and Setup

MySQL master‑slave replication follows a simple principle: the master handles all write operations and records every data change in the binary log (binlog), while one or more slaves read the binlog, replay the events, and serve read requests, ensuring data consistency across the cluster.

Typical scenarios for this architecture include read‑write separation to increase throughput, high‑availability disaster recovery where a slave can be promoted when the master fails, off‑loading backup workloads to a slave, and running heavy analytical queries on replicas to avoid impacting online transactions.

Standard architecture consists of the following components:

Master (writes, generates binlog)

Slave/Replica (receives and replays binlog, serves reads)

Replication threads: master’s binlog‑dump thread, slave’s I/O thread and SQL thread

Application layer or middleware that routes writes to the master and reads to the replicas

In production environments a single master often has multiple slaves to further boost read capacity and improve backup and fail‑over capabilities.

Implementation steps (illustrated with configuration snippets):

Configure the master : enable binlog, set a unique server-id, and choose the binlog format.

mysqld
server-id=1
log-bin=mysql-bin
binlog-format=ROW

Configure each slave : assign a different server-id and enable relay logging.

mysqld
server-id=2
relay-log=mysql-relay-bin

Create a replication account on the master and grant the required privileges.

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

Initialize data on the slave : export the master’s current data (e.g., with mysqldump or a physical backup tool) and import it into the slave to ensure an identical starting point.

Configure the replication relationship : on the slave, specify the master’s host, replication user, and either binlog file/position or GTID mode, then start the replication threads.

CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=4;
START SLAVE;

Traditional setups require the master’s IP, binlog file name, and offset; GTID mode simplifies this configuration and makes fail‑over easier.

Verify replication status using the following command on the slave: SHOW SLAVE STATUS\G Check that the I/O and SQL threads are running and that the replication delay is acceptable.

By following these steps, users can build a reliable MySQL master‑slave replication topology that supports read scaling, high availability, and efficient backup strategies.