Configure MariaDB Master-Slave Replication on Ubuntu VPS

MariaDB Replication: Why Your Database Needs a Backup Plan

Database failures happen. Hardware crashes, network partitions, or simple human error can take your primary database offline. MariaDB master-slave replication creates a live copy of your data on a separate server, giving you both disaster recovery and read scaling capabilities.

This tutorial walks through configuring MariaDB master-slave replication between two Ubuntu VPS instances. You'll set up secure replication with SSL encryption, test failover scenarios, and implement monitoring to catch replication lag before it becomes a problem.

We'll use MariaDB 10.11 on Ubuntu 22.04 throughout this guide. The process works identically on Hostperl VPS instances, where you get root access and the network performance needed for reliable database replication.

Prerequisites and Planning

You need two Ubuntu VPS instances with MariaDB installed. The master server handles all writes, while the slave replicates changes and can serve read queries.

Plan your network topology carefully. Both servers need reliable connectivity with low latency. Geographic separation helps with disaster recovery, but adds network delay that increases replication lag.

Resource planning matters too. The slave server needs enough CPU and RAM to replay all master transactions. For write-heavy workloads, size your slave server similarly to the master.

Install MariaDB on both servers if you haven't already:

sudo apt update
sudo apt install mariadb-server mariadb-client
sudo systemctl enable mariadb
sudo systemctl start mariadb

Run the security script on both servers to set root passwords and remove test databases:

sudo mysql_secure_installation

Configure the Master Server

The master server needs binary logging enabled to record all database changes. Edit the MariaDB configuration file:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Add these settings under the [mysqld] section:

[mysqld]
bind-address = 0.0.0.0
server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
binlog-format = ROW
expire-logs-days = 7
max-binlog-size = 100M

The bind-address = 0.0.0.0 allows connections from other servers. In production, restrict this to specific IP addresses. The server-id must be unique across your replication topology.

Binary log format ROW provides the most reliable replication. It logs actual data changes rather than SQL statements, avoiding issues with non-deterministic functions.

Restart MariaDB to apply the configuration:

sudo systemctl restart mariadb

Create a dedicated replication user on the master server:

sudo mysql -u root -p

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

Record the current binary log position. You'll need this when configuring the slave:

SHOW MASTER STATUS;

Note down the File name and Position number. Don't exit this MySQL session yet—keeping it open prevents the log position from changing.

Prepare Initial Data

The slave needs an exact copy of the master's data to start replication. Create a consistent backup while holding a read lock:

In your existing MySQL session on the master:

FLUSH TABLES WITH READ LOCK;

Open a new terminal session and create the backup:

sudo mysqldump --all-databases --master-data=2 --single-transaction --flush-logs --triggers --routines --events > master_backup.sql

The --master-data=2 option includes the binary log position as a comment in the backup file. This makes slave configuration easier.

Transfer the backup to your slave server:

scp master_backup.sql user@slave-server-ip:/tmp/

Back in your master MySQL session, release the read lock:

UNLOCK TABLES;
EXIT;

Configure the Slave Server

Configure the slave server with a unique server ID. Edit the MariaDB configuration:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
server-id = 2
relay-log = /var/log/mysql/relay-bin.log
log-slave-updates = 1
read-only = 1

The read-only = 1 setting prevents accidental writes to the slave. Users with SUPER privilege can still write, so restrict those carefully.

Restart MariaDB on the slave:

sudo systemctl restart mariadb

Import the master backup:

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

Configure the slave to connect to the master. Log into MySQL on the slave:

sudo mysql -u root -p

Set up the master connection using the binary log position you recorded earlier:

CHANGE MASTER TO
  MASTER_HOST='master-server-ip',
  MASTER_USER='replication',
  MASTER_PASSWORD='strong_replication_password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

Replace the IP address, and use the exact log file name and position from your master status output.

Start the slave processes:

START SLAVE;

Check replication status:

SHOW SLAVE STATUS\G

Look for Slave_IO_Running: Yes and Slave_SQL_Running: Yes. Both must be Yes for replication to work. The Seconds_Behind_Master field shows replication lag.

Test Master-Slave Replication

Verify replication by creating test data on the master. On the master server:

