How to Set Up MariaDB Active-Standby Replication on Bare Metal or VM

A practical guide for configuring MariaDB primary-slave replication for read scaling and standby readiness

MariaDB replication is a common setup for improving read performance, creating a standby database server, and preparing for basic disaster recovery scenarios.

In an active-standby setup, one MariaDB server acts as the primary server and handles writes. The second server acts as a replica, continuously receiving changes from the primary server.

The goal is simple:

Primary server  → handles writes
Standby server  → replicates data and can serve read queries

This guide explains how to configure MariaDB asynchronous replication on bare metal servers or virtual machines.

1. Architecture Overview

The replication architecture looks like this:

Application / Users
        ↓
MariaDB Primary
        ↓ Asynchronous Replication
MariaDB Standby / Slave

In this setup:

Primary = write database
Slave = read-only replica
Replication mode = asynchronous

The standby server can be used for read queries, reporting, backup preparation, or manual failover planning.

However, it is important to understand that this setup is not automatic high availability by itself. If the primary server fails, manual promotion or additional failover tooling is required.

2. Base Requirements

Before starting, prepare the following:

2 servers or virtual machines
MariaDB installed on both servers
Network connectivity between both servers
Port 3306 open from slave to primary
Root or sudo access
Time synchronization enabled

It is also recommended to configure the same timezone on both servers.

For Cambodia time:

sudo timedatectl set-timezone Asia/Phnom_Penh

Check time:

timedatectl

Time synchronization is important for troubleshooting, logs, and replication monitoring.

3. Example Server Information

For this guide, assume:

Primary IP: 192.168.0.10
Slave IP:   192.168.0.11
MariaDB port: 3306
Replication user: replicator

Replace these values with your real server information.

4. Configure the Primary Server

On the primary server, edit the MariaDB configuration file:

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

Find the [mysqld] section and add or update:

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

Explanation:

SettingPurposeserver-id=1Unique ID for the primary serverlog-bin=mysql-binEnables binary loggingbinlog-format=ROWRecommended replication formatexpire_logs_days=7Keeps binary logs for 7 daysbind-address=0.0.0.0Allows remote connections

Restart MariaDB:

sudo systemctl restart mariadb

Check status:

sudo systemctl status mariadb

5. Configure the Slave Server

On the slave server, edit the MariaDB configuration file:

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

Inside the [mysqld] section, add or update:

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

Explanation:

SettingPurposeserver-id=2Unique ID for the slave serverrelay-log=relay-binStores replicated events before applying themread_only=1Prevents normal users from writing to the slave

Restart MariaDB:

sudo systemctl restart mariadb

Check status:

sudo systemctl status mariadb

Important:

Each MariaDB server in replication must have a different server-id.

6. Open Firewall Port 3306

The slave server must be able to connect to the primary server on port 3306.

If UFW is enabled on the primary server, allow MariaDB traffic:

sudo ufw allow from 192.168.0.11 to any port 3306

Or, less securely:

sudo ufw allow 3306

Check firewall status:

sudo ufw status

From the slave server, test connectivity:

nc -vz 192.168.0.10 3306

If the connection fails, check firewall rules, security groups, routing, or MariaDB bind address.

7. Create the Replication User on the Primary

Log in to MariaDB on the primary server:

sudo mariadb

Create a replication user:

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

For better security, restrict the user to the slave server IP:

CREATE USER 'replicator'@'192.168.0.11' IDENTIFIED BY 'StrongPasswordHere';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.168.0.11';
FLUSH PRIVILEGES;

Recommended:

Use a strong password.
Restrict the replication user to the slave IP.
Avoid using '%' in production if possible.

8. Get the Primary Binary Log Position

On the primary server, run:

SHOW MASTER STATUS;

Example output:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB  | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      342 |              |                  |
+------------------+----------+--------------+------------------+

Record these two values:

File:     mysql-bin.000003
Position: 342

You will use them when configuring the slave.

9. Recommended: Initial Data Sync

If the primary already contains data, you should sync the existing data to the slave before starting replication.

On the primary server, create a dump:

mysqldump -uroot -p --all-databases --master-data=2 --single-transaction --routines --triggers --events > dump.sql

Copy the dump to the slave server:

scp dump.sql user@192.168.0.11:/tmp/

On the slave server, restore the dump:

mysql -uroot -p < /tmp/dump.sql

This step ensures the slave starts with the same data as the primary.

Important:

If the primary already has data, do not skip the initial sync.
Replication only sends changes from the selected binary log position onward.

10. Configure Replication on the Slave

Log in to MariaDB on the slave server:

sudo mariadb

Stop and reset any existing replication configuration:

STOP SLAVE;
RESET SLAVE ALL;

Configure the slave to connect to the primary:

CHANGE MASTER TO
MASTER_HOST='192.168.0.10',
MASTER_USER='replicator',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=342;

Start replication:

START SLAVE;

For production, replace the password with your real secure password.

11. Verify Replication Status

On the slave server, run:

SHOW SLAVE STATUS\G

Healthy replication should show:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

Important fields:

FieldMeaningSlave_IO_RunningSlave can connect to primary and read binary logsSlave_SQL_RunningSlave can apply replicated SQL eventsSeconds_Behind_MasterReplication delay in secondsLast_IO_ErrorConnection or binary log errorLast_SQL_ErrorSQL apply error

