MySQL Multi-Source Replication on a VPS: Consolidate Several Masters into One Replica

A production database on a single VPS is a single point of failure: if the server dies, the application dies with it. Standard replication solves this with one master and one replica, but what if you operate several applications, each with its own MySQL instance on its own VPS? Multi-source replication lets a single replica pull changes from multiple master servers at the same time — giving you one place for backups, reporting, and read-only queries across all of your databases.

The replica does not need to be powerful, since it only applies changes and serves reads. A 2 GB VPS is usually enough for several low-traffic masters, and you can compare VPS plans side by side in our comparison table to pick one that fits your workload and budget. This guide uses MySQL 8.4 on Ubuntu 24.04; MariaDB follows the same flow with minor syntax differences, which are noted below.

How Multi-Source Replication Works

In standard replication the replica has a single I/O thread and applies events in one stream. Multi-source replication gives each source its own channel: a dedicated I/O thread and SQL thread per master, all writing to the same replica instance. Each channel keeps its own relay log, binary log position, and error state, so one failing master never stalls the others.

Prerequisites

• MySQL 8.4 or MariaDB 11.4 installed on all nodes
• Root or sudo access on every server
• Private networking between the VPS instances, or a firewall rule allowing the replica to reach port 3306 on each master
• A unique server-id on each host

Step 1: Configure Each Master

On every master, enable binary logging and set a unique server-id. Edit /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW

Give each master a different server-id (1, 2, 3, …). Restart MySQL, then create a replication user with privileges limited to replication:

CREATE USER 'repl'@'%' IDENTIFIED BY 'strong-password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

Step 2: Configure the Replica

On the replica, set a server-id that differs from every master, then define one channel per source. In MySQL 8.4 the command is:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='master1.example.com',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='strong-password',
  SOURCE_AUTO_POSITION=1
  FOR CHANNEL 'master-1';

Repeat the command for each master with its own channel name (master-2, master-3, …), then start all channels:

START REPLICA FOR CHANNEL 'master-1';
START REPLICA FOR CHANNEL 'master-2';
START REPLICA FOR CHANNEL 'master-3';

Step 3: Verify and Monitor

Check each channel’s status and lag with:

SHOW REPLICA STATUS FOR CHANNEL 'master-1'\G

Look at Replica_IO_Running and Replica_SQL_Running (both should be Yes) and at Seconds_Behind_Source. Add a cron job that greps these fields and emails you when a channel stops, because a silent replication failure is worse than no replica at all.

Step 4: Avoid Conflicts

If two masters write to the same tables, multi-source replication can hit duplicate-key errors. The clean way to avoid conflicts is to keep each source’s data in its own database and restrict each channel with a per-channel filter:

CHANGE REPLICATION FILTER REPLICATE_DO_DB = (app1) FOR CHANNEL 'master-1';
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (app2) FOR CHANNEL 'master-2';

In MariaDB the equivalent options are replicate-do-db entries in the config file, one block per channel. Keeping databases disjoint is the simplest design and the easiest to reason about.

Step 5: Seed Initial Data and Enable GTID

Channels only replicate changes made after they start, so existing data must be copied to the replica first. Dump each master and import it on the replica before starting the channel:

# on master-1
mysqldump --single-transaction --all-databases --source-data=1 > master1.sql

# on the replica
mysql < master1.sql

With SOURCE_AUTO_POSITION=1 (MySQL 8) or MASTER_USE_GTID=current_pos (MariaDB), the replica resumes from the exact transaction the dump was taken at, so no position bookkeeping is needed. Run the import during a quiet period; a large dump can stall replication start if it overlaps with heavy writes.

Step 6: Secure Replication Traffic

Replication streams contain every write to your databases, so never send them over the public internet unencrypted. The simplest fix is to run the replica on the same private network as the masters; most VPS providers offer a private interface that never touches the public internet. If private networking is unavailable, require TLS on each channel with SOURCE_SSL=1 and SOURCE_SSL_CA='...' in the CHANGE statement, or tunnel port 3306 through SSH. Restrict the replication user's host to the replica's IP instead of '%' wherever possible.

Step 7: Promoting a Replica

If a master fails, you can promote the replica to serve that master's workload: stop all channels, reset them, and repoint the application. Because a multi-source replica holds data from several masters, promote only the affected channel and re-provision the remaining channels from their own backups rather than resetting the whole replica.

Conclusion

Multi-source replication gives you a central replica for backups, reporting, and read scaling across all of your MySQL instances without buying a separate server per application. Monitor channel lag, keep filters strict, and test your promotion procedure before you need it. If you are scaling out and need more instances, check the full specs and pricing of top providers in our comparison table to size the next VPS correctly.