Securing VPS Database Replication: MySQL Master-Slave Setup with SSL on a Budget VPS

Database replication is the foundation of high availability, read scaling, and disaster recovery for production applications. A MySQL master-slave replication setup allows you to distribute read queries across multiple servers and maintain a real-time copy of your data for failover scenarios. However, default replication configurations transmit data in plaintext and lack authentication controls — a significant security risk on any VPS. This guide walks through setting up MySQL 8.4 master-slave replication with SSL encryption on budget VPS instances, covering security hardening at every layer.

Before you begin, ensure your VPS instances have adequate resources for replication overhead. Compare VPS providers on our comparison table to find affordable plans with private networking and sufficient RAM for both your application and replication workload.

Prerequisites

• Two VPS instances — One master, one slave (both running Ubuntu 24.04 or Debian 12)
• MySQL 8.4+ installed on both instances
• Root or sudo access on both servers
• Firewall rules allowing MySQL port (3306) between the two VPS IPs only
• Private network recommended — most VPS providers offer a private IP at no extra cost

1. Generate SSL Certificates for Encrypted Replication

MySQL supports TLS encryption for replication connections. Generate a self-signed CA and server/client certificates on the master node:

# Create certificate directory
sudo mkdir -p /etc/mysql/ssl
cd /etc/mysql/ssl

# Generate Certificate Authority
sudo openssl genrsa 2048 > ca-key.pem
sudo openssl req -new -x509 -nodes -days 3650     -key ca-key.pem -out ca-cert.pem     -subj "/C=US/ST=State/L=City/O=VPS Replication/CN=MySQL-CA"

# Generate server certificate
sudo openssl req -newkey rsa:2048 -nodes -days 3650     -keyout server-key.pem -out server-req.pem     -subj "/C=US/ST=State/L=City/O=VPS Replication/CN=master-vps"
sudo openssl x509 -req -in server-req.pem -days 3650     -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# Generate client certificate (for slave)
sudo openssl req -newkey rsa:2048 -nodes -days 3650     -keyout client-key.pem -out client-req.pem     -subj "/C=US/ST=State/L=City/O=VPS Replication/CN=slave-vps"
sudo openssl x509 -req -in client-req.pem -days 3650     -CA ca-cert.pem -CAkey ca-key.pem -set_serial 02 -out client-cert.pem

# Secure permissions
sudo chmod 600 *.pem
sudo chown mysql:mysql *.pem

Copy the CA certificate and client certificate files to the slave node using SCP over SSH:

# On master, copy to slave
scp ca-cert.pem client-cert.pem client-key.pem user@slave-vps:/etc/mysql/ssl/

2. Configure the Master Node

Edit /etc/mysql/mysql.conf.d/mysqld.cnf on the master:

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = your_database_name
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M

# SSL/TLS for replication
ssl_ca = /etc/mysql/ssl/ca-cert.pem
ssl_cert = /etc/mysql/ssl/server-cert.pem
ssl_key = /etc/mysql/ssl/server-key.pem

# Require secure transport for replication
require_secure_transport = ON

Restart MySQL: sudo systemctl restart mysql.

Create a dedicated replication user with SSL requirement:

CREATE USER 'replicator'@'%' IDENTIFIED BY 'STRONG_PASSWORD_HERE'
    REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

Record the master status — you will need these values for the slave configuration:

SHOW MASTER STATUS;
-- Output shows File and Position (e.g., mysql-bin.000001, 157)

3. Configure the Slave Node

Edit /etc/mysql/mysql.conf.d/mysqld.cnf on the slave:

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

# SSL/TLS for replication
ssl_ca = /etc/mysql/ssl/ca-cert.pem
ssl_cert = /etc/mysql/ssl/client-cert.pem
ssl_key = /etc/mysql/ssl/client-key.pem
require_secure_transport = ON

Restart MySQL: sudo systemctl restart mysql.

