Linux VPS MySQL Master-Slave Replication Tutorial: Complete Setup with Binary Logs and Failover on Ubuntu 24.04 in 2026

Why MySQL Master-Slave Replication Matters for VPS Hosting

Database downtime costs money. One hardware failure or corrupted table can take your entire application offline for hours.

MySQL master-slave replication creates a live backup that can take over automatically when problems hit your primary database server.

This tutorial walks you through setting up MySQL master-slave replication between two Ubuntu 24.04 VPS instances. You'll configure binary logging, secure the replication stream with SSL, and build automated failover that actually works when you need it.

We'll use MySQL 8.0.40 (the current stable release) and cover real-world scenarios. This includes handling replication lag, monitoring slave status, and recovering from common failure modes.

Prerequisites and Server Requirements

You need two Ubuntu 24.04 VPS instances with at least 2GB RAM each. The master server handles all write operations, so give it slightly more resources if you're running high-traffic applications.

Both servers need MySQL 8.0 installed and running. Install it with:

sudo apt update
sudo apt install mysql-server-8.0
sudo systemctl enable mysql
sudo systemctl start mysql

Run the security script on both servers:

sudo mysql_secure_installation

Choose a strong root password and disable remote root access. You'll create dedicated replication users later.

Network connectivity between servers is essential. Test it with:

# From master server
telnet slave_server_ip 3306

If the connection fails, check firewall rules and security groups. MySQL needs port 3306 open between the two servers.

Configure the Master Server for Binary Logging

The master server needs binary logging enabled to record all database changes. Edit `/etc/mysql/mysql.conf.d/mysqld.cnf` and add these lines under the `[mysqld]` section:

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
binlog-do-db = your_database_name
expire_logs_days = 7
max_binlog_size = 100M

The `server-id` must be unique across your replication setup. Use `1` for the master and `2` for the slave.

Binary log format `ROW` captures actual data changes rather than SQL statements. This makes replication more reliable.

Restart MySQL to apply the configuration:

sudo systemctl restart mysql

Verify binary logging is active:

mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin';"

You should see `log_bin = ON` in the output.

Create a Dedicated Replication User

Never use root for replication. Create a dedicated user with minimal permissions:

mysql -u root -p

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

Replace `slave_server_ip` with your slave server's actual IP address. This restricts the replication user to connections from that specific server only.

Test the connection from your slave server:

# Run this from the slave server
mysql -h master_server_ip -u replication_user -p

If the connection succeeds, you're ready to proceed. If it fails, double-check the IP address and firewall rules.

Get Master Status and Create Initial Backup

Before configuring the slave, you need the master's current binary log position. This ensures the slave starts replicating from the correct point:

mysql -u root -p

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Note the `File` and `Position` values. They look something like `mysql-bin.000001` and `154`. Keep this terminal session open to maintain the lock.

In a new terminal, create a backup of your database:

mysqldump -u root -p --single-transaction --routines --triggers your_database_name > master_backup.sql

The `--single-transaction` flag ensures a consistent snapshot without locking the entire database.

Transfer this backup to your slave server:

scp master_backup.sql user@slave_server_ip:/tmp/

Now release the table lock on the master:

# Back in the first terminal
UNLOCK TABLES;
EXIT;

Configure the Slave Server

The slave server needs its own unique server ID. Edit `/etc/mysql/mysql.conf.d/mysqld.cnf`:

[mysqld]
server-id = 2
relay-log = mysql-relay-bin
log_slave_updates = 1
read_only = 1

The `read_only` setting prevents accidental writes to the slave. Only users with `SUPER` privilege can write to a read-only server, which includes the replication thread.

Restart MySQL:

sudo systemctl restart mysql

Import the master backup:

mysql -u root -p your_database_name < /tmp/master_backup.sql

This restores your database to the exact state it was in when you ran `SHOW MASTER STATUS`.

Start the Replication Process

Configure the slave to connect to the master using the binary log position you recorded earlier:

mysql -u root -p

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

Replace the values with your actual master server IP, replication user credentials, and the binary log file and position from the master status output.

Start the slave replication threads:

START SLAVE;

Check the replication status:

SHOW SLAVE STATUS\G

Look for these key indicators:

• `Slave_IO_Running: Yes` - The slave is receiving binary logs from the master
• `Slave_SQL_Running: Yes` - The slave is applying changes from the relay logs
• `Seconds_Behind_Master: 0` - The slave is caught up with the master

If either IO or SQL thread shows `No`, check the error messages in the same output. Common issues include network connectivity, authentication problems, or binary log file not found.

