Configure MySQL Replication on Ubuntu VPS: Complete Master-Slave Setup

Understanding MySQL Replication for VPS Hosting

MySQL replication creates a copy of your database on a separate server. Changes sync in real-time. For hosting customers running business applications, this provides data redundancy and the ability to distribute read operations across multiple servers.

This tutorial walks you through setting up master-slave replication between two Ubuntu VPS instances. The master server handles all write operations. The slave serves read queries and maintains an up-to-date copy of your data.

You'll need two Ubuntu VPS servers with MySQL installed. If you're running production applications, consider using Hostperl VPS instances with dedicated resources. This ensures consistent replication performance.

Preparing the Master Server Configuration

Start by configuring the master database server. Enable binary logging and assign a unique server ID.

Edit the MySQL configuration file:

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

Add these settings under the [mysqld] section:

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = your_database_name
bind-address = 0.0.0.0

The server-id must be unique across your replication setup. Binary logging records all database changes that need replication.

Replace "your_database_name" with your actual database name. Then restart MySQL:

sudo systemctl restart mysql

Creating a Replication User Account

The slave server needs a dedicated MySQL user account with replication privileges.

Log into MySQL on the master server:

sudo mysql -u root -p

Create the replication user:

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

Choose a strong password for the replica user. The '%' wildcard allows connections from any IP address. You can restrict this to your slave server's IP for better security.

Note the current master status:

SHOW MASTER STATUS;

Record the File name and Position values. You'll need these when configuring the slave server.

Configure MySQL Replication Ubuntu VPS Slave Settings

Set up the slave server with a unique server ID and replication settings.

Edit the MySQL configuration on your slave VPS:

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

Add these settings:

[mysqld]
server-id = 2
relay-log = mysql-relay-bin
log-bin = mysql-bin
binlog-do-db = your_database_name
read-only = 1

The relay log stores changes received from the master before applying them locally. Setting read-only prevents accidental writes to the slave database.

Restart MySQL on the slave server:

sudo systemctl restart mysql

Establishing the Master-Slave Connection

Connect the slave server to the master using the replication user credentials.

Log into MySQL on the slave server:

sudo mysql -u root -p

Configure the master connection details:

CHANGE MASTER TO
MASTER_HOST = 'master_server_ip',
MASTER_USER = 'replica_user',
MASTER_PASSWORD = 'strong_password_here',
MASTER_LOG_FILE = 'mysql-bin.000001',
MASTER_LOG_POS = 107;

Replace the IP address, password, log file, and position with the actual values from your master server. Use the exact File and Position values you recorded earlier.

Start the slave replication process:

START SLAVE;

Verifying Replication Status and Performance

Check that replication works correctly by examining the slave status.

Run this command on the slave server:

SHOW SLAVE STATUS\G;

Look for these key indicators:

• Slave_IO_Running: Should be "Yes"
• Slave_SQL_Running: Should be "Yes"
• Seconds_Behind_Master: Should be 0 or a small number
• Last_Error: Should be empty

If either IO or SQL thread shows "No", check the Last_Error field for troubleshooting information. Network connectivity issues between servers often cause replication failures.

Test the replication by creating a test table on the master:

USE your_database_name;
CREATE TABLE replication_test (id INT, message VARCHAR(100));
INSERT INTO replication_test VALUES (1, 'Replication working');

Verify the table appears on the slave server within seconds.

Database Backup Integration with Replication

Use your slave server for backup operations without impacting master server performance. This prevents backup processes from locking tables on your production database.

Create a backup script that runs on the slave server:

#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/var/backups/mysql"
mysqldump --single-transaction --routines --triggers your_database_name > "$BACKUP_DIR/backup_$DATE.sql"

Schedule this script using cron to run during low-traffic hours. The slave server handles the backup load. Your master continues serving live traffic.

For automated backup solutions with monitoring and alerts, our Hostperl VPS hosting includes backup automation tools. These work with MySQL replication setups.

Monitoring Replication Health and Lag

Set up monitoring to catch replication issues before they impact your applications.

Create a simple monitoring script:

#!/bin/bash
LAG=$(mysql -u root -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')
if [ "$LAG" -gt 30 ]; then
    echo "Replication lag detected: $LAG seconds" | mail -s "MySQL Replication Alert" admin@yourdomain.com
fi

Run this script every 5 minutes via cron. Replication lag above 30 seconds often indicates network issues, high write volume, or resource constraints on the slave server.

Monitor disk space on both servers. Binary logs accumulate on the master and relay logs grow on the slave.

Configure log rotation to prevent disk space issues:

SET GLOBAL expire_logs_days = 7;
SET GLOBAL max_binlog_size = 100M;

Handling Replication Failures and Recovery

Network interruptions or server restarts can break replication. Here's how to recover without data loss.

If replication stops, first check the error messages:

SHOW SLAVE STATUS\G;

For duplicate key errors (common after network interruptions), skip the problematic statement:

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

For serious corruption, you may need to reset replication completely:

STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='', MASTER_USER='', MASTER_PASSWORD='';

Then reconfigure using fresh master status information. Always test recovery procedures in a staging environment before applying them to production systems.

Frequently Asked Questions

How much replication lag is normal for MySQL on VPS?

Under normal conditions, replication lag should be under 1-2 seconds. Lag above 10 seconds indicates network or performance issues that need investigation.

Can I use the slave server for read queries while replication is active?

Yes, slave servers can handle SELECT queries to distribute database load. Ensure your application can handle slightly outdated data due to replication lag.

What happens if the master server fails during replication?

The slave server maintains a complete copy of your data up to the point of failure. You can promote the slave to master status. This requires updating application connection strings.

How much additional disk space does MySQL replication require?

Binary logs on the master typically use 10-20% additional disk space. Slave servers need space for relay logs and a complete database copy. Plan for 2x your database size on the slave server.

Can I replicate multiple databases between the same master and slave servers?

Yes, modify the binlog-do-db setting to include multiple databases. Or remove it entirely to replicate all databases. Each database replicates independently within the same connection.