MySQL Replication Between Two VPS Servers: Setup Guide

If you've only ever used mysqldump for backups, replication feels like a different animal - but it's really just MySQL streaming its binary log to a second server in near real time. Once it's running, you've got a live, continuously updated copy of your database for reporting, failover, or read scaling. Here's how to wire it up between two VPS instances without the usual gotchas.

Why Bother With Replication

A cron'd mysqldump is fine for nightly backups, but it's a snapshot - you lose everything since the last dump if the source dies mid-day. Replication keeps a second copy within a second or two of the primary, so you can:

• Point read-heavy queries (reports, analytics, search) at the replica and take load off the primary
• Promote the replica to primary in minutes if the main VPS goes down
• Take your mysqldump backups from the replica instead of the live production database, so backups never lock production tables

It's not a replacement for backups - a bad DROP TABLE replicates just as fast as a good insert - but it solves a different problem than mysqldump does.

Before You Start

This guide uses classic file/position-based replication, which works on MySQL 5.7 and 8.0 and on MariaDB. If you're on MySQL 8.0.23+, the commands are the same except CHANGE MASTER TO is now CHANGE REPLICATION SOURCE TO, and START SLAVE is START REPLICA - I'll note both.

Step 1: Configure the Source Server

Edit the MySQL config on the source (usually /etc/mysql/mysql.conf.d/mysqld.cnf on Ubuntu, or /etc/my.cnf on CentOS/AlmaLinux):

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = your_database_name
bind-address = 0.0.0.0

server-id must be a unique positive integer across every server in the replication set - never reuse it. Restart MySQL to apply:

sudo systemctl restart mysql

Now create a dedicated replication user - don't reuse your app's DB user for this:

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

Then lock the tables briefly and note the current binlog position - this is the exact point the replica needs to start from:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Write down the File and Position values from the output. Keep this session open - don't run UNLOCK TABLES until after you've taken the dump in Step 2, or the position will drift.

Step 2: Take a Consistent Snapshot

In a second terminal (leaving the lock in place), dump the database:

mysqldump -u root -p --databases your_database_name > /root/replica_seed.sql

Once the dump finishes, go back to the first session and release the lock:

UNLOCK TABLES;

Copy the dump to the replica server:

scp /root/replica_seed.sql user@replica-ip:/root/

Step 3: Configure the Replica Server

On the replica, edit its MySQL config with a different server-id:

[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
read_only = ON

read_only = ON is worth keeping even after setup - it stops anything from accidentally writing to the replica and quietly breaking sync. Restart MySQL, then load the seed dump:

sudo systemctl restart mysql
mysql -u root -p < /root/replica_seed.sql

Point the replica at the source, using the File and Position you noted in Step 1:

CHANGE MASTER TO
  MASTER_HOST='source-vps-ip',
  MASTER_USER='replica_user',
  MASTER_PASSWORD='a-strong-password-here',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=154;

START SLAVE;

On MySQL 8.0.23 or newer, swap in CHANGE REPLICATION SOURCE TO (same options, renamed: SOURCE_HOST, SOURCE_USER, etc.) and START REPLICA.

Step 4: Verify It's Actually Working

Don't trust that a clean START SLAVE means you're done - check the status:

SHOW SLAVE STATUS\G

Look for exactly two lines:

• Slave_IO_Running: Yes - the replica is pulling the binlog stream from the source
• Slave_SQL_Running: Yes - the replica is applying those events locally

If both say Yes and Seconds_Behind_Master is 0 (or close to it), you're replicating. Prove it by inserting a test row on the source and confirming it shows up on the replica within a second or two.

Common Errors and Fixes

Securing the Replication Link

Replication traffic isn't encrypted by default, and the replication user's GRANT is powerful. Two things worth doing before this goes anywhere near production:

• Lock port 3306 down to the replica's IP address only, via UFW (ufw allow from replica-ip to any port 3306) or your firewall of choice - never leave it open to 0.0.0.0/0
• If the two VPS servers talk over the public internet rather than a private network, set up replication over SSL (MASTER_SSL=1 plus certificates) so the binlog stream itself isn't sent in plaintext

Prevention: Keep an Eye on It

Replication that silently stops is worse than no replication - you think you have a live copy and you don't. Add a quick cron check that alerts you if Slave_IO_Running or Slave_SQL_Running ever comes back anything other than Yes, and keep taking your regular mysqldump backups regardless. Replication protects against downtime; backups protect against mistakes. You need both.