MariaDB’s multi-source replication lets a single replica pull data from multiple masters simultaneously, effectively acting as a central aggregation point.
Let’s see it in action. Imagine we have two masters, master1 and master2, and we want to replicate their data to a single replica, replica.
First, on master1:
-- Enable binlog and set server ID
SET GLOBAL log_bin = 'mysql-bin';
SET GLOBAL server_id = 1;
-- Create a replication user
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;
-- Get current binlog position
SHOW MASTER STATUS;
-- Note down File and Position
On master2, the commands are identical, just change server_id to 2.
Now, on replica, we’ll configure the connections to both masters.
-- Set server ID for the replica
SET GLOBAL server_id = 100;
-- Configure the first master connection
CHANGE MASTER TO
MASTER_HOST='master1_ip',
MASTER_USER='repl_user',
MASTER_PASSWORD='repl_password',
MASTER_PORT=3306,
MASTER_LOG_FILE='<file_from_master1_show_master_status>',
MASTER_LOG_POS=<pos_from_master1_show_master_status>,
MASTER_ID=1; -- This is the server_id of master1
-- Start replication from master1
START SLAVE;
-- Configure the second master connection
CHANGE MASTER TO
MASTER_HOST='master2_ip',
MASTER_USER='repl_user',
MASTER_PASSWORD='repl_password',
MASTER_PORT=3306,
MASTER_LOG_FILE='<file_from_master2_show_master_status>',
MASTER_LOG_POS=<pos_from_master2_show_master_status>,
MASTER_ID=2; -- This is the server_id of master2
-- Start replication from master2
START SLAVE;
The key here is MASTER_ID. This parameter is crucial because it distinguishes between the different master connections on the replica. Without it, MariaDB wouldn’t know which CHANGE MASTER TO statement applied to which logical replication stream. The replica internally manages separate threads and states for each master connection, identified by these MASTER_IDs.
You can verify the status of each replication stream independently:
SHOW SLAVE STATUS FOR CHANNEL 'master1'; -- Or whatever you named the channel, default is master by default for first connection, then MASTER_2, MASTER_3 etc.
SHOW SLAVE STATUS FOR CHANNEL 'master2';
The output for each will show its individual Master_Host, Master_Log_File, Master_Log_Pos, and importantly, Slave_IO_Running and Slave_SQL_Running status.
This setup allows a single replica to consolidate data. For instance, you could have an analytics replica pulling data from multiple transactional databases, or a disaster recovery replica that can failover to any of its masters. The replica applies changes from each master in the order they appear in its own binary log, which is a critical detail. If master1 writes a transaction at binlog position 100, and master2 writes one at position 50, and both are received by the replica, the replica will execute them in the order they are received and written to its own relay log. This is handled by the replica’s SQL thread, which reads from the relay log and applies events.
The most surprising thing about multi-source replication is how it handles potential conflicts or out-of-order events if you were to try and merge data from masters that are not designed to be replicated together. MariaDB’s multi-source replication doesn’t inherently "merge" data in a smart, conflict-resolving way. Instead, it faithfully replicates the transactions from each master as they arrive. If both masters modify the same row, the replica will apply the transaction that arrives later in its relay log. This means your replica’s data state will reflect the last transaction applied from any master for a given row. This is why it’s most effective when masters are either entirely separate (e.g., different databases) or when you have a clear understanding of your write patterns and potential overlaps. The MASTER_ID ensures that the replica knows which CHANGE MASTER TO parameters belong to which incoming stream, and SHOW SLAVE STATUS will show statuses per channel.
The next problem you’ll likely encounter is managing the replica’s own write conflicts and ensuring the correct transaction order when masters have overlapping data.