Disaster Recovery (DR) Replica Setup for MariaDB Galera Cluster

Disaster Recovery (DR) Replica Setup for MariaDB Galera Cluster - Printable Version

+- DevOps Discussion Forum (https://forums.geekssolutions.io)
+-- Forum: Cloud Computing (https://forums.geekssolutions.io/forumdisplay.php?fid=10)
+--- Forum: DevOps (https://forums.geekssolutions.io/forumdisplay.php?fid=14)
+--- Thread: Disaster Recovery (DR) Replica Setup for MariaDB Galera Cluster (/showthread.php?tid=21)

Disaster Recovery (DR) Replica Setup for MariaDB Galera Cluster - aniket.pitre - 04-07-2026

Purpose: Configure a standalone Disaster Recovery replica for a MariaDB Galera cluster to ensure data redundancy and failover capability.
Applies To: MariaDB 10.11 / Galera Cluster
Environment: Ubuntu 24.04

? Overview
This guide explains how to configure a Disaster Recovery (DR) replica node from an existing Galera cluster.
The DR node:

Replicates from a single Galera node
Does NOT join the cluster
Always remains read-only
Used for disaster recovery and reporting

⚙️ Key Characteristics

Replicates from one Galera node
Never becomes a master
Always read-only
Standalone replica (not part of cluster)

? Prerequisites

Ubuntu 24.04 VM
Disk space ≥ 2× database size
Network access to Galera node (port 3306)
Backup storage access
Backup encryption password
Replication user credentials
Private IP of Galera node

? Phase 1 — VM Setup
Step 1.1 — Verify Disk

Code:
lsblk

df -h

Step 1.2 — Update System

Code:
apt update

apt upgrade -y

apt install curl wget software-properties-common -y

? Phase 2 — Install MariaDB
Step 2.1 — Add Repository

Code:
curl -LsS [url=https://downloads.mariadb.com/MariaDB/mariadb_repo_setup]https://downloads.mariadb.com/MariaDB/mariadb_repo_setup[/url]

| bash -s -- --mariadb-server-version="mariadb-10.11"

Step 2.2 — Install

Code:
apt update

apt install mariadb-server mariadb-backup -y

Step 2.3 — Verify

Code:
mariadb --version

systemctl status mariadb

⚙️ Phase 3 — Configure DR Node
Step 3.1 — Create Config File
/etc/mysql/mariadb.conf.d/99-dr-replica.cnf

Code:
[mysqld]

server_id = 100

log_bin = mysql-bin

binlog_format = ROW

relay_log = relay-bin

relay_log_recovery = ON

log_slave_updates = ON

read_only = ON

skip_slave_start = ON

Step 3.2 — Disable Galera
Edit:

Code:
/etc/mysql/mariadb.conf.d/50-server.cnf

Ensure:

Code:
[mariadb]

wsrep_on = OFF

Step 3.3 — Restart

Code:
systemctl restart mariadb

Step 3.4 — Verify

Code:
SHOW VARIABLES LIKE 'server_id';

SHOW VARIABLES LIKE 'wsrep_on';

SHOW VARIABLES LIKE 'read_only';

? Phase 4 — Verify Galera Source Node

Code:
SHOW VARIABLES LIKE 'log_bin';

SHOW VARIABLES LIKE 'binlog_format';

SHOW VARIABLES LIKE 'server_id';

SHOW MASTER STATUS\G

Grant Replication Permissions

Code:
GRANT REPLICATION SLAVE, BINLOG MONITOR ON [i].[/i]

TO 'replication_user'@'<dr_node_ip>';

FLUSH PRIVILEGES;

? Phase 5 — Download & Restore Backup
Download

Code:
az storage blob download

--account-name <storage_account>

--container-name

--name ".tar.zst.enc"

--file "/mnt/.tar.zst.enc"

--auth-mode login

Decrypt

Code:
openssl enc -d -aes-256-cbc -pbkdf2 -iter 200000

-in /mnt/.tar.zst.enc

-out /mnt/backup.tar.zst

-pass pass:''

Extract

Code:
cd /mnt

tar --use-compress-program=zstd -xf backup.tar.zst

Get Binlog Position

Code:
cat /mnt/xtrabackup_binlog_info

Example:

Code:
mysql-bin.002064 385

?️ Phase 6 — Restore Database
⚠️ WARNING: This overwrites existing data

Code:
systemctl stop mariadb

mv /var/lib/mysql /mnt/mysql_old

mkdir -p /var/lib/mysql

Prepare Backup

Code:
mariabackup --prepare --target-dir=/mnt/

Restore

Code:
mariabackup --copy-back

--target-dir=/mnt/

--datadir=/var/lib/mysql/

Fix Permissions

Code:
chown -R mysql:mysql /var/lib/mysql/

Start DB

Code:
systemctl start mariadb

? Phase 7 — Setup Replication

Code:
CHANGE MASTER TO

MASTER_HOST='<galera_node_ip>',

MASTER_USER='<replication_user>',

MASTER_PASSWORD='',

MASTER_PORT=3306,

MASTER_LOG_FILE='<binlog_file>',

MASTER_LOG_POS=<binlog_position>;

Code:
START SLAVE;

Verify

Code:
SHOW SLAVE STATUS\G

❗ Phase 8 — Common Errors
Error 1032

Code:
STOP SLAVE;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

START SLAVE;

Error 1526

Missing partition → add required partitions

Error 1146

Missing temporary table

✅ Phase 9 — Final Verification

Code:
SHOW VARIABLES LIKE 'server_id';

SHOW VARIABLES LIKE 'read_only';

SHOW VARIABLES LIKE 'wsrep_on';

SHOW SLAVE STATUS\G

? Key Rules

✔ Unique server_id
✔ wsrep must be OFF
✔ Always read_only = ON
✔ Use correct binlog position from backup
✔ Replicate from only ONE Galera node
✔ Do NOT leave error skipping enabled