How MySQL Multi-Source Replication Works

This article explains how MySQL handles multi-source replication, a feature that allows a single replica server to connect to and receive data from multiple source servers simultaneously. We will explore the underlying architecture, the role of replication channels, how MySQL prevents data conflicts, and the primary use cases for this configuration.

The Architecture of Multi-Source Replication

In traditional MySQL replication, a replica node connects to a single source. Multi-source replication, introduced in MySQL 5.7, enables a single replica to aggregate data from multiple distinct sources.

To achieve this without mixing up data streams, MySQL uses Replication Channels. A replication channel represents an independent path from a specific source to the replica.

How Replication Channels Work

When you configure multi-source replication, MySQL creates a dedicated set of threads and files for each defined channel:

• Receiver Thread (I/O Thread): Each channel has its own receiver thread that connects to its designated source, reads the binary log events, and writes them to the replica’s relay log.
• Applier Thread (SQL Thread): Each channel has its own applier thread that reads the events from that channel’s specific relay log and applies them to the replica’s database.
• Relay Logs: MySQL maintains separate relay logs for each channel to prevent data from different sources from interleaving and causing corruption.

Configuration and Management

To set up multi-source replication, you must define distinct channel names. This is done by appending the FOR CHANNEL clause to standard replication commands.

For example, to configure connections to two different sources, you would execute:

CHANGE REPLICATION SOURCE TO 
    SOURCE_HOST='source1.example.com', 
    SOURCE_USER='repl_user', 
    SOURCE_PASSWORD='password' 
    FOR CHANNEL 'source_channel_one';

CHANGE REPLICATION SOURCE TO 
    SOURCE_HOST='source2.example.com', 
    SOURCE_USER='repl_user', 
    SOURCE_PASSWORD='password' 
    FOR CHANNEL 'source_channel_two';

Administrative commands like START REPLICA, STOP REPLICA, and SHOW REPLICA STATUS can be executed globally for all channels, or targeted to a specific channel using the FOR CHANNEL 'channel_name' syntax.

Conflict Resolution and Schema Design

MySQL multi-source replication does not include built-in conflict resolution mechanisms. If two sources attempt to modify the same row or insert duplicate primary keys on the replica, the replication process for that channel will halt with an error.

To prevent conflicts, you must design your database schema using one of the following strategies:

• Schema Separation: Ensure each source writes to a completely different database (schema) on the replica. This is the safest and most common approach.
• Disjoint Primary Keys: If sources write to the same tables, configure unique primary key generation. For example, use different auto-increment offsets and increments on each source, or use UUIDs.

Common Use Cases

Multi-source replication is highly effective for specific infrastructure needs:

1. Data Aggregation and Reporting: You can consolidate data from multiple regional or departmental databases onto a single reporting server for analytics and data warehousing.
2. Centralized Backups: Instead of backing up multiple servers individually, you can replicate them to a single replica and run backups from that single node, reducing the performance impact on production sources.
3. Merging Shards: If you have sharded your data across multiple MySQL databases, multi-source replication can merge those shards back into a single database system.