VPS Database Replication: Setting Up MySQL Master-Slave on a Single VPS

Database replication is often associated with multi-server architectures, but you can run a fully functional MySQL master-slave setup on a single VPS. Why would you do that? For backups without locking production tables, running analytics queries against a read replica, testing failover scenarios, or simply learning replication before moving to a multi-node cluster. This tutorial walks through a practical single-VPS master-slave configuration using MySQL 8 on Ubuntu.

Prerequisites

A VPS running Ubuntu 22.04 or later with at least 2 GB RAM and 10 GB free disk space.
MySQL 8 installed (sudo apt install mysql-server).
Root or sudo access.
Basic familiarity with the MySQL command line.

If you need a VPS for this setup, consider the plans at virtualserversvps.com—a 2 GB RAM instance is sufficient for learning and light workloads.

Step 1: Install and Configure MySQL

If MySQL is not already running, install it and secure the installation:

sudo apt update
sudo apt install mysql-server -y
sudo mysql_secure_installation

Follow the prompts to set a root password and remove anonymous users. Then edit the MySQL configuration file:

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

Find the [mysqld] section and add or modify these lines:

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = your_database_name
bind-address = 127.0.0.1

Replace your_database_name with the actual database you want to replicate. The bind-address stays local because both master and slave run on the same host. Restart MySQL:

sudo systemctl restart mysql

Step 2: Create a Replication User

Log into MySQL as root:

sudo mysql -u root -p

Create a user specifically for replication:

CREATE USER 'replicator'@'127.0.0.1' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'127.0.0.1';
FLUSH PRIVILEGES;

Note the master status—you will need these coordinates for the slave:

SHOW MASTER STATUS;

Write down the File and Position values from the output.

Step 3: Configure the Slave Instance

On a single VPS, the slave runs as a separate MySQL instance on a different port. The cleanest approach is to use MySQL’s multi-source or run a second MySQL process with a separate data directory and config file. For simplicity, we will use Docker to run the slave instance on port 3307 while the master runs natively on 3306.

Install Docker if not already present:

sudo apt install docker.io -y
sudo systemctl start docker

Run the slave container:

sudo docker run -d \
  --name mysql-slave \
  -e MYSQL_ROOT_PASSWORD=slave_root_pass \
  -p 127.0.0.1:3307:3306 \
  mysql:8.0

Now configure the slave to connect to the master. Connect to the slave container:

sudo docker exec -it mysql-slave mysql -u root -p

Run the following, replacing the MASTER_LOG_FILE and MASTER_LOG_POS with the values from Step 2:

CHANGE MASTER TO
  MASTER_HOST='127.0.0.1',
  MASTER_PORT=3306,
  MASTER_USER='replicator',
  MASTER_PASSWORD='strong_password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=1234;

START SLAVE;

Check the slave status:

SHOW SLAVE STATUS\G

Look for Slave_IO_Running: Yes and Slave_SQL_Running: Yes. If either says No, check the Last_IO_Error or Last_SQL_Error field for details.

Step 4: Test Replication

On the master (port 3306), create a test database and a table:

CREATE DATABASE test_repl;
USE test_repl;
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));
INSERT INTO users VALUES (1, 'Alice');

On the slave (port 3307), verify the data arrived:

USE test_repl;
SELECT * FROM users;

You should see the row with Alice. If you do, replication is working. You can now use the slave for read-only queries, backups, or testing without affecting the master.

Use Cases for Single-VPS Replication

Lock-free backups — Run mysqldump against the slave while the master handles production traffic.
Analytics — Point reporting tools at the slave so heavy queries do not slow the main application.
Failover practice — Stop the slave, promote it, then reattach—learn the mechanics without risking a real cluster.
Staging schema changes — Test ALTER TABLE on the slave before applying to the master.

Conclusion

A single-VPS MySQL master-slave setup is a low-risk way to learn database replication, improve backup workflows, and isolate read-heavy workloads. Once you outgrow one server, the same configuration scales directly to a multi-node cluster across multiple VPS instances. For more VPS database tutorials, visit virtualserversvps.com.