{"id":568,"date":"2026-07-22T05:02:41","date_gmt":"2026-07-22T05:02:41","guid":{"rendered":"https:\/\/virtualserversvps.com\/blog\/?p=568"},"modified":"2026-08-06T22:18:02","modified_gmt":"2026-08-06T22:18:02","slug":"mysql-multi-source-replication-vps-configuration","status":"publish","type":"post","link":"https:\/\/virtualserversvps.com\/blog\/mysql-multi-source-replication-vps-configuration\/","title":{"rendered":"MySQL Multi-Source Replication on a VPS: Consolidate Several Masters into One Replica"},"content":{"rendered":"<p class=\"wp-block-paragraph\">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 \u2014 giving you one place for backups, reporting, and read-only queries across all of your databases.<\/p>\n\n<p class=\"wp-block-paragraph\">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 <a href=\"https:\/\/virtualserversvps.com\/#providers\">compare VPS plans side by side<\/a> 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.<\/p>\n\n<h2 class=\"wp-block-heading\">How Multi-Source Replication Works<\/h2>\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n<h2 class=\"wp-block-heading\">Prerequisites<\/h2>\n\n<ul class=\"wp-block-list\"><li>MySQL 8.4 or MariaDB 11.4 installed on all nodes<\/li><li>Root or sudo access on every server<\/li><li>Private networking between the VPS instances, or a firewall rule allowing the replica to reach port 3306 on each master<\/li><li>A unique <code>server-id<\/code> on each host<\/li><\/ul>\n\n<h2 class=\"wp-block-heading\">Step 1: Configure Each Master<\/h2>\n\n<p class=\"wp-block-paragraph\">On every master, enable binary logging and set a unique <code>server-id<\/code>. Edit <code>\/etc\/mysql\/mysql.conf.d\/mysqld.cnf<\/code>:<\/p>\n\n<pre class=\"wp-block-code\"><code>[mysqld]\nserver-id = 1\nlog_bin = \/var\/log\/mysql\/mysql-bin.log\nbinlog_format = ROW<\/code><\/pre>\n\n<p class=\"wp-block-paragraph\">Give each master a different <code>server-id<\/code> (1, 2, 3, &#8230;). Restart MySQL, then create a replication user with privileges limited to replication:<\/p>\n\n<pre class=\"wp-block-code\"><code>CREATE USER 'repl'@'%' IDENTIFIED BY 'strong-password';\nGRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';\nFLUSH PRIVILEGES;<\/code><\/pre>\n\n<h2 class=\"wp-block-heading\">Step 2: Configure the Replica<\/h2>\n\n<p class=\"wp-block-paragraph\">On the replica, set a <code>server-id<\/code> that differs from every master, then define one channel per source. In MySQL 8.4 the command is:<\/p>\n\n<pre class=\"wp-block-code\"><code>CHANGE REPLICATION SOURCE TO\n  SOURCE_HOST='master1.example.com',\n  SOURCE_USER='repl',\n  SOURCE_PASSWORD='strong-password',\n  SOURCE_AUTO_POSITION=1\n  FOR CHANNEL 'master-1';<\/code><\/pre>\n\n<p class=\"wp-block-paragraph\">Repeat the command for each master with its own channel name (<code>master-2<\/code>, <code>master-3<\/code>, &#8230;), then start all channels:<\/p>\n\n<pre class=\"wp-block-code\"><code>START REPLICA FOR CHANNEL 'master-1';\nSTART REPLICA FOR CHANNEL 'master-2';\nSTART REPLICA FOR CHANNEL 'master-3';<\/code><\/pre>\n\n<h2 class=\"wp-block-heading\">Step 3: Verify and Monitor<\/h2>\n\n<p class=\"wp-block-paragraph\">Check each channel&#8217;s status and lag with:<\/p>\n\n<pre class=\"wp-block-code\"><code>SHOW REPLICA STATUS FOR CHANNEL 'master-1'\\G<\/code><\/pre>\n\n<p class=\"wp-block-paragraph\">Look at <code>Replica_IO_Running<\/code> and <code>Replica_SQL_Running<\/code> (both should be <code>Yes<\/code>) and at <code>Seconds_Behind_Source<\/code>. 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.<\/p>\n\n<h2 class=\"wp-block-heading\">Step 4: Avoid Conflicts<\/h2>\n\n<p class=\"wp-block-paragraph\">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&#8217;s data in its own database and restrict each channel with a per-channel filter:<\/p>\n\n<pre class=\"wp-block-code\"><code>CHANGE REPLICATION FILTER REPLICATE_DO_DB = (app1) FOR CHANNEL 'master-1';\nCHANGE REPLICATION FILTER REPLICATE_DO_DB = (app2) FOR CHANNEL 'master-2';<\/code><\/pre>\n\n<p class=\"wp-block-paragraph\">In MariaDB the equivalent options are <code>replicate-do-db<\/code> entries in the config file, one block per channel. Keeping databases disjoint is the simplest design and the easiest to reason about.<\/p>\n\n<h2 class=\"wp-block-heading\">Step 5: Seed Initial Data and Enable GTID<\/h2>\n\n<p class=\"wp-block-paragraph\">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:<\/p>\n\n<pre class=\"wp-block-code\"><code># on master-1\nmysqldump --single-transaction --all-databases --source-data=1 > master1.sql\n\n# on the replica\nmysql < master1.sql<\/code><\/pre>\n\n<p class=\"wp-block-paragraph\">With <code>SOURCE_AUTO_POSITION=1<\/code> (MySQL 8) or <code>MASTER_USE_GTID=current_pos<\/code> (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.<\/p>\n\n<h2 class=\"wp-block-heading\">Step 6: Secure Replication Traffic<\/h2>\n\n<p class=\"wp-block-paragraph\">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 <code>SOURCE_SSL=1<\/code> and <code>SOURCE_SSL_CA='...'<\/code> in the CHANGE statement, or tunnel port 3306 through SSH. Restrict the replication user's host to the replica's IP instead of <code>'%'<\/code> wherever possible.<\/p>\n\n<h2 class=\"wp-block-heading\">Step 7: Promoting a Replica<\/h2>\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n<p class=\"wp-block-paragraph\">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 <a href=\"https:\/\/virtualserversvps.com\/#providers\">full specs and pricing of top providers<\/a> in our comparison table to size the next VPS correctly.<\/p>","protected":false},"excerpt":{"rendered":"<p>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&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":1,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-568","post","type-post","status-publish","format-standard","hentry","category-vps-guides-tutorials"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v26.1 (Yoast SEO v26.1) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>MySQL Multi-Source Replication on a VPS: Consolidate Several Masters into One Replica - Virtual Servers VPS Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/virtualserversvps.com\/blog\/mysql-multi-source-replication-vps-configuration\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL Multi-Source Replication on a VPS: Consolidate Several Masters into One Replica\" \/>\n<meta property=\"og:description\" content=\"MySQL Multi-Source Replication on a VPS: Consolidate Several Masters into One Replica\" \/>\n<meta property=\"og:url\" content=\"https:\/\/virtualserversvps.com\/blog\/mysql-multi-source-replication-vps-configuration\/\" \/>\n<meta property=\"og:site_name\" content=\"Virtual Servers VPS Blog\" \/>\n<meta property=\"article:published_time\" content=\"2026-07-22T05:02:41+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-08-06T22:18:02+00:00\" \/>\n<meta name=\"author\" content=\"Virtual-Servers-Vps-Editor\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Virtual-Servers-Vps-Editor\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/mysql-multi-source-replication-vps-configuration\/\",\"url\":\"https:\/\/virtualserversvps.com\/blog\/mysql-multi-source-replication-vps-configuration\/\",\"name\":\"MySQL Multi-Source Replication on a VPS: Consolidate Several Masters into One Replica - Virtual Servers VPS Blog\",\"isPartOf\":{\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#website\"},\"datePublished\":\"2026-07-22T05:02:41+00:00\",\"dateModified\":\"2026-08-06T22:18:02+00:00\",\"author\":{\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/82a299a8284a66ff49f97c74684724a0\"},\"breadcrumb\":{\"@id\":\"https:\/\/virtualserversvps.com\/blog\/mysql-multi-source-replication-vps-configuration\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/virtualserversvps.com\/blog\/mysql-multi-source-replication-vps-configuration\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/mysql-multi-source-replication-vps-configuration\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/virtualserversvps.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL Multi-Source Replication on a VPS: Consolidate Several Masters into One Replica\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#website\",\"url\":\"https:\/\/virtualserversvps.com\/blog\/\",\"name\":\"Virtual Servers VPS Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/virtualserversvps.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/82a299a8284a66ff49f97c74684724a0\",\"name\":\"Virtual-Servers-Vps-Editor\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d820b15f1cd028e97610d9adf536df7be5cb6423869967037d468d5355fa003f?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d820b15f1cd028e97610d9adf536df7be5cb6423869967037d468d5355fa003f?s=96&d=mm&r=g\",\"caption\":\"Virtual-Servers-Vps-Editor\"},\"sameAs\":[\"https:\/\/virtualserversvps.com\/blog\"],\"url\":\"https:\/\/virtualserversvps.com\/blog\/author\/virtualserversvps\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"MySQL Multi-Source Replication on a VPS: Consolidate Several Masters into One Replica - Virtual Servers VPS Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/virtualserversvps.com\/blog\/mysql-multi-source-replication-vps-configuration\/","og_locale":"en_US","og_type":"article","og_title":"MySQL Multi-Source Replication on a VPS: Consolidate Several Masters into One Replica","og_description":"MySQL Multi-Source Replication on a VPS: Consolidate Several Masters into One Replica","og_url":"https:\/\/virtualserversvps.com\/blog\/mysql-multi-source-replication-vps-configuration\/","og_site_name":"Virtual Servers VPS Blog","article_published_time":"2026-07-22T05:02:41+00:00","article_modified_time":"2026-08-06T22:18:02+00:00","author":"Virtual-Servers-Vps-Editor","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Virtual-Servers-Vps-Editor","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/virtualserversvps.com\/blog\/mysql-multi-source-replication-vps-configuration\/","url":"https:\/\/virtualserversvps.com\/blog\/mysql-multi-source-replication-vps-configuration\/","name":"MySQL Multi-Source Replication on a VPS: Consolidate Several Masters into One Replica - Virtual Servers VPS Blog","isPartOf":{"@id":"https:\/\/virtualserversvps.com\/blog\/#website"},"datePublished":"2026-07-22T05:02:41+00:00","dateModified":"2026-08-06T22:18:02+00:00","author":{"@id":"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/82a299a8284a66ff49f97c74684724a0"},"breadcrumb":{"@id":"https:\/\/virtualserversvps.com\/blog\/mysql-multi-source-replication-vps-configuration\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/virtualserversvps.com\/blog\/mysql-multi-source-replication-vps-configuration\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/virtualserversvps.com\/blog\/mysql-multi-source-replication-vps-configuration\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/virtualserversvps.com\/blog\/"},{"@type":"ListItem","position":2,"name":"MySQL Multi-Source Replication on a VPS: Consolidate Several Masters into One Replica"}]},{"@type":"WebSite","@id":"https:\/\/virtualserversvps.com\/blog\/#website","url":"https:\/\/virtualserversvps.com\/blog\/","name":"Virtual Servers VPS Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/virtualserversvps.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/82a299a8284a66ff49f97c74684724a0","name":"Virtual-Servers-Vps-Editor","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d820b15f1cd028e97610d9adf536df7be5cb6423869967037d468d5355fa003f?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d820b15f1cd028e97610d9adf536df7be5cb6423869967037d468d5355fa003f?s=96&d=mm&r=g","caption":"Virtual-Servers-Vps-Editor"},"sameAs":["https:\/\/virtualserversvps.com\/blog"],"url":"https:\/\/virtualserversvps.com\/blog\/author\/virtualserversvps\/"}]}},"_links":{"self":[{"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/posts\/568","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/comments?post=568"}],"version-history":[{"count":5,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/posts\/568\/revisions"}],"predecessor-version":[{"id":812,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/posts\/568\/revisions\/812"}],"wp:attachment":[{"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/media?parent=568"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/categories?post=568"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/tags?post=568"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}