Using MySQL Multi‑Source Replication to Aggregate Data from Multiple Servers

When a client asks how to consolidate data from multiple MySQL servers onto a single instance, the recommended solution is MySQL's multi‑source replication.

Unlike the common single‑master‑multiple‑slaves or cascading setups, multi‑source replication involves one replica pulling data from several masters, which is useful in three main scenarios:

1. Backing up data from multiple servers. This corresponds to vertical partitioning, where distinct business domains (A, B, C, D) reside on separate servers and need to be aggregated for backup without the performance penalties of cross‑database joins.

2. Aggregating sharded data from front‑end servers. This aligns with horizontal partitioning, such as yearly‑based shards that must be combined for reporting, often using middleware like DBLE to handle the sharding transparently.

3. Consolidating and merging data from multiple servers. Similar to the first case but requiring data merging, making the task more complex yet still suitable for multi‑source replication.

Implementation example: four servers each hold a fragment of table A (A1‑A4) with different column ranges. An EVENT can periodically insert merged rows into the central table A using the following SQL:

insert ignore into A select A1.ID,F1,F2,...,F100 \
from A1 natural join A2 natural join A3 natural join A4;

This approach avoids the need to run multiple MySQL instances on the aggregation server and eliminates costly cross‑instance joins.

Overall, MySQL multi‑source replication provides a flexible method for data backup, sharding aggregation, and data merging across multiple servers.