How to Build a Reliable MySQL Master‑Slave Replication Cluster

Drawbacks of a single MySQL server

Single point of failure – if the server crashes the whole application stops.

CPU, memory and I/O resources cannot keep up with growing read/write traffic.

Peak connection counts often exceed the server's max_connections limit.

Cluster‑based solution

Deploy additional MySQL instances and form a replication cluster.

All nodes must contain identical data so that any node can take over the workload.

When one node fails the remaining nodes continue serving requests without manual intervention.

MySQL master‑slave replication architecture

Master writes all changes to its binary log (binlog). The binlog must be enabled (log_bin in my.cnf). If it was disabled, enable it and restart MySQL – a restart may affect production.

Slave I/O thread connects to the master and streams the binlog. The I/O thread creates a client connection to the master and reads events via the binlog‑dump thread, storing them locally as a Relay Log.

Slave SQL thread replays the Relay Log. The SQL thread executes the events on the slave and can optionally write them to the slave’s own binlog (log_slave_updates).

MySQL supports two replication positioning methods:

Binary‑log‑position based replication.

GTID‑based replication (recommended for MySQL ≥ 5.7).

Configuration steps

Configure the master server. Add the following parameters to my.cnf and restart MySQL:

[mysqld]
server_id=1
log_bin=mysql-bin
binlog_format=row
expire_logs_days=7

Create a replication user on the master.

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

Backup the master and initialise the slave. Use the same MySQL version on both hosts and perform a logical full backup:

mysqldump --master-data=2 -uroot -p -A \
  --single-transaction -R --triggers > all.sql

Copy all.sql to the slave (e.g., scp all.sql root@slave_ip:/root ) and restore it: mysql -uroot -p < all.sql Set up binlog‑position based replication. On the slave run:

CHANGE MASTER TO
  MASTER_HOST='192.168.3.100',
  MASTER_USER='repl',
  MASTER_PASSWORD='StrongPassword!',
  MASTER_LOG_FILE='mysql-bin.000017',
  MASTER_LOG_POS=663;

Optional: enable GTID‑based replication. Add the following to my.cnf on both master and slave and restart:

gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON

Then on the slave execute:

CHANGE MASTER TO
  MASTER_HOST='192.168.3.100',
  MASTER_USER='repl',
  MASTER_PASSWORD='StrongPassword!',
  MASTER_AUTO_POSITION=1;

GTID replication limitations

Statements like CREATE TABLE ... SELECT are prohibited; create the table first, then insert data.

Temporary tables cannot be created inside a transaction.

A single statement cannot modify both transactional and non‑transactional tables.

Demo of master‑slave replication

Configure the master as described above and verify that log_bin is active.

Configure the slave (set server_id to a unique value, enable relay_log, etc.) and restart MySQL.

Create the replication account on the master (step 2).

Perform a full logical backup on the master and copy it to the slave: scp all.sql root@slave_ip:/root Restore the backup on the slave: mysql -uroot -p < all.sql Configure the replication link on the slave (choose either method). Example for binlog‑position:

CHANGE MASTER TO
  MASTER_HOST='192.168.3.100',
  MASTER_USER='repl',
  MASTER_PASSWORD='StrongPassword!',
  MASTER_LOG_FILE='mysql-bin.000017',
  MASTER_LOG_POS=663;

Start replication and verify status:

START SLAVE;
SHOW SLAVE STATUS \G

Key fields to check are Slave_IO_Running and Slave_SQL_Running – both should be Yes .

Remaining drawbacks of asynchronous master‑slave replication

Replication provides a read‑only copy but is asynchronous, so a small lag (seconds to minutes) can exist between master and slave. If the master fails, a manual failover is required; this introduces downtime and does not satisfy strict high‑availability requirements.