With HostMyCode managed VPS hosting, you get pre-configured database optimization and 24/7 monitoring to catch replication issues before they impact your applications.

Enable SSL Encryption for Secure Replication

Replication traffic contains your actual data and should be encrypted. MySQL 8.0 supports SSL encryption between master and slave servers.

First, verify SSL is available on the master:

mysql -u root -p -e "SHOW VARIABLES LIKE 'have_ssl';

You should see `have_ssl = YES`. If not, check your MySQL installation.

Stop the slave and reconfigure it with SSL:

STOP SLAVE;

CHANGE MASTER TO
    MASTER_HOST='master_server_ip',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='strong_password_here',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154,
    MASTER_SSL=1,
    MASTER_SSL_VERIFY_SERVER_CERT=0;

START SLAVE;

The `MASTER_SSL=1` enables SSL encryption. Setting `MASTER_SSL_VERIFY_SERVER_CERT=0` allows self-signed certificates. This works fine for internal replication between your own servers.

Verify SSL is active:

SHOW SLAVE STATUS\G

Look for `Master_SSL_Allowed: Yes` in the output.

Test Replication with Real Data Changes

Create a test table on the master to verify replication works:

# On master server
mysql -u root -p your_database_name

CREATE TABLE replication_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO replication_test (message) VALUES ('Replication test successful');

Check if the table appears on the slave:

# On slave server
mysql -u root -p your_database_name -e "SELECT * FROM replication_test;"

You should see the same data. If the table doesn't exist or data is missing, check the slave status for error messages.

Try a few more operations to test different scenarios:

# On master
UPDATE replication_test SET message = 'Updated via replication' WHERE id = 1;
INSERT INTO replication_test (message) VALUES ('Second test record');
DELETE FROM replication_test WHERE id = 2;

Each change should appear on the slave within seconds.

Monitor Replication Health and Performance

Active monitoring prevents small replication issues from becoming major outages. Set up these key checks:

Create a monitoring script that checks slave status every minute:

#!/bin/bash
# Save as /opt/check_replication.sh

MYSQL_USER="root"
MYSQL_PASS="your_root_password"

SLAVE_STATUS=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -e "SHOW SLAVE STATUS\G" 2>/dev/null)

IO_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_IO_Running:" | awk '{print $2}')
SQL_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_SQL_Running:" | awk '{print $2}')
LAG=$(echo "$SLAVE_STATUS" | grep "Seconds_Behind_Master:" | awk '{print $2}')

if [[ "$IO_RUNNING" != "Yes" ]] || [[ "$SQL_RUNNING" != "Yes" ]]; then
    echo "CRITICAL: MySQL replication stopped"
    exit 2
elif [[ "$LAG" -gt 60 ]]; then
    echo "WARNING: Replication lag ${LAG} seconds"
    exit 1
else
    echo "OK: Replication healthy, ${LAG}s lag"
    exit 0
fi

Make it executable and test:

sudo chmod +x /opt/check_replication.sh
/opt/check_replication.sh

Add it to cron to run every minute:

echo "* * * * * /opt/check_replication.sh >> /var/log/replication_check.log 2>&1" | sudo crontab -

Monitor binary log disk usage on the master. Old logs accumulate over time:

mysql -u root -p -e "SHOW BINARY LOGS;"

The `expire_logs_days = 7` setting automatically removes logs older than a week. Check disk space regularly anyway.

For comprehensive database monitoring and automated backups, HostMyCode database hosting includes built-in replication monitoring with instant alerts when issues occur.

Handle Common Replication Failures

Replication breaks. Here's how to fix the most common problems:

Duplicate Key Error: This happens when the same record gets inserted on both master and slave.

# Check the specific error
SHOW SLAVE STATUS\G

# Skip the problematic statement
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

For recurring duplicate key issues, review your application code. Writes should only happen on the master.

Master Binary Log Not Found: The master purged logs before the slave could read them.

# Get current master status
mysql -h master_server_ip -u replication_user -p -e "SHOW MASTER STATUS;"

# Reset slave to current master position
STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='current_file', MASTER_LOG_POS=current_position;
START SLAVE;

This loses any data changes between the old position and new position. For zero data loss, restore from a fresh backup.

Network Connection Lost: Temporary network issues can break replication.

# Restart slave connections
STOP SLAVE;
START SLAVE;

If problems persist, check firewall rules and network connectivity between servers.

Set Up Automated Failover with Scripts

Manual failover takes too long during an outage. This script promotes the slave to master when the original master becomes unavailable:

#!/bin/bash
# Save as /opt/failover_to_slave.sh

