Master‑Slave MySQL Replication: Multi‑Source and Parallel Techniques Explained

Overview of MySQL Replication

Replication copies data from a primary (master) server to one or more secondary (slave) servers. MySQL supports several replication models, including classic master‑slave, semi‑synchronous, multi‑master, and master‑master. The core mechanism writes changes to the binary log on the master, ships the log to slaves, and replays the events to keep the slaves in sync.

Common Master‑Slave Topologies

Typical configurations include:

Single master with one or many slaves (1‑master‑N‑slaves).

Multi‑source replication where several masters send data to a single slave.

Chained replication where a slave can act as a master for downstream slaves.

Benefits of Master‑Slave Replication

Load distribution: Read‑write separation reduces I/O load on the master and shortens query response time.

Increased robustness: When the master fails, a slave can be promoted to maintain service continuity.

Backup convenience: Backups can be taken on slaves without affecting master performance.

Query analysis: Slaves can serve as OLAP nodes for reporting and analytics.

Disaster‑recovery: Remote slaves provide off‑site data protection.

Multi‑Source Replication (MySQL 5.7+)

From MySQL 5.7, multi‑source replication allows multiple masters to stream their binary logs to a single slave. This consolidates data for unified analysis and reduces hardware costs.

Data aggregation: Centralizes data from sharded databases for combined reporting.

Cost savings: Fewer physical backup servers are needed compared with separate 1‑master‑1‑slave setups.

Centralized backup: A single server can back up all incoming data.

Remote DR: The slave can be placed in a distant location for disaster‑recovery.

Basic 1‑Master‑1‑Slave Setup

The replication process involves the master’s binary log, the slave’s I/O thread (which writes to the relay log), and the SQL thread (which replays events).

On the slave you can see two essential threads:

I/O thread – Waiting for master to send event SQL (Coordinator) thread – Slave has read all relay log; waiting for more updates When parallel replication is enabled, additional worker threads appear:

Worker thread –

Waiting for an event from Coordinator

Configuring Multi‑Source Replication

stop slave;

SET GLOBAL master_info_repository = 'TABLE';

SET GLOBAL relay_log_info_repository = 'TABLE';

change master to master_host='192.168.5.160', master_user='slave1', master_password='gaoqiang' for channel 'master1';

change master to master_host='192.168.5.163', master_user='slave1', master_password='gaoqiang' for channel 'master2';

start slave;

The diagram below shows two masters (music and habit) feeding a single slave, enabling unified user‑behavior analysis and a single mysqldump --all-databases backup.

Parallel Replication

Parallel replication splits transaction replay across multiple worker threads, reducing latency. The coordinator distributes work to workers; each worker replays a subset of transactions that share the same last_committed value (logical‑clock mode).

Enabling parallel replication uses the same commands for both single‑master and multi‑source setups:

stop slave;

SET GLOBAL SLAVE_PARALLEL_TYPE='LOGICAL_CLOCK';

SET GLOBAL SLAVE_PARALLEL_WORKERS=30;

start slave;

Verification with show processlist reveals one coordinator thread plus the configured number of workers (e.g., 30 workers → 60+ threads total).

Performance Testing

Sysbench was used to generate 20,000 mixed read/write operations across two masters (each with 5 tables, 200k rows per table) and a single slave. The test ran 100 concurrent connections per database.

Key metrics collected:

CPU utilization – parallel replication (30 workers) kept CPU around 36% while completing work faster than single‑threaded execution.

Disk write throughput – remained stable; the 30‑worker curve finished earliest.

Memory usage – showed minimal difference between parallel and non‑parallel runs.

Overall, enabling parallel replication for multi‑source setups significantly reduced replication lag and improved resource utilization.

Conclusion

MySQL multi‑source replication, combined with parallel execution, offers a cost‑effective, flexible, and high‑performance solution for consolidating data from multiple masters, simplifying backup strategies, and accelerating data synchronization.