If both Slave_IO_Running and Slave_SQL_Running are Yes, replication is working.

12. Test Replication

On the primary server:

CREATE DATABASE repl_test;
USE repl_test;

CREATE TABLE t1 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

INSERT INTO t1(name) VALUES ('hello');

On the slave server:

SELECT * FROM repl_test.t1;

Expected result:

+----+-------+
| id | name  |
+----+-------+
|  1 | hello |
+----+-------+

If the record appears on the slave, replication is working.

13. Enforce Read-Only Mode on the Slave

On the slave server:

SET GLOBAL read_only = ON;

To make it persistent, keep this in the slave configuration:

read_only=1

Important note:

The root user or users with SUPER privilege may still be able to write even when read_only is enabled.
Use non-privileged accounts for application read access.

For stronger protection, newer MariaDB versions may support additional read-only controls depending on your version and configuration.

14. Monitoring Replication

Regularly check:

SHOW SLAVE STATUS\G

Focus on:

Slave_IO_Running
Slave_SQL_Running
Seconds_Behind_Master
Last_IO_Error
Last_SQL_Error

A simple monitoring checklist:

Slave_IO_Running should be Yes
Slave_SQL_Running should be Yes
Seconds_Behind_Master should be low
Last_IO_Error should be empty
Last_SQL_Error should be empty

For production, integrate replication monitoring with your monitoring system.

15. Troubleshooting

Cannot Connect to Primary

Check firewall:

sudo ufw status
sudo ufw allow 3306

Check MariaDB is listening:

sudo ss -tulnp | grep 3306

Check bind-address on the primary:

bind-address=0.0.0.0

Replication User Cannot Login

Test from the slave:

mysql -h 192.168.0.10 -u replicator -p

If login fails, check:

Replication username
Password
Host restriction
Firewall
MariaDB privileges

Binary Logging Not Enabled

On primary:

SHOW VARIABLES LIKE 'log_bin';

Expected:

ON

If it is OFF, verify:

log-bin=mysql-bin

Then restart MariaDB.

Duplicate Server ID

Check on each server:

SHOW VARIABLES LIKE 'server_id';

Primary and slave must have different values.

Example:

Primary: 1
Slave:   2

Data Not Syncing

Check:

SHOW SLAVE STATUS\G

Look at:

Last_IO_Error
Last_SQL_Error
Exec_Master_Log_Pos
Read_Master_Log_Pos
Seconds_Behind_Master

Also remember:

Only new writes after replication starts will replicate unless you performed initial data sync.

16. Best Practices

For production environments:

Use ROW binlog format
Use different server-id values
Use a strong replication password
Restrict replication user to the slave IP
Take a backup before setup
Perform initial data sync for existing databases
Monitor replication lag
Monitor binary log disk usage
Keep enough binary log retention
Use read-only accounts on the slave
Document manual failover steps

17. Important Concepts

Replication Is Not a Backup

Replication copies changes from primary to slave.

If someone accidentally drops a table on the primary, that drop can also replicate to the slave.

You still need real backups.

Use tools such as:

mysqldump
mariabackup
filesystem snapshots
cloud backups

Replication Is Not Automatic High Availability

This setup does not provide automatic failover.

If the primary fails, the slave does not automatically become the new primary unless you configure additional tooling.

For automatic failover, consider tools such as:

MaxScale
MariaDB replication manager
Keepalived
ProxySQL
Orchestrator
Custom failover automation

Asynchronous Replication Can Have Lag

MariaDB replication in this setup is asynchronous.

That means the primary can commit transactions before the slave receives and applies them.

If the primary fails suddenly, the slave may be missing the latest transactions.

Monitor replication lag carefully.

18. Primary vs Slave Summary

ItemPrimarySlaveRoleWrite databaseReplica / read databaseserver-id12log-binOnOptionalrelay-logNot requiredRequiredread_onlyOffOnHandles writesYesNoHandles readsYesYesUsed for reportingNot preferredYesUsed for backup sourcePossibleYes

19. Quick Command Summary

Primary

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

sudo systemctl restart mariadb

CREATE USER 'replicator'@'192.168.0.11' IDENTIFIED BY 'StrongPasswordHere';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.168.0.11';
FLUSH PRIVILEGES;

SHOW MASTER STATUS;

Slave

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

sudo systemctl restart mariadb

STOP SLAVE;
RESET SLAVE ALL;

CHANGE MASTER TO
MASTER_HOST='192.168.0.10',
MASTER_USER='replicator',
MASTER_PASSWORD='StrongPasswordHere',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=342;

START SLAVE;

SHOW SLAVE STATUS\G

Final Thoughts

MariaDB active-standby replication is a practical way to create a read replica and prepare a standby server for production systems.

It can help with:

Read scaling
Reporting workload separation
Backup offloading
Basic standby readiness

However, replication should not be confused with backup or full high availability.

The key lesson is simple:

MariaDB replication helps copy data to another server, but you still need backups, monitoring, and a clear failover plan for production reliability.

If this article helped you, follow me on Medium for more real-world backend, DevOps, PostgreSQL, Airflow, GLPI, and system engineering tutorials.