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

1. Overview

From this article we will introduce various MySQL service cluster deployment methods, starting with the simplest master‑slave scheme, then extending to more complex cluster solutions that address its shortcomings. We will select typical cluster architectures for discussion.

2. MySQL Simplest Master‑Slave Scheme and Working Principle

We explain using MySQL 5.6, the version most widely used in production; later versions (5.7, 8.0) have improvements but the core concepts remain the same, even for MariaDB. MySQL’s built‑in binary log replication (MySQL Replication) is the foundation.

2‑1. Basic Working Principle of MySQL Replication

Replication involves two roles: Master and Slave. The Master writes data to binary logs; the Slave receives and replays them, enabling read‑write separation. The process relies on binary‑log synchronization.

When a Slave starts, it connects to the Master. Upon binary‑log changes, the Master pushes updates to the Slave, which writes them to a relay log, then applies them to its tables, updates its position, and prepares for the next cycle. Various parameters (sync_binlog, binlog_format, sync_relay_log, etc.) can be tuned.

2‑2. Setting Up a One‑Master‑One‑Slave Cluster

After covering replication basics, we quickly build a cluster with one Master and one Slave, which can be expanded to one‑master‑multiple‑slave configurations.

We use MySQL 5.6 on Linux (CentOS). Master IP: 192.168.61.140, Slave IP: 192.168.61.141.

2‑2‑1. Configuring the Master

Edit my.cnf to enable binary logging and set replication parameters:

# Enable binary log
log_bin
# Parameters explained later
sync_binlog=1
binlog_format=mixed
binlog-do-db=qiang
binlog_checksum=CRC32
binlog_cache_size=2M
max_binlog_cache_size=1G
max_binlog_size=100M
server_id=140

These settings control log generation, storage, and transmission. After restarting MySQL, create a replication user:

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

Check master status with show master status; to obtain File and Position values.

2‑2‑2. Configuring the Slave

Edit the Slave’s my.cnf to enable relay logging:

# Enable relay log
log-bin
sync_relay_log=1
server_id=141

Specify the Master connection details:

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;

Verify synchronization with show slave status;. Once the Slave I/O and SQL threads are running, the one‑master‑one‑slave cluster is operational.

2‑3. Recommendations for One‑Master‑Multiple‑Slave Deployments

This architecture suits read‑intensive workloads such as e‑commerce catalogs, logistics tracking, telecom CRM, and monitoring logs. The Master must be powerful enough to handle all writes, typically using SSDs and RAID‑10. A dedicated standby Slave can serve as a backup Master, possibly using Rsync or DRBD for additional safety.

For high availability, tools like Keepalived or Heartbeat can automate Master failover.

Complex analytical queries should run on separate Slave nodes to keep transactional workloads fast. Ensure proper indexing to avoid full‑table scans.

3. Issues Exposed by This Scheme

Challenges include the need for applications to know individual node addresses, increasing maintenance complexity, and the single‑Master single point of failure, which requires additional strategies for true high availability.