Configure the replication channel with SSL:

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='MASTER_PRIVATE_IP',
    SOURCE_USER='replicator',
    SOURCE_PASSWORD='STRONG_PASSWORD_HERE',
    SOURCE_LOG_FILE='mysql-bin.000001',
    SOURCE_LOG_POS=157,
    SOURCE_SSL=1,
    SOURCE_SSL_CA='/etc/mysql/ssl/ca-cert.pem',
    SOURCE_SSL_CERT='/etc/mysql/ssl/client-cert.pem',
    SOURCE_SSL_KEY='/etc/mysql/ssl/client-key.pem',
    SOURCE_SSL_VERIFY_SERVER_CERT=1;

START REPLICA;

-- Verify status
SHOW REPLICA STATUS\G;

Look for these fields in the output:

• Replica_IO_Running: Yes — The I/O thread is receiving events from the master.
• Replica_SQL_Running: Yes — The SQL thread is applying events to the replica.
• Last_IO_Errno: 0 — No I/O errors (indicates SSL connection is working).
• Replica_IO_State: Waiting for source to send event — Normal idle state.

4. Firewall and Network Security

Restrict MySQL traffic to only the replication pair:

# On master — allow only slave IP
sudo ufw allow from SLAVE_PRIVATE_IP to any port 3306 proto tcp
sudo ufw deny 3306  # block all other MySQL access

# On slave — allow only master IP
sudo ufw allow from MASTER_PRIVATE_IP to any port 3306 proto tcp
sudo ufw deny 3306

Additionally, bind MySQL to the private network interface so it is not exposed to the public internet:

# In mysqld.cnf
bind-address = MASTER_PRIVATE_IP

5. Monitoring Replication Health

Set up a monitoring script to detect replication lag and errors:

#!/bin/bash
# /usr/local/bin/check_replication.sh
LAG=$(mysql -e "SHOW REPLICA STATUS\G" | grep "Seconds_Behind_Source" | awk '{print $2}')
IO_RUNNING=$(mysql -e "SHOW REPLICA STATUS\G" | grep "Replica_IO_Running" | awk '{print $2}')
SQL_RUNNING=$(mysql -e "SHOW REPLICA STATUS\G" | grep "Replica_SQL_Running" | awk '{print $2}')

if [ "$IO_RUNNING" != "Yes" ] || [ "$SQL_RUNNING" != "Yes" ]; then
    echo "Replication ERROR at $(date)" | mail -s "Replication Error" [email protected]
fi

if [ "$LAG" -gt 60 ]; then
    echo "Replication lag ${LAG}s at $(date)" | mail -s "Replication Lag Warning" [email protected]
fi

Add to cron: */5 * * * * /usr/local/bin/check_replication.sh checks every 5 minutes.

6. Security Best Practices Checklist

• Always use SSL for replication traffic — do not rely on network isolation alone.
• Use strong passwords for the replication user (minimum 20 characters, generated via openssl rand -base64 24).
• Restrict replication user hosts — Grant access only from the slave’s IP: CREATE USER 'replicator'@'SLAVE_IP'.
• Enable require_secure_transport on both nodes to reject unencrypted connections.
• Rotate binary logs with expire_logs_days = 7 to prevent disk exhaustion.
• Monitor disk space — replication relay logs can grow unbounded if the slave falls behind.
• Test failover regularly — promote the slave to master and verify application connectivity at least monthly.

Estimated Resource Usage

VPS Spec	Replication Overhead	Suitable For
1 vCPU, 1 GB RAM	~5% CPU, ~50 MB RAM	Low-traffic sites (<10K req/day)
2 vCPU, 2 GB RAM	~3% CPU, ~80 MB RAM	Medium-traffic sites (<100K req/day)
4 vCPU, 4 GB RAM	~1% CPU, ~120 MB RAM	High-traffic sites with read scaling

Replication overhead is minimal — even a budget 2 GB VPS can serve as a replication slave while handling application read traffic.

Conclusion

MySQL master-slave replication with SSL encryption provides both high availability and data security on budget VPS hardware. By generating your own certificates, restricting network access, and monitoring replication health, you can build a production-grade database topology that protects data in transit and ensures business continuity. For VPS providers with private networking and sufficient resources for replication, compare VPS providers on our comparison table to find the right balance of price and performance for your database infrastructure.