Multi-source replication in MariaDB 10.0

I have wanted multi-source replication in MySQL since 4.0, so I was delighted to see this feature appear in MariaDB 10.0. Let’s explore the feature a little. What is it? Multi-source replication allows a single slave server to replicate changes from multiple masters. Simple enough. I originally wanted this feature (well over 10 years ago now) to consolidate data from several different database servers onto a single server for reporting and backup purposes. So, let’s have an overview. I started three MariaDB servers: msr1, msr2, and msr3. I want msr1 to replicate changes from both msr2 and msr3, so I log into msr1 and configure replication (simplified for the sake of brevity):

CHANGE MASTER 'msr2' TO MASTER_HOST='10.0.0.152', MASTER_PORT=5002, MASTER_USER='replicant';
CHANGE MASTER 'msr3' TO MASTER_HOST='10.0.0.153', MASTER_PORT=5003, MASTER_USER='replicant';
START ALL SLAVES;

Then I confirm that everything appears to be working:

SHOW ALL SLAVES STATUSG
*************************** 1. row ***************************
Connection_name: msr2
Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: replicant
Master_Port: 5002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
*************************** 2. row ***************************
Connection_name: msr3
Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: replicant
Master_Port: 5003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

On msr1, the replication threads for both masters are visible in SHOW PROCESSLIST, and as one would expect, changes made on msr2 and msr3 are successfully replicated down to msr1. For simple use cases, multi-source replication in MariaDB 10.0 is almost too easy to configure. If you are adventurous and know exactly what you are doing, it’s worth noting that this works with ring/circular replication as well, so that you can configure each server to replicate from all of the others, potentially preventing the need for log_slave_updates. There are plenty of caveats to this approach, particularly for avoiding duplicate key violations and bad data when modifying the same table from different servers. Exploring multi-master scenarios will soon lead you to MariaDB 10.0’s Global Transaction ID functionality, which also works with multi-source replication and can be configured fairly easily. ex:

CHANGE MASTER 'msr2' TO MASTER_HOST='10.0.0.152', MASTER_PORT=5002, MASTER_USER='replicant', MASTER_USE_GTID=CURRENT_POS;

Exploring those kind of replication topologies would be an entirely different blog post, however. You can read more about these features in the MariaDB knowledge base: https://staging-mdb.com/kb/en/multi-source-replication/ https://staging-mdb.com/kb/en/global-transaction-id/#use-with-multi-source-r… This is just one of several great new features in the upcoming MariaDB 10.0, offering some very interesting possibilities. Check it out!