Set Up MariaDB Replication on Two VPS Nodes

Database downtime usually doesn’t start with MariaDB itself. It often starts with a VPS reboot, storage contention, or a maintenance window that runs long.

If your app can handle a short read-only period, a primary + standby pair is a simple way to keep service online.

This tutorial shows MariaDB replication on VPS across two nodes: one primary (read/write) and one standby replica (read-only).

You’ll enable binary logging, create a dedicated replication user, take a consistent snapshot, and start replication. Then you’ll run a controlled failover drill and confirm the data matches.

The workflow applies to Ubuntu 24.04/26.04 LTS, Debian 12/13, AlmaLinux 9/10, and Rocky Linux 9/10.

What you’ll build (and what it is not)

• Primary VPS: takes writes from your application.
• Replica VPS: continuously pulls changes from the primary, stays read-only, and can be promoted if the primary fails.

This is classic asynchronous replication. It improves availability and shortens recovery time.

It does not replace backups. Replication copies mistakes and deletes just as faithfully as real data.

Prerequisites and sizing

• Two VPS instances on a private network if possible (recommended). Public IPs also work, but you must lock down firewalls.
• MariaDB 10.11 LTS or newer on both nodes (keep versions matched).
• Root or sudo access.
• Time synchronization enabled (chrony/systemd-timesyncd). Replication is sensitive to clock drift in logging and monitoring.

For production, avoid CPU-starved plans and slow network storage.

NVMe-backed instances make replication more predictable because the replica can apply changes quickly.

If you want steadier I/O, a dedicated VM tier is often worth the jump—HostMyCode VPS plans fit a two-node database pair well.

If you prefer to offload patching and baseline hardening, managed VPS hosting is an option.

Network plan and hostnames

Use stable hostnames and IPs. Example used below:

• Primary: db1 at 10.10.0.11
• Replica: db2 at 10.10.0.12

On both servers, add host entries (or use internal DNS):

sudo nano /etc/hosts

10.10.0.11  db1
10.10.0.12  db2

Step 1: Install MariaDB on both VPS nodes

Install MariaDB from your distro packages (often fine for 10.11+). If you need a specific LTS build, use MariaDB’s official repo instead.

The key rule is simple: run the same major/minor version on both nodes.

Ubuntu/Debian

sudo apt update
sudo apt install -y mariadb-server
sudo systemctl enable --now mariadb

AlmaLinux/Rocky

sudo dnf install -y mariadb-server
sudo systemctl enable --now mariadb

Run the secure setup on both:

sudo mariadb-secure-installation

• Set a root password (or use unix_socket auth—either is fine, just be consistent).
• Remove anonymous users.
• Disallow remote root login.
• Remove test database.

Verify:

sudo mariadb -e "SELECT VERSION();"

If you want a refresher on basics and backup hygiene, keep this open: Install MariaDB and Create Secure Backups on Ubuntu.

The same principles apply here: prioritize consistency, then automate.

Step 2: Configure the primary for binary logs (db1)

Replication depends on binary logs.

Use row-based logging to reduce surprises from non-deterministic statements.

Open your MariaDB server config. Paths differ by distro:

• Ubuntu/Debian: /etc/mysql/mariadb.conf.d/50-server.cnf
• Alma/Rocky: /etc/my.cnf.d/mariadb-server.cnf (or /etc/my.cnf)

On db1, edit the server section (usually [mariadb] or [mysqld]) and set:

[mariadb]
bind-address = 0.0.0.0
server_id = 1
log_bin = mariadb-bin
binlog_format = ROW
binlog_row_image = FULL
expire_logs_days = 7
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

Notes:

• bind-address: listen on the private interface. If you can bind to the private IP only, do that instead of 0.0.0.0.
• sync_binlog=1 and innodb_flush_log_at_trx_commit=1: stronger durability, slightly more write latency. For most web apps, it’s a good trade.

Restart MariaDB:

sudo systemctl restart mariadb

Verify binary logging is on:

sudo mariadb -e "SHOW VARIABLES LIKE 'log_bin';"
sudo mariadb -e "SHOW VARIABLES LIKE 'binlog_format';"

Step 3: Create a replication user on the primary (db1)

Create a dedicated account for replication. Restrict it to the replica’s IP.

Use a long random password. Store it in a secrets manager (or at least somewhere better than shell history).

sudo mariadb

CREATE USER 'repl'@'10.10.0.12' IDENTIFIED BY 'REPLACE_WITH_A_LONG_RANDOM_PASSWORD';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'10.10.0.12';
FLUSH PRIVILEGES;

Verify grants:

SHOW GRANTS FOR 'repl'@'10.10.0.12';

Step 4: Lock down port 3306 (firewall)

Even on a private network, don’t leave MariaDB open.

Allow 3306 only between db1 and db2.

Add app servers separately if they need direct DB access.

UFW (Ubuntu/Debian)

On db1:

sudo ufw allow from 10.10.0.12 to any port 3306 proto tcp
sudo ufw enable
sudo ufw status verbose

On db2:

