Disaster Recovery (DR) Replica Setup for MariaDB Galera Cluster

[aniket.pitre]

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