MySQL Replication on UK VPS: Master–Replica High Availability 2026

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–replica 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.

What Is MySQL Replication?

MySQL replication is the process by which a primary server (historically called the “master”) records every change to its data in a binary log, and one or more replica servers (historically “slaves”) 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.

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 MariaDB vs MySQL vs PostgreSQL is a good start, and PostgreSQL users can follow our sister guide on self-hosting PostgreSQL on a UK VPS.

Why Use Master–Replica Replication

A single database server is a single point of failure. Master-replica replication addresses that in several practical ways:

• High availability: if the primary fails, you can promote a replica to take over, keeping downtime to minutes rather than hours.
• Read scaling: 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.
• Off-primary backups: run mysqldump or a snapshot against a replica so your backup window never slows the live site.
• Geo-distributed reads: place a replica closer to a specific audience to cut read latency.

Be realistic, though: standard asynchronous replication does not 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 scaling WooCommerce on a UK VPS.

What You Need for MySQL Replication

• Two UK VPS servers running the same major version of MySQL 8.0 (or the same MariaDB release). UK Speed’s UK VPS plans work well as paired database nodes.
• A private network or VPN link between them so replication traffic never crosses the public internet.
• Enough disk for the full dataset on both nodes — fast storage matters, which is why enterprise NVMe pays off for busy databases.
• Root or sudo access, and a hardened SSH setup on both boxes.

Async vs Semi-Synchronous Replication

MySQL supports different consistency guarantees. With asynchronous 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 semi-synchronous 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.

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.

How to Configure the Primary Server

On the primary, edit your MySQL configuration (usually /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/my.cnf). Give the server a unique server-id, 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.

[mysqld]
server-id            = 1
log_bin              = /var/log/mysql/mysql-bin.log
binlog_format        = ROW
gtid_mode            = ON
enforce_gtid_consistency = ON
bind-address         = 10.0.0.1   # private network IP

Restart MySQL, then create a dedicated replication user restricted to the replica’s private IP and grant it only the privilege it needs:

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

MariaDB uses gtid_strict_mode 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 the official MySQL replication documentation. Finally, make sure port 3306 is firewalled to the private network only — see our steps to harden SSH on a Linux VPS for the wider security posture.

How to Set Up the Replica

On the replica, give it its own unique server-id (for example server-id = 2) 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:

# On the primary (or any client with access):
mysqldump --single-transaction --set-gtid-purged=ON 
  --all-databases --triggers --routines --events 
  -u root -p > primary-snapshot.sql

# Copy to the replica and import:
mysql -u root -p < primary-snapshot.sql

--single-transaction gives a consistent snapshot without locking InnoDB tables, and --set-gtid-purged=ON records the GTID position. Now point the replica at the primary and start replication:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST     = '10.0.0.1',
  SOURCE_USER     = 'repl',
  SOURCE_PASSWORD = 'a-strong-password',
  SOURCE_SSL      = 1,
  SOURCE_AUTO_POSITION = 1;

START REPLICA;

On MySQL 5.7 and MariaDB the older CHANGE MASTER TO … MASTER_HOST … MASTER_AUTO_POSITION = 1 and START SLAVE commands do the same job. SOURCE_AUTO_POSITION = 1 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.

Verify and Monitor Replication

Confirm the replica is healthy with a single command:

SHOW REPLICA STATUSG

You want Replica_IO_Running: Yes and Replica_SQL_Running: Yes. The Seconds_Behind_Source 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 — 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 SHOW SLAVE STATUSG.)

Switch to a Replica on Failover

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:

STOP REPLICA;
RESET REPLICA ALL;
SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;

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 — power it off or block its network — 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.

Best Practices for Production Replication

• Monitor lag with alerts: track Seconds_Behind_Source and the IO/SQL thread state, and alert before lag becomes a customer-facing problem.
• Use GTID: it makes failover and re-pointing replicas dramatically simpler than legacy file-and-position replication.
• Encrypt the channel: replicate over a private network or VPN with TLS enabled, a strong replication-user password, and 3306 firewalled off from the public internet.
• Keep versions matched: run the same major MySQL or MariaDB version on both nodes to avoid subtle apply errors.
• Back up from the replica: take consistent dumps or snapshots off the replica so the primary is never slowed, and keep an off-site copy.
• Test failover: rehearse promotion on a staging pair so the runbook is proven before you ever need it.

Conclusion

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.

Here is what to do next:

• Provision two matched UK VPS nodes on a private network.
• Enable binary logging and GTID on the primary, then create the replication user.
• Snapshot the primary, load the replica, and start replication with auto-positioning.
• Add lag monitoring and rehearse a failover before you go live.