MySQL Replication Setup on Cloud VPS

Why MySQL Replication Matters

As your application grows, a single MySQL server becomes a bottleneck—handling both writes and reads on the same machine limits throughput and creates a single point of failure. MySQL replication solves this by allowing you to copy data from a primary server to one or more replica servers. Replicas handle read queries, reducing load on the primary and providing redundancy in case of failure.

On ServerRaja Cloud VPS, you can spin up multiple instances in the same datacenter for low-latency replication. This guide walks you through setting up asynchronous primary-replica replication from scratch.

Prerequisites

Two ServerRaja Cloud VPS instances running Ubuntu 22.04 or 24.04
MySQL 8.0+ installed on both servers
Root or sudo access on both servers
Network connectivity between servers (same VLAN recommended)

# Install MySQL on both servers
sudo apt update
sudo apt install mysql-server -y
sudo mysql_secure_installation

Step 1: Configure the Primary Server

Edit the MySQL configuration file on the primary server to enable binary logging and set a unique server ID.

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

Add or modify these directives:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800
max_binlog_size = 500M
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

`server-id` must be unique across all servers in the replication topology
`binlog_format = ROW` provides the most reliable replication
`sync_binlog = 1` and `innodb_flush_log_at_trx_commit = 1` ensure durability (at the cost of some write performance)

Restart MySQL:

sudo systemctl restart mysql

Step 2: Create a Replication User on the Primary

Connect to the primary MySQL server and create a dedicated user for replication:

CREATE USER 'repl_user'@'replica-server-ip' IDENTIFIED BY 'StrongReplicationPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'replica-server-ip';
FLUSH PRIVILEGES;

Check the primary's binary log position:

SHOW MASTER STATUS;

Note the `File` and `Position` values—you will need these for the replica configuration.

Step 3: Configure the Replica Server

Edit the MySQL configuration on the replica:

[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin
log_bin = /var/log/mysql/mysql-bin
read_only = ON
super_read_only = ON
log_replica_updates = ON

`server-id = 2` (different from the primary)
`read_only = ON` prevents accidental writes on the replica
`super_read_only = ON` prevents even root from writing

Restart MySQL:

sudo systemctl restart mysql

Step 4: Initialize the Replica

If the primary already has data, you need to create an initial snapshot. Use `mysqldump` for smaller databases:

# On the primary server
mysqldump --all-databases --master-data=2 --single-transaction \
  --routines --triggers --events > /tmp/full-backup.sql

# Transfer to the replica scp /tmp/full-backup.sql user@replica-server-ip:/tmp/

# On the replica server mysql < /tmp/full-backup.sql ```

The `--master-data=2` flag writes the binary log coordinates as a comment in the dump file, so MySQL can automatically pick up replication from the correct position.

Step 5: Start Replication

Connect to the replica and configure it to follow the primary:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'primary-server-ip',
  SOURCE_USER = 'repl_user',
  SOURCE_PASSWORD = 'StrongReplicationPassword123!',
  SOURCE_LOG_FILE = 'mysql-bin.000001',  -- from SHOW MASTER STATUS
  SOURCE_LOG_POS = 157;                 -- from SHOW MASTER STATUS

START REPLICA; ```

Check replication status:

SHOW REPLICA STATUS\G

Look for these critical fields: - `Replica_IO_Running: Yes` - `Replica_SQL_Running: Yes` - `Seconds_Behind_Source: 0` (or a small number)

Step 6: Test the Setup

-- On the primary, create a test database
CREATE DATABASE replication_test;
USE replication_test;
CREATE TABLE messages (id INT AUTO_INCREMENT PRIMARY KEY, content TEXT);
INSERT INTO messages (content) VALUES ('Hello from primary!');

-- On the replica, verify the data appeared USE replication_test; SELECT * FROM messages; ```

Monitoring Replication Health

Set up automated monitoring to catch replication lag or failures early:

#!/bin/bash
# check_replication.sh
STATUS=$(mysql -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}')
LAG=$(echo "$STATUS" | grep "Seconds_Behind" | head -1 | awk '{print $2}')

if [ "$IO_RUNNING" != "Yes" ] || [ "$SQL_RUNNING" != "Yes" ]; then echo "CRITICAL: Replication is broken!" | mail -s "MySQL Replication Alert" [email protected] fi

if [ "$LAG" -gt 60 ] 2>/dev/null; then echo "WARNING: Replication lag is ${LAG} seconds" | mail -s "MySQL Replication Lag" [email protected] fi ```

Handling Replication Errors

Common issues and fixes:

-- Skip a problematic statement (use with caution)
STOP REPLICA;
SET GLOBAL sql_replica_skip_counter = 1;
START REPLICA;

-- For GTID-based replication, inject an empty transaction STOP REPLICA; SET GTID_NEXT = 'uuid:transaction_id'; BEGIN; COMMIT; SET GTID_NEXT = 'AUTOMATIC'; START REPLICA; ```

Conclusion

MySQL replication on ServerRaja Cloud VPS provides read scaling and a foundation for high availability. Start with asynchronous replication as described here, monitor it actively, and consider semi-synchronous replication or MySQL Group Replication when your application demands stronger consistency guarantees.

MySQL Replication Setup on Cloud Servers: A Step-by-Step Guide