sudo ufw allow from 10.10.0.11 to any port 3306 proto tcp
sudo ufw enable
sudo ufw status verbose

firewalld (Alma/Rocky)

On db1:

sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="10.10.0.12" port protocol="tcp" port="3306" accept'
sudo firewall-cmd --reload

Quick connectivity test from db2 to db1:

nc -vz 10.10.0.11 3306

For a general security baseline (unattended updates, SSH cleanup, firewall defaults), this pairs well with: Set Up Automated Security Updates on Ubuntu VPS.

Step 5: Take a consistent snapshot of the primary

The replica needs a starting dataset that matches a known binlog position.

For many deployments, mariadb-dump with --single-transaction (InnoDB) plus --master-data is a safe, predictable approach.

On db1, create a dump:

sudo mkdir -p /root/replication-bootstrap
sudo mariadb-dump \
  --all-databases \
  --single-transaction \
  --routines --events --triggers \
  --master-data=2 \
  --gtid \
  --hex-blob \
  --flush-logs \
  --quick \
  > /root/replication-bootstrap/db1.sql

What to look for:

• --master-data=2 writes a commented CHANGE MASTER TO line containing MASTER_LOG_FILE and MASTER_LOG_POS.
• --gtid includes GTID info if GTID is enabled/available. We’ll set up GTID-based replication next, but file/position details remain useful when you’re troubleshooting.

Verify the dump contains a binlog hint:

grep -n "CHANGE MASTER TO" -n /root/replication-bootstrap/db1.sql | head

Copy the dump to db2:

scp /root/replication-bootstrap/db1.sql root@10.10.0.12:/root/

Step 6: Configure the replica (db2) for replication

On db2, set a unique server ID and make it read-only.

You can also enable binlogs on the replica. This helps with promotion and point-in-time recovery later.

Edit the MariaDB config on db2:

[mariadb]
bind-address = 0.0.0.0
server_id = 2
read_only = 1
super_read_only = 1
relay_log = mariadb-relay-bin
log_bin = mariadb-bin
binlog_format = ROW

Restart:

sudo systemctl restart mariadb

Step 7: Import the snapshot into the replica

On db2:

sudo mariadb < /root/db1.sql

Verify databases exist:

sudo mariadb -e "SHOW DATABASES;"

Common mistake: importing onto a host that already has “almost the same” tables.

If you’re converting an existing server, stop here and reconcile schemas first.

Table mismatches often show up later as confusing replication errors.

Step 8: Start replication (GTID recommended)

GTID-based replication is usually the cleanest option in 2026, especially if you expect to promote the replica.

You spend less time chasing file names and positions. You can focus on validating behavior instead.

On db1 and db2, add (or confirm) GTID settings in the MariaDB config:

[mariadb]
gtid_strict_mode = 1
log_slave_updates = 1

Restart both nodes if you changed anything.

Now configure the replica’s master connection on db2:

sudo mariadb

STOP SLAVE;
RESET SLAVE ALL;

CHANGE MASTER TO
  MASTER_HOST='10.10.0.11',
  MASTER_USER='repl',
  MASTER_PASSWORD='REPLACE_WITH_A_LONG_RANDOM_PASSWORD',
  MASTER_PORT=3306,
  MASTER_USE_GTID=slave_pos;

START SLAVE;

Verify replication health:

SHOW SLAVE STATUS\G

Check these fields:

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Seconds_Behind_Master: ideally small (0–a few seconds under normal load)
• Last_IO_Error / Last_SQL_Error: should be empty

If Slave_IO_Running is No, start with the basics.

Common causes are a wrong password, firewall rules, or MariaDB listening on the wrong interface because of bind-address.

Step 9: Prove it’s working with a write test

Status output can lie by omission.

Write something on db1 and confirm it lands on db2.

On db1:

sudo mariadb -e "CREATE DATABASE IF NOT EXISTS repl_test;"
sudo mariadb -e "CREATE TABLE IF NOT EXISTS repl_test.t (id INT PRIMARY KEY AUTO_INCREMENT, note VARCHAR(100), created TIMESTAMP DEFAULT CURRENT_TIMESTAMP);"
sudo mariadb -e "INSERT INTO repl_test.t(note) VALUES('hello from db1');"

On db2 (read-only is fine for SELECT):

sudo mariadb -e "SELECT * FROM repl_test.t;"

You should see the row. If you don’t, go back to SHOW SLAVE STATUS\G.

Read the error fields line by line.

Step 10: Basic monitoring checks you can automate

At minimum, alert on “replication stopped” and “replication lag too high.”

These quick checks work in cron or inside a monitoring agent.

Replica running check

sudo mariadb -e "SHOW SLAVE STATUS\G" | egrep "Slave_(IO|SQL)_Running|Seconds_Behind_Master|Last_SQL_Error|Last_IO_Error"

Lag check (simple threshold)

sudo mariadb -Nse "SHOW SLAVE STATUS\G" | awk -F": " '/Seconds_Behind_Master/ {print $2}'

For a typical web app, alert if it stays above ~30 seconds.

Brief spikes happen during backups, schema changes, or traffic bursts. The trend matters more than a single datapoint.

