MySQL: MySQL Master-Slave Replication and High-Availability…

Last updated: 2026-08-26

Master-slave replication is the foundation of MySQL high availability—both read-write separation and failover rely on it.

This lesson explains the principles and configuration of master-slave replication.

1. What You'll Learn

• Principles of Master-Slave Replication
• Configure master-slave replication
• Read-write separation
• Semi-synchronous replication
• MySQL Group Replication

2. A True Story

(1) Pain Point: A single server cannot handle the read and write load

An e-commerce platform has only one database server, so during major sales events, all read and write requests are concentrated on a single machine. Order queries slow down, write operations get queued, and the CPU reaches full capacity—the database becomes the bottleneck for the entire system. What’s even more dangerous is that if this machine goes down, the entire business comes to a complete halt.

(2) A Solution Using Master-Slave Replication and Read-Write Separation

Deploy master-slave replication: Writes go to the master database, and reads go to the slave database. If the master database goes down, the slave database is promoted to master, ensuring business continuity.

3. The Principle of Replication

graph LR
    A[Master<br/>Primary Database] -->|binlog| B[Slave IO Thread]
    B -->|relay log| C[Slave SQL Thread]
    C -->|Replay| D[Slave Data<br/>Data from the database]

graph LR
    A[Master<br/>Primary Database] -->|binlog| B[Slave IO Thread]
    B -->|relay log| C[Slave SQL Thread]
    C -->|Replay| D[Slave Data<br/>Data from the database]

4. Configuring Master-Slave Replication

▶ Example: Master Configuration

# my.cnf
[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW

-- Create a replication user
CREATE USER 'repl'@'%' IDENTIFIED BY 'ReplPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- View master status
SHOW MASTER STATUS;

Output:

Configuration saved. Restart the service to apply changes.

▶ Example: Slave Configuration

# my.cnf
[mysqld]
server-id = 2
relay_log = relay-bin

-- Configure master-slave relationship
CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='repl',
    MASTER_PASSWORD='ReplPass123!',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

-- Start the slave database
START SLAVE;

-- Check slave database status
SHOW SLAVE STATUS\G

Output:

Output displayed

5. Separation of Read and Write Operations

graph TB
    A[Applications] -->|Write| B[Master]
    A -->|Read| C[Slave 1]
    A -->|Read| D[Slave 2]
    B -->|Replicate| C
    B -->|Replicate| D

graph TB
    A[Applications] -->|Write| B[Master]
    A -->|Read| C[Slave 1]
    A -->|Read| D[Slave 2]
    B -->|Replicate| C
    B -->|Replicate| D

6. Semi-synchronous Replication

▶ Example: Enabling Semi-Synchronous Replication

-- On the master: install and enable the semi-sync plugin
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 10000;  -- 10 seconds

-- On the slave: install and enable the semi-sync plugin
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

Output:

Output displayed

7. MySQL Group Replication

-- Install the Group Replication plugin
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

-- Start Group Replication
SET GLOBAL group_replication_bootstrap_group = ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group = OFF;

❓ FAQ

📖 Summary

• Master-slave replication achieves data synchronization via the binlog → relay log
• Asynchronous replication is the default method; semi-synchronous replication is more secure.
• Read-Write Separation distributes the load, with the primary server handling writes and the secondary server handling reads
• Group Replication implements a highly available cluster

📝 Exercises

1. Basic Question (Difficulty: ⭐): Configure a master-slave replication environment.

2. Advanced Problem (Difficulty ⭐⭐): Verify master-slave data synchronization.

3. Challenge (Difficulty: ⭐⭐⭐): Simulate a primary database failure and perform a manual failover.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us