Mastering MySQL Replication: Types, Setup, and Performance Tips

Replication Modes

One master – one slave

Master‑master replication

One master – many slaves (improves read performance by reading from slaves)

Multiple masters – one slave (supported from MySQL 5.7)

Cascading replication

Uses and Prerequisites

MySQL Replication Uses

Real‑time disaster recovery for failover

Read/write separation to provide query services

Backup without affecting production workloads

Deployment Preconditions

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

Master and slave must have different server-id Slave must be able to connect to the master

Replication Principles

MySQL replication works as follows:

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

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

The master runs a log‑dump thread that sends binlog data to the slave's I/O thread.

The SQL thread reads the relay log, parses the events, and executes them so that the slave’s data stays consistent with the master.

Problems and Solutions

Typical Issues

Data loss may occur if the master crashes.

Only one SQL thread on the slave can cause replication lag under heavy write load.

Mitigation Strategies

Semi‑synchronous replication to prevent data loss.

Parallel replication to reduce replication delay.

Semi‑Synchronous Replication

Overview

Integrated as a plugin in MySQL 5.5; requires separate installation.

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 a performance penalty and longer response times.

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

Asynchronous Replication Principle (illustration)

Semi‑Sync Mechanism

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

Same plugin requirements as above.

Ensures transmission to at least one slave but not full application.

Performance impact is noticeable.

Network or slave failures can stall the master until timeout or recovery.

Parallel Replication

Details

Introduced in community edition 5.6.

Parallelism means the slave applies binlog entries using multiple threads; changes within the same database remain serialized (transaction‑group based in 5.7).

Configure the number of SQL threads, e.g., set to 10.

set global slave_parallel_workers=10;

Other Topics

Partial Data Replication

Master configuration:

binlog_do_db=db1
binlog_ignore_db=db1
binlog_ignore_db=db2

Slave configuration:

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: log_slave_updates Server B records A's binlog into its own binlog.

Replication Monitoring

show slave status \G

Error Handling

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

Resolution: Manual intervention.

Skip replication error: set global sql_slave_skip_counter=1

MySQL replication is the foundation for high availability and load‑balanced performance.

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

Continuous monitoring of replication status is essential; errors or lag can impact the system.

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