How to Build a MySQL Master‑Slave Cluster: Step‑by‑Step Guide

1. Overview This article introduces various MySQL service cluster deployment methods, starting from the simplest master‑slave setup and expanding to more complex solutions.

2. MySQL simplest master‑slave scheme and principle It uses MySQL 5.6 (applicable to 5.7/8.0) and explains the built‑in MySQL replication (MySQL‑Replication) mechanism.

2‑1. Basic replication principle Replication involves a Master (write) and a Slave (read). The Master writes binary logs; the Slave receives them, stores them in a relay log, and applies changes to its tables. Configuration parameters such as sync_binlog, binlog_format, and sync_relay_log control the process.

2‑2. One‑master‑multiple‑slave deployment The article shows a one‑master‑one‑slave example that can be extended to many slaves, using IPs 192.168.61.140 (Master) and 192.168.61.141 (Slave).

2‑2‑1. Configure Master Edit my.cnf to enable binary logging and set parameters such as log_bin, sync_binlog=1, binlog_format=mixed, server_id=140, etc. After restarting MySQL, create a replication user:

grant replication slave on *.* to [email protected] identified by '123456';
show master status;

These commands display the current binary log file and position.

2‑2‑2. Configure Slave Edit the slave’s my.cnf to enable logging ( log-bin) and set sync_relay_log=1 and a unique server_id. Then point the slave to the master:

change master to master_host='192.168.61.140',
master_user='root',master_password='123456',
master_log_file='kp2-bin.000002',master_log_pos=120;
start slave;
show slave status;

After these steps the one‑master‑one‑slave cluster is operational.

2‑3. Recommendations for one‑master‑multiple‑slave This architecture suits read‑heavy workloads (e‑commerce, logistics, CRM). The master must be powerful, preferably on SSDs with RAID‑10. A standby slave can act as a backup master, using additional sync tools like Rsync or DRBD. Complex queries should run on dedicated slave nodes.

3. Exposed issues The article notes two main problems: upper‑layer applications must know which node to connect to, and high‑availability is limited because only one master exists; its failure brings the whole cluster down.