Back to articles
AI & Cloud

Setting Up MySQL Replication Cluster on 2 VPS: High Availability Solution for Business Data

Detailed guide to setting up a MySQL replication cluster on two VPS servers to ensure high availability and secure, stable data backup for business databases.

DPTCloud Engineering4 min read
Setting Up MySQL Replication Cluster on 2 VPS: High Availability Solution for Business Data

Introduction to MySQL Replication Cluster

MySQL Replication is a widely used solution for ensuring high availability and automatic data backup in database systems. By implementing a master-slave replication configuration, data from the primary server (master) is automatically copied to the secondary server (slave), enhancing system stability and enabling quick disaster recovery. This guide walks through setting up a MySQL Replication Cluster on two VPS instances to protect business data effectively.

Requirements and Prerequisites

  • 2 VPS servers (available via High-RAM Cloud VPS for Self-Hosted LLMs & API Gateways)
  • MySQL 8.0 or higher installed on both VPS instances
  • Stable network connection between the two VPS servers
  • Root or MySQL administrator account on both servers
  • Firewall configured to allow traffic on port 3306 (MySQL)

Setting Up MySQL Replication on Two VPS Servers

We will configure one server as the master and the other as the slave. The process includes master configuration, replication user creation, slave configuration, and starting replication.

Step 1: Configure the Master

On the master server, edit the MySQL configuration file:

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

Add or modify the following lines:

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=ROW
bind-address=0.0.0.0

Restart MySQL to apply changes:

sudo systemctl restart mysql

Step 2: Create Replication User

Log in to MySQL on the master server and create a replication user:

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

Step 3: Configure the Slave

On the slave server, edit the MySQL configuration file:

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

Add the following lines:

[mysqld]
server-id=2
relay-log=relay-bin
bind-address=0.0.0.0

Restart MySQL:

sudo systemctl restart mysql

Step 4: Start Replication

On the master server, check the binary log status:

SHOW MASTER STATUS;

Take note of the File and Position values.

On the slave server, configure the master connection:

CHANGE MASTER TO
  MASTER_HOST='[IP_Master]',
  MASTER_USER='replica',
  MASTER_PASSWORD='your_password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

Start the replication process:

START SLAVE;

Verification and Testing

To verify replication status, run the following command on the slave server:

SHOW SLAVE STATUS\\G

Check the following fields:

  • Slave_IO_Running: Must be Yes
  • Slave_SQL_Running: Must be Yes
  • Last_Error: Must be empty (no errors)

Troubleshooting Common Issues

  • Cannot connect to master: Verify firewall rules, ensure port 3306 is open, and confirm the master IP is correct.
  • Slave not synchronizing: Double-check the MASTER_LOG_FILE and MASTER_LOG_POS values match the master's output.
  • Replication user permission denied: Recheck the GRANT statement on the master server.

References

DPTCloud Tier III Infrastructure

High-RAM Cloud VPS Plans Optimized for AI, Databases & Cache

Enterprise RAID 10 NVMe SSDs, CMC & FPT District 9 uplinks with Anycast Anti-DDoS and 99.99% SLA.

View 22 VPS Plans
ECO · vn-02Recommended

VPS-4E

297.000 ₫ / mo

10% VAT included

  • CPU: 4 vCPU
  • RAM: 8 GB
  • SSD: 50 GB NVMe
ECO · vn-02

VPS-5E

614.900 ₫ / mo

10% VAT included

  • CPU: 8 vCPU
  • RAM: 16 GB
  • SSD: 100 GB NVMe
ADVANCED · vn-03Recommended

VPS-4T

548.900 ₫ / mo

10% VAT included

  • CPU: 4 vCPU
  • RAM: 8 GB
  • SSD: 50 GB NVMe

Related Articles

View all
ZaloZalo SupportDiscord0384 753 478