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.
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 beYesSlave_SQL_Running: Must beYesLast_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_FILEandMASTER_LOG_POSvalues match the master's output. - Replication user permission denied: Recheck the GRANT statement on the master server.
References
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.
VPS-4E
10% VAT included
- CPU: 4 vCPU
- RAM: 8 GB
- SSD: 50 GB NVMe
