MySQL Replication on a VPS: GTID Setup, Binary Logs and Failover Planning

Replication copies every change from a source database to one or more replicas. It gives you read scaling and a warm standby, and it shortens recovery after hardware failure. It does not replace backups: a mistaken DELETE replicates in seconds. This guide sets up GTID-based replication between two KVM VPS and covers monitoring and failover.

Prerequisites

• Two VPS, ideally in different nodes. StreamData runs Mumbai, Noida and Jaipur, and a source in one with a replica in another protects against a site-level event.
• The same major version of MySQL, or of MariaDB, on both.
• Private or firewalled connectivity between them. Never expose the database port to the internet.

Step 1: configure the source

[mysqld]
server_id = 1
log_bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON

Global transaction identifiers give each transaction a unique ID, which makes it much easier to point a replica at a new source during failover. MariaDB implements GTIDs differently, so follow its own replication documentation if you use MariaDB.

Step 2: create a replication user

CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY '<strong password>';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';

Restrict the user to the replica's address range.

Step 3: configure the replica

[mysqld]
server_id = 2
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = ON
super_read_only = ON

Step 4: seed the replica

Take a consistent copy of the source, for example with a logical dump taken with GTID information included, or a physical backup tool, and restore it on the replica.

Step 5: start replication

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.0.1', SOURCE_USER='repl',
  SOURCE_PASSWORD='<password>', SOURCE_AUTO_POSITION=1;
START REPLICA;
SHOW REPLICA STATUS\G

Both the IO and SQL threads should report running, and lag should fall to zero.

Monitoring

• Alert when either replication thread stops.
• Alert on replication lag above what your application tolerates.
• Check disk space for binary logs and set an expiry period.

Failover plan

1. Confirm the source is really down, not just unreachable from one place.
2. Stop writes to the old source if it is still partly alive.
3. Promote the replica: turn off read-only and point the application at it.
4. Rebuild the old source as a new replica when it returns.

Rehearse this before you need it. Automated failover tools exist, but a manual, rehearsed procedure is safer than an automation nobody has tested.

Sizing

Five of the ten published sizes, from the StreamData KVM VPS plans page. Prices are monthly in INR, excluding GST.

Give the replica the same memory as the source so it can take over at full load. For large or sustained write loads, the dedicated server inventory includes EPYC servers with 128 GB DDR4 and 2 TB NVMe.

StreamData does not publish an uptime percentage on its site, so treat availability commitments as something to agree in your order or quotation. Its backup policy is equally clear that backups are the customer's responsibility unless a written order includes managed backups.

Common problems

• Duplicate server IDs. Every server in the topology needs a unique server_id, or replication stops with an error.
• Writes on the replica. Keep super_read_only on so an application or an administrator cannot write to the replica by mistake.
• Binary logs filling the disk. Set an expiry period and alert on free space.
• Large transactions. A single huge update replicates as one event and can cause long lag. Batch large changes.

Checking consistency

Replication can drift if someone writes to the replica or if a statement behaves differently on each side. Row-based logging reduces the risk. Periodically compare checksums of important tables between source and replica, and rebuild the replica from a fresh copy if they differ.

Document the replication topology, credentials location and failover steps in the same place as your backup runbook, so whoever is on call can act without searching.

Frequently asked questions

Can I read from the replica?

Yes, for reporting and read-heavy pages, as long as the application tolerates slight lag.

Is replication a backup?

No. Keep point-in-time backups as well, stored off both servers.

Semi-synchronous or asynchronous?

Asynchronous is simpler and faster. Semi-synchronous reduces data loss on failover at the cost of write latency. Choose based on your RPO.

Sources

• MySQL Reference Manual: Replication with Global Transaction Identifiers
• MariaDB: Setting Up Replication
• MariaDB: Global Transaction ID