MASTER_IP="your_master_ip"
SLAVE_USER="root"
SLAVE_PASS="your_slave_root_password"

# Test master connectivity
if ! mysql -h $MASTER_IP -u root -p$SLAVE_PASS -e "SELECT 1;" &>/dev/null; then
    echo "Master server unreachable, starting failover..."
    
    # Stop slave replication
    mysql -u $SLAVE_USER -p$SLAVE_PASS -e "STOP SLAVE;"
    
    # Make slave writable
    mysql -u $SLAVE_USER -p$SLAVE_PASS -e "SET GLOBAL read_only = 0;"
    
    # Update application config to point to this server
    echo "Failover complete. Update application database config to use this server."
else
    echo "Master server is accessible. No failover needed."
fi

Test the script in a controlled environment before relying on it for production failover.

For true high availability, consider setting up multiple slaves and implementing proper leader election. Tools like Orchestrator or ProxySQL can automate complex failover scenarios.

Performance Optimization for Replication

Large databases with heavy write activity can experience replication lag. These optimizations help:

Enable Multi-Threaded Replication: MySQL 8.0 can apply changes in parallel on the slave.

# On slave server
STOP SLAVE;
SET GLOBAL slave_parallel_workers = 4;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
START SLAVE;

Use one worker per CPU core, up to 8 workers maximum. More workers don't always improve performance.

Optimize Binary Log Settings: Reduce binary log sync overhead on the master.

# Add to /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
sync_binlog = 1000
binlog_cache_size = 1M
max_binlog_cache_size = 128M

The `sync_binlog = 1000` setting syncs logs every 1000 transactions instead of every transaction. This improves performance but increases the risk of losing recent changes during a crash.

Use Row-Based Replication Efficiently: Enable partial row images to reduce network traffic.

# Add to master configuration
[mysqld]
binlog_row_image = minimal

This only logs changed columns instead of entire rows. It significantly reduces replication traffic for large tables.

Backup Strategy with Replication

Replication isn't a backup. It protects against hardware failures but not against data corruption, accidental deletions, or application bugs that affect both servers.

Run daily backups from the slave server to avoid impacting master performance:

#!/bin/bash
# Daily backup script for slave server

BACKUP_DIR="/backups/mysql"
DATE=$(date +%Y%m%d)
MYSQL_USER="root"
MYSQL_PASS="your_slave_root_password"

mkdir -p $BACKUP_DIR

# Create consistent backup
mysqldump -u $MYSQL_USER -p$MYSQL_PASS \
    --single-transaction \
    --routines \
    --triggers \
    --all-databases \
    --flush-logs \
    --master-data=2 \
    | gzip > "$BACKUP_DIR/mysql_backup_$DATE.sql.gz"

# Keep backups for 30 days
find $BACKUP_DIR -name "mysql_backup_*.sql.gz" -mtime +30 -delete

echo "Backup completed: mysql_backup_$DATE.sql.gz"

The `--master-data=2` option includes binary log position information. This makes it easier to set up new slaves from the backup.

Test your backups regularly by restoring them to a test server. A backup you can't restore is worthless.

Our comprehensive guide on VPS MySQL backup and recovery covers advanced backup strategies including point-in-time recovery and cross-region backup replication.

Frequently Asked Questions

How long does it take to set up MySQL master-slave replication?

Initial setup takes 30-60 minutes for most databases. The actual time depends on your database size since you need to create and transfer a complete backup.

Small databases (under 1GB) replicate within minutes. Larger databases may take hours for the initial sync.

Can I write to the slave database during replication?

You should avoid writing to slave databases. The `read_only = 1` setting prevents most write operations, but users with SUPER privilege can still modify data.

Any changes made directly to the slave won't replicate back to the master and may cause replication conflicts.

What happens if the master server crashes during replication?

The slave stops receiving updates but retains all data up to the crash point. You can promote the slave to master by disabling read-only mode and updating your application configuration.

However, you may lose any transactions that weren't replicated before the crash.

How do I monitor replication lag effectively?

Check `Seconds_Behind_Master` in `SHOW SLAVE STATUS` output. Values under 5 seconds are normal for most applications.

Consistent lag above 30 seconds indicates performance issues with either the master, slave, or network connection. Set up automated alerts when lag exceeds your acceptable threshold.

Can I replicate only specific databases or tables?

Yes, use `binlog-do-db` and `replicate-do-db` settings to limit replication scope. On the master, `binlog-do-db = database_name` only logs changes for specific databases.

On the slave, `replicate-do-db = database_name` only applies changes for those databases. Be careful with cross-database queries and stored procedures when using selective replication.