MySQL Master‑Slave Replication: Types, Use Cases, Principles, and Solutions

1. Replication Modes

One master, one slave

Master‑master replication

One master, multiple slaves – improves read performance because reads are from slaves

Multiple masters, one slave – supported from MySQL 5.7

Cascading replication

2. Uses and Conditions

2.1 MySQL Replication Uses

Real‑time disaster recovery for failover

Read/write separation to provide query services

Backup without affecting the primary workload

2.2 Prerequisites for Master‑Slave Deployment

Enable binlog on the master (set log-bin parameter)

Different server-id values for master and slave

Network connectivity from slave to master

3. Replication Principles

MySQL replication works by the master writing binary logs (binlog) and the slave reading them.

The slave creates two threads: an I/O thread and an SQL thread.

I/O thread requests the master's binlog and writes it to a relay log file.

The master runs a log‑dump thread to send binlog data to the slave’s I/O thread.

SQL thread reads the relay log, parses the events, and applies them so that data on master and slave stay consistent.

4. Problems and Solutions

Problems

If the master crashes, some data may be lost.

The slave has only one SQL thread; heavy write load on the master can cause replication lag.

Solutions

Semi‑synchronous replication to avoid data loss.

Parallel replication to reduce replication delay.

5. Semi‑Synchronous Replication

MySQL Semi‑Sync

Semi‑synchronous replication (available as a plugin from MySQL 5.5, requires separate installation) works as follows:

Ensures that after a transaction commits, its binlog is transmitted to at least one slave.

Does not guarantee that the slave has applied the transaction.

Introduces some performance overhead and longer response times.

Network issues or slave failure can block the master until timeout or slave recovery.

Asynchronous Replication Principle

Semi‑Synchronous Replication Principle

After the master writes the binlog, it must receive an acknowledgment from at least one slave before responding to the client.

Implemented as a plugin in MySQL 5.5.

Ensures the binlog reaches at least one slave but does not wait for the slave to apply it.

Performance may degrade and network problems can block the master.

6. Parallel Replication

MySQL Parallel Replication

Introduced in community version 5.6.

Parallel means the slave uses multiple threads to apply binlog events at the database level; within the same database changes remain sequential (transaction‑group based in 5.7).

Set the number of SQL threads, e.g., to 10.

set global slave_parallel_workers=10;

7. Other Topics

Partial Data Replication

Parameters added on the master:

binlog_do_db=db1
binlog_ignore_db=db1
binlog_ignore_db=db2

Or parameters added on the slave:

replicate_do_db=db1
replicate_ignore_db=db1
replicate_do_table=db1.t1
replicate_wild_do_table=db%.%
replicate_wild_ignore_table=db1.%

Cascading Replication (Common)

Topology: A → B → C

On server B add the parameter:

log_slave_updates

Server B records A's binlog into its own binlog.

Replication Monitoring

show slave status \G

Replication Error Handling

Common errors: 1062 (duplicate primary key), 1032 (record not found).

Solution: manual handling, e.g., skip the error:

set global sql_slave_skip_counter=1

MySQL master‑slave replication is the foundation for high availability and load balancing.

It is simple, flexible, and offers many deployment options for different business scenarios.

Continuous monitoring of replication status is essential to avoid impact from errors or lag.

Existing issues can be mitigated by enabling semi‑synchronous or parallel replication as needed.

Source: blog.csdn.net/darkangel1228/article/details/80003967