Unlocking MariaDB 10 Multi-Source Replication: Scenarios and Step-by-Step Setup

MariaDB 10 Multi-Source Replication: Business Scenarios and Usage

Official MySQL allows only one master per slave, but MariaDB 10 introduces multi-source replication, enabling a single slave to replicate from multiple masters, each providing different databases.

This feature is useful in OLAP or big-data platforms where data from many separate databases must be aggregated for complex queries. Instead of building application-level aggregation or using unreliable federated engines, you can replicate all required databases onto one slave and join them directly.

Other typical use cases include:

Verifying data consistency between two independent systems that exchange data via APIs (RESTful, SOAP) by joining the two databases on the slave.

Allowing DBAs to query data on a single server without connecting to multiple servers.

Centralizing backups on the multi-source slave.

Consolidating data extraction for big-data pipelines on one platform (with appropriate storage such as SSD or PCIe cards).

For detailed configuration, refer to the official MariaDB documentation.

Converting Traditional Replication to Multi-Source

stop slave ; show slave status to view stop position.

reset slave all;

change master 'conn_mall' to master_host='master ip', MASTER_PORT=3306, master_user='', master_password='', master_log_file='mysql-bin.000036', master_log_pos=189492612;

start slave 'conn_mall';

show all slaves status \G or show slave 'conn_mall' status \G

Adding a New Replication Source

Export from source: mysqldump --master-data=2

Import into slave.

change master 'conn_erp' to master_host='master ip', MASTER_PORT=3306, master_user='', master_password='', master_log_file='mysql-bin.000470', master_log_pos=215418341;

start slave 'conn_erp';

Command Differences When Using Multi-Source

reset slave → reset slave 'conn_erp' (specify connection name).

start slave → start slave 'connection_name' or start all slaves .

show slave status → show slave 'conn_mall' status or show all slaves status to view each connection.

To skip events: stop slave 'connection_name' , set @@default_master_connection='connection_name' , then set global sql_slave_skip_counter=1; , finally start slave 'connection_name' .

Replication Variables in Multi-Source Environments

Prefix variables with the connection name, e.g., main_connection.replicate_ignore_db=ignore_database. Without the prefix, the setting applies to all databases with the same name.