CREATE DATABASE replication_test;
USE replication_test;
CREATE TABLE test_data (id INT AUTO_INCREMENT PRIMARY KEY, message VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
INSERT INTO test_data (message) VALUES ('Replication test message');

Check if the data appears on the slave:

USE replication_test;
SELECT * FROM test_data;

The data should appear within seconds. If not, check the slave status for error messages:

SHOW SLAVE STATUS\G

Common issues include network connectivity problems, wrong credentials, or binary log position mismatches.

Secure Replication with SSL

Replication traffic contains your database data. Encrypt it with SSL, especially over public networks. Generate SSL certificates on the master server:

sudo mkdir -p /etc/mysql/ssl
sudo openssl genrsa 2048 > /etc/mysql/ssl/ca-key.pem
sudo openssl req -new -x509 -nodes -days 3650 -key /etc/mysql/ssl/ca-key.pem -out /etc/mysql/ssl/ca-cert.pem

Create server certificates:

sudo openssl req -newkey rsa:2048 -days 3650 -nodes -keyout /etc/mysql/ssl/server-key.pem -out /etc/mysql/ssl/server-req.pem
sudo openssl rsa -in /etc/mysql/ssl/server-key.pem -out /etc/mysql/ssl/server-key.pem
sudo openssl x509 -req -in /etc/mysql/ssl/server-req.pem -days 3650 -CA /etc/mysql/ssl/ca-cert.pem -CAkey /etc/mysql/ssl/ca-key.pem -set_serial 01 -out /etc/mysql/ssl/server-cert.pem

Copy the certificates to the slave server:

scp /etc/mysql/ssl/ca-cert.pem /etc/mysql/ssl/server-cert.pem /etc/mysql/ssl/server-key.pem user@slave-server:/etc/mysql/ssl/

Configure SSL in the master's MariaDB configuration:

[mysqld]
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

Update the slave's replication configuration to use SSL:

STOP SLAVE;
CHANGE MASTER TO
  MASTER_SSL=1,
  MASTER_SSL_CA='/etc/mysql/ssl/ca-cert.pem',
  MASTER_SSL_CERT='/etc/mysql/ssl/server-cert.pem',
  MASTER_SSL_KEY='/etc/mysql/ssl/server-key.pem';
START SLAVE;

Verify SSL is working by checking the slave status for Master_SSL_Allowed: Yes.

Monitor Replication Health

Replication monitoring prevents small issues from becoming major outages. Create a monitoring script that checks slave status:

#!/bin/bash
# Check MariaDB replication status
REPLICATION_STATUS=$(mysql -u root -p"password" -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master")

echo "$REPLICATION_STATUS"

# Alert if replication is broken
if echo "$REPLICATION_STATUS" | grep -q "No"; then
    echo "ALERT: Replication is broken!"
    # Add notification logic here
fi

Set up a cron job to run this check every few minutes:

*/5 * * * * /path/to/replication-check.sh

Monitor key metrics like replication lag, I/O thread status, and SQL thread status. Most monitoring tools like Prometheus can scrape these metrics directly from MariaDB.

Set alerts for replication lag exceeding your tolerance threshold. For most applications, lag under 5 seconds is acceptable. High-frequency trading or real-time applications need much tighter bounds.

Handle Replication Failures

Replication failures fall into two categories: temporary network issues and permanent divergence. Temporary issues usually self-heal when connectivity returns.

For permanent divergence, you need to rebuild the slave from a fresh master backup. Stop the slave, take a new backup from the master, and restart replication from the current position.

Some common failure scenarios and their solutions:

• Duplicate key errors: Often caused by writes to the slave. Fix the conflict and restart replication with START SLAVE;
• Network timeouts: Check firewall rules and network connectivity. Increase timeout values if needed.
• Disk space issues: Binary logs and relay logs consume disk space. Configure log rotation and monitoring.

Keep documentation of your replication setup including server IDs, credentials, and recovery procedures. This saves critical time during outages.

Performance Optimization

Replication performance depends on network latency, disk I/O, and CPU capacity. The slave must replay transactions at least as fast as the master generates them.

Enable parallel replication for better performance on multi-core systems:

[mysqld]
slave-parallel-threads = 4
slave-domain-parallel-threads = 4

Adjust these values based on your server's CPU cores and workload characteristics. More threads help with parallel transactions but can cause lock contention.

Consider using row-based replication with compressed binary logs for high-volume environments:

[mysqld]
binlog-row-image = minimal
log-slave-updates = 1
sync-binlog = 1

Monitor disk I/O on both servers. Slow disks create replication lag that compounds over time. SSD storage significantly improves replication performance.

Frequently Asked Questions

How do I promote a slave to master during failover?

Stop the slave processes with STOP SLAVE;, then remove the read-only restriction with SET GLOBAL read_only = OFF;. Update your application to connect to the new master server. Document this process thoroughly for emergency use.

Can I run multiple slaves from one master?

Yes, one master can replicate to multiple slaves. Each slave needs a unique server-id and connects independently. This setup provides both read scaling and redundancy.

What happens if the binary logs get deleted on the master?

If the slave needs a binary log that's been deleted, replication breaks permanently. You'll need to rebuild the slave from a fresh backup. Set expire_logs_days high enough to handle expected downtime.

How do I monitor replication lag?

Check the Seconds_Behind_Master field in SHOW SLAVE STATUS. Set up automated monitoring to alert when lag exceeds your threshold. Consistent high lag indicates the slave can't keep up with the master's workload.

Should I replicate the mysql database?

Generally no. User accounts and permissions often need to be different between master and slave. Use replicate-ignore-db = mysql in the slave configuration to skip replicating the mysql database.