Master MySQL Replication: Step‑by‑Step Guide to Master‑Slave Setup

Introduction

Ensuring 24‑hour service availability requires both backup and redundancy. Backup restores data after a crash, while redundancy keeps the application running when one or more nodes fail. MySQL replication provides the redundancy by copying all data changes from a master to one or more slaves.

What Is Replication?

Replication means the master (primary) server records every data change and the slave (secondary) server applies those changes to stay consistent. It is primarily used to create a reliable copy of the master.

Key Characteristics

Data distribution across different data centers.

Load balancing – queries can be offloaded to slaves, reducing master load.

Backup – replication itself acts as a continuous backup of the master.

High availability and failover – if the master crashes, a slave can take over.

Asynchronous vs. Synchronous Replication

MySQL replication is fundamentally asynchronous: the transaction commits on the master first, then the changes are sent to the slave, which may lag behind. Asynchronous replication is faster, more scalable, and uses fewer resources.

Synchronous replication forces the master to wait until all slaves have written the changes before committing, which greatly reduces write speed and adds extra network traffic.

Master‑Slave Replication Setup

A simple one‑master, one‑slave topology is demonstrated. The master (Vm11.xxxx.com, IP 192.168.1.231, server‑id 1) and the slave (Vm22.xxxx.com, IP 192.168.1.232, server‑id 2) are configured to use binary logs.

Step 1 – Configure the Master

Add the following lines to /etc/my.cnf and restart MySQL:

log-bin=master-bin
log-bin-index=master-bin.index
server-id=1

Create a replication user on the master:

mysql> CREATE USER 'repl_user'@'%' IDENTIFIED BY 'mysql'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';

Step 2 – Configure the Slave

On the slave, issue the CHANGE MASTER command (example shown in a blockquote):

CHANGE MASTER TO MASTER_HOST='vm11.qq.com', MASTER_USER='repl_user', MASTER_PASSWORD='mysql', MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=756;

Then start the replication: START SLAVE; If the slave reports Slave_SQL_Running: NO because a database (e.g., test) does not exist, the error 1008 occurs and must be resolved before replication can continue.

Binary Log Overview

The binary log records all changes to tables. Examples of log entries are shown in the original images (omitted here). To view events in a specific binlog file:

SHOW BINLOG EVENTS IN 'master-bin.000002' \G;

Replication Workflow

Replication consists of three steps:

Master writes changes to the binary log.

Slave copies those binary log events to its relay log.

Slave replays the relay log events, applying the changes to its own data.

Diagram omitted.

Statement‑Based (Logical) Replication

Executes the same SQL statements on the slave. It works well for most cases but cannot reliably replicate certain statements such as those using non‑deterministic functions, triggers, or stored procedures. It also requires serial execution, increasing lock contention.

Row‑Based Replication

Copies the actual row changes. It handles all statements, including stored procedures and triggers, but can generate a large volume of events and increase network traffic because each row change is logged individually.

Recommended Configuration

Key settings for the master: sync_binlog=1 – ensures the binary log is flushed to disk before transaction commit.

InnoDB settings: innodb_flush_logs_at_trx_commit=1 (safest) or 2 / 0 for performance trade‑offs. innodb_support_xa=1 – guarantees transaction consistency between binlog and redo log. innodb_safe_binlog – legacy equivalent of innodb_support_xa. log-bin=/data/binlog/master-bin – explicit path to avoid naming issues.

Recommended settings for the slave: relay_log=/data/relaylog/relay-bin – defines a stable relay log location. skip_slave_start – prevents automatic start after a crash, avoiding further corruption. read_only – restricts non‑privileged users from modifying data.

Replication Lag

Two types of lag exist: temporary lag caused by long‑running queries, and persistent lag indicating deeper issues. Common mitigations include adjusting innodb_flush_log_at_trx_commit to 2, disabling binary logging on the slave, or setting innodb_locks_unsafe_for_binlog=1. These improve performance at the cost of durability.

Optimizing Expensive Operations on the Master

Heavy operations performed on the master are duplicated on the slave. Offloading such work to the slave (e.g., large aggregations) and then loading results back to the master can reduce load on the primary.

Parallel Writes on the Slave

By default, slaves write serially. To achieve parallelism, one can separate write categories, disable binary logging for archival data, and execute writes independently on master and slave, improving OLTP throughput.

Semi‑Synchronous Replication and Heartbeat

Semi‑synchronous replication adds a delay: the master must ensure the binary log reaches at least one slave before confirming the transaction to the client. This improves data safety but adds latency.

The replication heartbeat monitors the connection between master and slave, detecting silent disconnections and triggering resynchronization when the link is restored.