How to Set Up MySQL Database Replication on a VPS

Running a production database on a single VPS is risky. If that server goes down, your entire application goes with it. Database replication solves this by maintaining synchronized copies of your data across multiple VPS instances. This tutorial walks through configuring MySQL master-slave replication on two Ubuntu VPS nodes, complete with monitoring, failover considerations, and security hardening.

Why Database Replication Matters on a VPS

VPS instances, especially at lower price points, share hypervisor resources with neighboring tenants. While reputable providers minimize noise, resource contention can still cause brief service interruptions. Database replication gives you:

• High availability — If the primary fails, the replica can be promoted to master with minimal downtime.
• Read scalability — Offload read queries to replica servers to keep the master focused on writes.
• Disaster recovery — A replica on a separate VPS (ideally in a different datacenter) protects against full server failure.
• Backup isolation — Run backups against the replica to avoid performance impact on production traffic.

Before diving in, review the VPS comparison table to choose providers with adequate RAM and disk I/O for database workloads.

Prerequisites

• Two Ubuntu 22.04 or 24.04 VPS instances (one master, one replica)
• MySQL 8.0+ installed on both (or MariaDB 10.6+)
• Root or sudo access on both servers
• Network connectivity between both VPS instances (private network preferred)

For this guide we recommend at least 2 GB RAM per VPS. InterServer VPS plans offer NVMe storage and dedicated CPU resources ideal for database workloads.

Step 1: Configure the Master Server

SSH into your master VPS and edit the MySQL configuration:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add or uncomment the following lines under the [mysqld] section:

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

Replace your_database_name with the actual database you want to replicate. Restart MySQL:

sudo systemctl restart mysql

Step 2: Create a Replication User

Log into the MySQL shell on the master and create a dedicated replication user:

sudo mysql -u root -p

CREATE USER 'replica'@'%' IDENTIFIED BY 'strong_password_here';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;

SHOW MASTER STATUS;

Note the File and Position values from SHOW MASTER STATUS; — you will need them on the replica.

Step 3: Configure the Replica Server

On the replica VPS, edit the MySQL configuration:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

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

The read_only = 1 prevents accidental writes to the replica. Restart MySQL:

sudo systemctl restart mysql

Step 4: Import the Master Database to the Replica

On the master, export the database:

mysqldump -u root -p --databases your_database_name --master-data > master_dump.sql

Copy this dump to the replica (via SCP):

scp master_dump.sql user@replica_ip:/tmp/

Import it on the replica:

mysql -u root -p < /tmp/master_dump.sql

Step 5: Start Replication

On the replica, configure and start the replication process:

sudo mysql -u root -p

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='master_ip',
  SOURCE_USER='replica',
  SOURCE_PASSWORD='strong_password_here',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=157;

START REPLICA;

SHOW REPLICA STATUS\G

Check that Replica_IO_Running and Replica_SQL_Running both show Yes. If Seconds_Behind_Source is 0 or a low number, replication is working.

Step 6: Monitor and Verify Replication

Create a monitoring script that checks replica lag:

#!/bin/bash
# check_replication.sh
STATUS=$(mysql -u root -p'your_password' -e "SHOW REPLICA STATUS\G" 2>/dev/null)

IO_RUNNING=$(echo "$STATUS" | grep "Replica_IO_Running" | awk '{print $2}')
SQL_RUNNING=$(echo "$STATUS" | grep "Replica_SQL_Running" | awk '{print $2}')
SECONDS_BEHIND=$(echo "$STATUS" | grep "Seconds_Behind_Source" | awk '{print $2}')

if [ "$IO_RUNNING" != "Yes" ] || [ "$SQL_RUNNING" != "Yes" ]; then
  echo "Replication is DOWN!" | mail -s "CRITICAL: MySQL Replication Failure" [email protected]
elif [ "$SECONDS_BEHIND" -gt 300 ]; then
  echo "Replica lag exceeds 5 minutes ($SECONDS_BEHIND seconds)" | mail -s "WARNING: MySQL Replication Lag" [email protected]
else
  echo "Replication OK. Lag: ${SECONDS_BEHIND}s"
fi

Add this to cron to run every 5 minutes:

*/5 * * * * /usr/local/bin/check_replication.sh

Security Hardening for Replication

• Use private networking — Configure both VPS instances on the same private network so replication traffic never touches the public internet.
• Encrypt replication — MySQL 8.0 supports TLS for replication. Add SOURCE_SSL=1 to your CHANGE command.
• Firewall the replica — Only allow MySQL connections from the master IP: sudo ufw allow from master_ip to any port 3306.
• Use strong passwords — The replication user should have a randomly generated password stored in a secrets manager.

Automated Failover with Orchestrator

For production environments, manual failover is too slow. Orchestrator is an open-source MySQL replication management tool that detects master failures and promotes the best replica automatically. Install it on a third management VPS:

sudo apt install -y orchestrator orchestrator-cli

Orchestrator also provides a web UI for visualizing replication topology. For managed database solutions that handle replication automatically, Cloudways managed hosting includes built-in database replication and automated backups.

Conclusion

MySQL replication transforms a single-point-of-failure database into a resilient, scalable data layer. By following this guide, you now have a working master-replica setup with monitoring, security hardening, and a path to automated failover. Start with asynchronous replication for simplicity, then graduate to semi-synchronous or Group Replication as your requirements grow. For help choosing a VPS configuration that supports database workloads, check our VPS provider comparison table.