If you want dashboards, plug MariaDB metrics into a time-series stack.

This HostMyCode guide shows a production-friendly pattern (Docker + TLS + backups) that translates well to DB monitoring too: Deploy Grafana on a VPS With Docker, TLS, and Backups.

Step 11: Practice failover without guessing

Failover is a skill. You want a runbook you’ve already executed.

Here’s a safe, repeatable drill.

A. Put your app in maintenance mode (or pause writes)

Replication is asynchronous.

If the primary keeps accepting writes while you promote the replica, you can lose the last transactions.

For WordPress, enable maintenance mode briefly. For custom apps, block write traffic at the load balancer or app layer.

B. Confirm the replica has caught up

On db2:

sudo mariadb -e "SHOW SLAVE STATUS\G" | egrep "Seconds_Behind_Master|Slave_SQL_Running|Slave_IO_Running"

Wait until Seconds_Behind_Master is 0 (or as close as your workload allows).

C. Stop replication and promote db2

On db2:

sudo mariadb

STOP SLAVE;
RESET SLAVE ALL;

SET GLOBAL read_only = 0;
SET GLOBAL super_read_only = 0;

Verify it accepts writes now:

sudo mariadb -e "INSERT INTO repl_test.t(note) VALUES('db2 promoted');"
sudo mariadb -e "SELECT * FROM repl_test.t ORDER BY id DESC LIMIT 2;"

D. Point your application to the new primary

Update your app’s database host from db1 to db2.

• For apps using environment variables, change DB_HOST.
• For WordPress, change DB_HOST in wp-config.php.

Tip: If you use a DNS name like db.example.com, you can fail over by updating an A record.

Keep TTL low (30–60 seconds) so clients move quickly.

For a clean DNS change workflow, this companion tutorial is useful: Migrate a Domain to Cloudflare DNS Without Downtime.

E. Optional: rebuild the old primary as a replica

When db1 comes back, don’t just start MariaDB and hope it “falls into line.”

db1 may contain writes the replica never saw. That’s how split-brain happens.

The safe path is:

1. Stop MariaDB on db1.
2. Wipe data directory (or restore from a fresh snapshot).
3. Bootstrap db1 from the new primary (db2) and configure replication in reverse.

Step 12: Common replication errors and fast fixes

Replica can’t connect (Slave_IO_Running: No)

• Check firewall rules on db1 allow db2 to port 3306.
• Verify MariaDB is listening: ss -lntp | grep 3306
• Confirm the replication user host matches: 'repl'@'10.10.0.12' is not the same as 'repl'@'%'.

SQL thread stopped (Slave_SQL_Running: No)

Run:

sudo mariadb -e "SHOW SLAVE STATUS\G" | egrep "Last_SQL_Error|Last_Errno|Relay_Log_File|Relay_Log_Pos"

The usual culprits are schema drift and “quick fixes” applied directly on the replica.

Fix the underlying issue first.

Don’t skip errors unless you fully understand the event and have documented it.

Replication lag grows during traffic spikes

• Watch disk I/O first (iostat -x 1 or node exporter graphs).
• Check slow queries on the primary and optimize the hot path.
• Consider moving reads to the replica for reporting/search pages, but keep consistency expectations clear.

Where MariaDB replication on VPS fits in a real hosting stack

A common layout for smaller production environments looks like this:

• 1–2 web/app VPS nodes behind a reverse proxy (Nginx/Caddy/Traefik)
• 2 database VPS nodes (primary + standby replica)
• Offsite backups with tested restores

Once replication is in place, the next failure point is usually the application during maintenance.

For WordPress, predictable file permissions and reliable cron behavior prevent a lot of “it broke during the switch” incidents: WordPress File Permissions Fix on a VPS and Fix WordPress Cron Not Running on a VPS.

Summary: your go-live checklist

• Primary has binlogs enabled, ROW format, unique server_id.
• Replica has unique server_id, is read-only, replication started, and passes a real data test.
• Firewall allows 3306 only between nodes (and app servers if needed).
• You’ve rehearsed failover: stop writes, confirm caught up, promote replica, repoint app.
• You still run backups and test restores (replication is not a backup).

FAQ

Is MariaDB replication on VPS enough for “no downtime”?

It reduces downtime, but it doesn’t remove it.

You still need a failover method (manual runbook, scripts, or a proxy layer) and a plan for brief write pauses during promotion.

Will I lose data during failover?

Possibly, because standard replication is asynchronous. You reduce risk by pausing writes, waiting for Seconds_Behind_Master to hit 0, then promoting.

Should I allow my app to read from the replica?

Yes for analytics, reports, and search pages that tolerate slight lag.

Avoid reading “just-written” data from the replica unless you handle consistency in your app.

Do I still need backups if I have replication?

Yes. Replication copies deletes, corruption, and bad migrations. Keep offsite backups and test restores on a schedule.

Can I run this setup on a dedicated server instead of two VPS nodes?

You can, but it defeats the point if both nodes share the same hardware failure domain.

For real availability, separate nodes—two VPS instances or two dedicated servers—are the safer design.