{"id":2608,"date":"2026-09-16T12:22:42","date_gmt":"2026-09-16T12:22:42","guid":{"rendered":"https:\/\/ukspeed.co.uk\/blog\/mysql-replication-uk-vps-master-replica-high-availability-2026\/"},"modified":"2026-09-16T12:37:49","modified_gmt":"2026-09-16T12:37:49","slug":"mysql-replication-uk-vps-master-replica-high-availability-2026","status":"publish","type":"post","link":"https:\/\/ukspeed.co.uk\/blog\/mysql-replication-uk-vps-master-replica-high-availability-2026\/","title":{"rendered":"MySQL Replication on UK VPS: Master\u2013Replica High Availability 2026"},"content":{"rendered":"\n<p>MySQL replication is one of the most dependable ways to add high availability and read scaling to a production database without rewriting your application, and this guide walks UK developers and sysadmins through building a master\u2013replica setup across two UK VPS servers. Whether you run a busy WooCommerce store, a SaaS backend, or an agency full of client sites, a live second copy of your database means you can survive a server failure, spread read traffic, and back up without touching the primary. Below we cover what replication is, why it matters, the trade-offs between async and semi-synchronous modes, and the exact steps to configure, verify, and promote a replica on failover.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">What Is MySQL Replication?<\/h2>\n\n\n\n<p>MySQL replication is the process by which a primary server (historically called the &#8220;master&#8221;) records every change to its data in a binary log, and one or more replica servers (historically &#8220;slaves&#8221;) connect to that primary, read the log, and apply the same changes to their own copy. The result is a second database that stays continuously in sync with the first. The same mechanism applies to MariaDB, which shares most of the syntax, with only minor differences we flag along the way.<\/p>\n\n\n\n<p>Modern documentation uses the terms primary and replica, while older tutorials and some configuration variables still use master and slave for the same roles; throughout this guide we treat master-replica and primary-replica as interchangeable. If you are still choosing an engine, our comparison of <a href=\"https:\/\/ukspeed.co.uk\/blog\/mariadb-vs-mysql-vs-postgresql-for-wordpress-in-2026-which-database-wins-on-uk-hosting\/\">MariaDB vs MySQL vs PostgreSQL<\/a> is a good start, and PostgreSQL users can follow our sister guide on <a href=\"https:\/\/ukspeed.co.uk\/blog\/self-host-postgresql-uk-vps-production-setup-tuning-2026\/\">self-hosting PostgreSQL on a UK VPS<\/a>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Why Use Master\u2013Replica Replication<\/h2>\n\n\n\n<p>A single database server is a single point of failure. Master-replica replication addresses that in several practical ways:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>High availability:<\/strong> if the primary fails, you can promote a replica to take over, keeping downtime to minutes rather than hours.<\/li><li><strong>Read scaling:<\/strong> point read-heavy queries (SELECTs) at one or more replicas so the primary is free to handle writes. This is invaluable for reporting dashboards and content-heavy stores.<\/li><li><strong>Off-primary backups:<\/strong> run mysqldump or a snapshot against a replica so your backup window never slows the live site.<\/li><li><strong>Geo-distributed reads:<\/strong> place a replica closer to a specific audience to cut read latency.<\/li><\/ul>\n\n\n\n<p>Be realistic, though: standard asynchronous replication does <em>not<\/em> give you automatic failover on its own. Promoting a replica requires orchestration, whether a health-checking script or tools such as Orchestrator, ProxySQL, or keepalived, and replication can lag under heavy write load, so an app that reads its own writes needs care. For a real-world example, see how read replicas fit into our guide to <a href=\"https:\/\/ukspeed.co.uk\/blog\/scale-woocommerce-uk-vps-black-friday-redis-cdn-autoscaling-2026\/\">scaling WooCommerce on a UK VPS<\/a>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">What You Need for MySQL Replication<\/h3>\n\n\n\n<ul class=\"wp-block-list\"><li>Two UK VPS servers running the same major version of MySQL 8.0 (or the same MariaDB release). UK Speed&#8217;s UK VPS plans work well as paired database nodes.<\/li><li>A private network or VPN link between them so replication traffic never crosses the public internet.<\/li><li>Enough disk for the full dataset on both nodes \u2014 fast storage matters, which is why <a href=\"https:\/\/ukspeed.co.uk\/blog\/enterprise-nvme-vs-consumer-nvme-uk-hosting-2026\/\">enterprise NVMe<\/a> pays off for busy databases.<\/li><li>Root or sudo access, and a hardened SSH setup on both boxes.<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Async vs Semi-Synchronous Replication<\/h2>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/ukspeed.co.uk\/blog\/wp-content\/uploads\/2026\/09\/my1-async.svg\" alt=\"MySQL replication async vs semi-synchronous compared on latency and data-loss risk\" class=\"wp-image-2609\" \/><figcaption class=\"wp-element-caption\">Async replication is fastest but can lose the last transactions on failover; semi-sync trades a little latency for safety.<\/figcaption><\/figure>\n\n\n\n<p>MySQL supports different consistency guarantees. With <strong>asynchronous<\/strong> replication (the default), the primary commits a transaction and moves on without waiting for any replica to receive it. This is fast, but if the primary dies before the last few transactions reach a replica, those writes are lost. With <strong>semi-synchronous<\/strong> replication, the primary waits until at least one replica acknowledges receipt of the transaction before returning success to the client, greatly reducing the risk of data loss at the cost of a little write latency.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>Aspect<\/th><th>Asynchronous<\/th><th>Semi-Synchronous<\/th><\/tr><\/thead><tbody><tr><td>Data safety on failover<\/td><td>May lose last un-replicated transactions<\/td><td>At least one replica has each committed write<\/td><\/tr><tr><td>Write latency<\/td><td>Lowest<\/td><td>Slightly higher (waits for one ACK)<\/td><\/tr><tr><td>Complexity<\/td><td>Simplest, default<\/td><td>Requires plugin + tuning<\/td><\/tr><tr><td>Best for<\/td><td>Read scaling, backups, tolerant workloads<\/td><td>Financial or order data where loss is costly<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>For the highest consistency, MySQL also offers Group Replication (the foundation of InnoDB Cluster), which provides synchronous, multi-primary high availability with built-in conflict handling. It is more complex to operate, so for most UK production apps a well-monitored master-replica pair with GTID is the pragmatic sweet spot.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">How to Configure the Primary Server<\/h2>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/ukspeed.co.uk\/blog\/wp-content\/uploads\/2026\/09\/my2-topology.svg\" alt=\"MySQL replication master-replica topology: primary binary log replicated to a read replica on a UK VPS\" class=\"wp-image-2611\" \/><figcaption class=\"wp-element-caption\">The primary logs every change to its binary log; the replica applies it and can serve read queries &mdash; over a private network.<\/figcaption><\/figure>\n\n\n\n<p>On the primary, edit your MySQL configuration (usually <code>\/etc\/mysql\/mysql.conf.d\/mysqld.cnf<\/code> or <code>\/etc\/my.cnf<\/code>). Give the server a unique <code>server-id<\/code>, enable the binary log, and turn on GTID so failover and re-pointing replicas is far simpler. Bind MySQL to the private network address rather than the public one.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>[mysqld]\nserver-id            = 1\nlog_bin              = \/var\/log\/mysql\/mysql-bin.log\nbinlog_format        = ROW\ngtid_mode            = ON\nenforce_gtid_consistency = ON\nbind-address         = 10.0.0.1   # private network IP<\/code><\/pre>\n\n\n\n<p>Restart MySQL, then create a dedicated replication user restricted to the replica&#8217;s private IP and grant it only the privilege it needs:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE USER 'repl'@'10.0.0.2' IDENTIFIED WITH caching_sha2_password BY 'a-strong-password';\nGRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.2';\nFLUSH PRIVILEGES;<\/code><\/pre>\n\n\n\n<p>MariaDB uses <code>gtid_strict_mode<\/code> and slightly different GTID syntax, so check its manual if that is your engine. For the authoritative reference on every variable and edge case, consult <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/replication.html\" rel=\"noopener\" target=\"_blank\">the official MySQL replication documentation<\/a>. Finally, make sure port 3306 is firewalled to the private network only \u2014 see our steps to <a href=\"https:\/\/ukspeed.co.uk\/blog\/how-to-harden-ssh-on-a-linux-vps-10-security-steps-beyond-just-changing-the-default-port\/\">harden SSH on a Linux VPS<\/a> for the wider security posture.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">How to Set Up the Replica<\/h2>\n\n\n\n<p>On the replica, give it its own unique <code>server-id<\/code> (for example <code>server-id = 2<\/code>) and enable the same GTID settings, then restart MySQL. Next, take a consistent snapshot of the primary and load it onto the replica. For most datasets a logical dump works well:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># On the primary (or any client with access):\nmysqldump --single-transaction --set-gtid-purged=ON \n  --all-databases --triggers --routines --events \n  -u root -p &gt; primary-snapshot.sql\n\n# Copy to the replica and import:\nmysql -u root -p &lt; primary-snapshot.sql<\/code><\/pre>\n\n\n\n<p><code>--single-transaction<\/code> gives a consistent snapshot without locking InnoDB tables, and <code>--set-gtid-purged=ON<\/code> records the GTID position. Now point the replica at the primary and start replication:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CHANGE REPLICATION SOURCE TO\n  SOURCE_HOST     = '10.0.0.1',\n  SOURCE_USER     = 'repl',\n  SOURCE_PASSWORD = 'a-strong-password',\n  SOURCE_SSL      = 1,\n  SOURCE_AUTO_POSITION = 1;\n\nSTART REPLICA;<\/code><\/pre>\n\n\n\n<p>On MySQL 5.7 and MariaDB the older <code>CHANGE MASTER TO \u2026 MASTER_HOST \u2026 MASTER_AUTO_POSITION = 1<\/code> and <code>START SLAVE<\/code> commands do the same job. <code>SOURCE_AUTO_POSITION = 1<\/code> tells the replica to use GTID to work out exactly where to begin, which removes the fiddly binary log file-and-position bookkeeping of older setups.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Verify and Monitor Replication<\/h2>\n\n\n\n<p>Confirm the replica is healthy with a single command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SHOW REPLICA STATUSG<\/code><\/pre>\n\n\n\n<p>You want <code>Replica_IO_Running: Yes<\/code> and <code>Replica_SQL_Running: Yes<\/code>. The <code>Seconds_Behind_Source<\/code> value shows replication lag and should sit at or near zero. A steadily rising number means the replica cannot keep up, often due to slow storage or single-threaded apply \u2014 multi-threaded replication and fast NVMe both help. Insert a test row on the primary and confirm it appears on the replica to prove the pipeline end to end. (On MySQL 5.7 and MariaDB, use <code>SHOW SLAVE STATUSG<\/code>.)<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Switch to a Replica on Failover<\/h2>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/ukspeed.co.uk\/blog\/wp-content\/uploads\/2026\/09\/my3-failover.svg\" alt=\"MySQL replication failover: promoting the replica to primary and repointing the app\" class=\"wp-image-2613\" \/><figcaption class=\"wp-element-caption\">On failover you promote the replica and repoint the app &mdash; async replication needs orchestration, not automatic promotion by itself.<\/figcaption><\/figure>\n\n\n\n<p>If the primary fails, you promote a replica to become the new primary. Manually, that means stopping replication on the chosen replica, making it writable, and repointing your application and any other replicas at it:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>STOP REPLICA;\nRESET REPLICA ALL;\nSET GLOBAL read_only = OFF;\nSET GLOBAL super_read_only = OFF;<\/code><\/pre>\n\n\n\n<p>The single biggest risk during failover is split-brain, where the old primary returns and accepts writes while the new one is also live. Always fence the failed node \u2014 power it off or block its network \u2014 before promoting, and never let both accept writes at once. In production, tools such as Orchestrator or a ProxySQL layer automate detection, promotion, and traffic switching so this happens in seconds. Whatever approach you choose, test the failover procedure regularly; an untested plan is not a plan.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Best Practices for Production Replication<\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>Monitor lag with alerts:<\/strong> track <code>Seconds_Behind_Source<\/code> and the IO\/SQL thread state, and alert before lag becomes a customer-facing problem.<\/li><li><strong>Use GTID:<\/strong> it makes failover and re-pointing replicas dramatically simpler than legacy file-and-position replication.<\/li><li><strong>Encrypt the channel:<\/strong> replicate over a private network or VPN with TLS enabled, a strong replication-user password, and 3306 firewalled off from the public internet.<\/li><li><strong>Keep versions matched:<\/strong> run the same major MySQL or MariaDB version on both nodes to avoid subtle apply errors.<\/li><li><strong>Back up from the replica:<\/strong> take consistent dumps or snapshots off the replica so the primary is never slowed, and keep an off-site copy.<\/li><li><strong>Test failover:<\/strong> rehearse promotion on a staging pair so the runbook is proven before you ever need it.<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p>MySQL replication turns a single, fragile database into a resilient primary-replica pair that can survive server failure, absorb read traffic, and back itself up without downtime. It is not automatic failover out of the box, but with GTID, semi-synchronous mode where data safety matters, and solid monitoring, a master-replica setup on two UK VPS servers gives you enterprise-grade high availability at a modest cost.<\/p>\n\n\n\n<p>Here is what to do next:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Provision two matched UK VPS nodes on a private network.<\/li><li>Enable binary logging and GTID on the primary, then create the replication user.<\/li><li>Snapshot the primary, load the replica, and start replication with auto-positioning.<\/li><li>Add lag monitoring and rehearse a failover before you go live.<\/li><\/ul>\n\n\n\n\n<div class=\"wp-block-group uks-cta-box has-border-color\" style=\"border-color:#bfdbfe;border-style:solid;border-width:1px;border-radius:14px;background-color:#eff6ff;padding:30px\"><div class=\"wp-block-group__inner-container is-layout-flow wp-block-group-is-layout-flow\">\n\n<h3 class=\"wp-block-heading has-text-color\" style=\"color:#0f172a\">Run a highly available database on UK Speed VPS<\/h3>\n\n\n<p class=\"has-text-color\" style=\"color:#334155\">Spin up primary and replica MySQL nodes on fast UK NVMe VPS with a private network and full root access &#8211; built for high availability and low-latency reads, kept in the UK.<\/p>\n\n\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex\">\n<div class=\"wp-block-button\"><a class=\"wp-block-button__link has-text-color has-background wp-element-button\" href=\"https:\/\/ukspeed.co.uk\/vps-nvme\" style=\"border-radius:8px;color:#ffffff;background-color:#2563eb\">Explore UK NVMe VPS plans &rarr;<\/a><\/div>\n<\/div>\n\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"<p>MySQL replication is one of the most dependable ways to add high availability and read scaling to a production database without rewriting your application,\u2026<\/p>\n","protected":false},"author":3,"featured_media":2619,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_joinchat":[],"footnotes":""},"categories":[105],"tags":[299,143,146,145,113,141,136,140,139],"class_list":["post-2608","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-backup","tag-database","tag-linux","tag-nvme","tag-performance","tag-security","tag-ssl","tag-vps","tag-wordpress"],"_links":{"self":[{"href":"https:\/\/ukspeed.co.uk\/blog\/wp-json\/wp\/v2\/posts\/2608","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ukspeed.co.uk\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ukspeed.co.uk\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ukspeed.co.uk\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/ukspeed.co.uk\/blog\/wp-json\/wp\/v2\/comments?post=2608"}],"version-history":[{"count":4,"href":"https:\/\/ukspeed.co.uk\/blog\/wp-json\/wp\/v2\/posts\/2608\/revisions"}],"predecessor-version":[{"id":2615,"href":"https:\/\/ukspeed.co.uk\/blog\/wp-json\/wp\/v2\/posts\/2608\/revisions\/2615"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ukspeed.co.uk\/blog\/wp-json\/wp\/v2\/media\/2619"}],"wp:attachment":[{"href":"https:\/\/ukspeed.co.uk\/blog\/wp-json\/wp\/v2\/media?parent=2608"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ukspeed.co.uk\/blog\/wp-json\/wp\/v2\/categories?post=2608"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ukspeed.co.uk\/blog\/wp-json\/wp\/v2\/tags?post=2608"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}