MariaDB Galera DR Replica Setup (Step-by-Step Guide)

Tutorials & Guides
, , , ,
1

Overview

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

DR Node Behavior

  • Replicates from a single Galera node

  • Does NOT join the cluster

  • Always remains read-only

  • Used for disaster recovery and reporting

Key Characteristics

  • Replication from a single Galera node

  • Never acts as a master

  • Always runs in read-only mode

  • Fully standalone (not part of Galera 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

1. Verify Disk

lsblk
df -h

2. Update System

apt update
apt upgrade -y
apt install curl wget software-properties-common -y

Phase 2 — Install MariaDB

1. Add Repository

curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup \
| bash -s -- --mariadb-server-version="mariadb-10.11"

2. Install Packages

apt update
apt install mariadb-server mariadb-backup -y

3. Verify Installation

mariadb --version
systemctl status mariadb

Phase 3 — Configure DR Node

1. Create Configuration File

File:

/etc/mysql/mariadb.conf.d/99-dr-replica.cnf


[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

2. Disable Galera

Edit:

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


[mariadb]
wsrep_on = OFF

3. Restart Service

systemctl restart mariadb

4. Verify Configuration

SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'wsrep_on';
SHOW VARIABLES LIKE 'read_only';

Phase 4 — Verify Source (Galera Node)

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'server_id';
SHOW MASTER STATUS\G

Create Replication User

GRANT REPLICATION SLAVE, BINLOG MONITOR ON *.*
TO 'replication_user'@'<dr_node_ip>';
FLUSH PRIVILEGES;

Phase 5 — Download & Restore Backup

1. Download Backup

az storage blob download \
--account-name <storage_account> \
--container-name <container> \
--name "<backup>.tar.zst.enc" \
--file "/mnt/backup.tar.zst.enc" \
--auth-mode login

2. Decrypt Backup

openssl enc -d -aes-256-cbc -pbkdf2 -iter 200000 \
-in /mnt/backup.tar.zst.enc \
-out /mnt/backup.tar.zst \
-pass pass:<password>

3. Extract Backup

cd /mnt
tar --use-compress-program=zstd -xf backup.tar.zst

4. Get Binlog Position

cat /mnt/xtrabackup_binlog_info

Example:

mysql-bin.002064 385

Phase 6 — Restore Database

:warning: WARNING: This will overwrite existing data

systemctl stop mariadb
mv /var/lib/mysql /mnt/mysql_old
mkdir -p /var/lib/mysql

Prepare Backup

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

Restore Backup

mariabackup --copy-back \
--target-dir=/mnt/ \
--datadir=/var/lib/mysql/

Fix Permissions

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

Start Database

systemctl start mariadb

Phase 7 — Configure Replication

CHANGE MASTER TO
MASTER_HOST='<galera_node_ip>',
MASTER_USER='<replication_user>',
MASTER_PASSWORD='<password>',
MASTER_PORT=3306,
MASTER_LOG_FILE='<binlog_file>',
MASTER_LOG_POS=<binlog_position>;


START SLAVE;

Verify Replication

SHOW SLAVE STATUS\G

Phase 8 — Common Errors

Error 1032

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

Error 1526

  • Missing partition → create required partition

Error 1146

  • Missing temporary table

Phase 9 — Final Verification

SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'read_only';
SHOW VARIABLES LIKE 'wsrep_on';
SHOW SLAVE STATUS\G

Key Rules

  • :check_mark: Use unique server_id

  • :check_mark: wsrep_on must be OFF

  • :check_mark: Always keep read_only = ON

  • :check_mark: Use correct binlog position

  • :check_mark: Replicate from only ONE Galera node

  • :cross_mark: Do NOT leave skip counter enabled

Conclusion

This DR setup provides:

  • Reliable disaster recovery

  • Data redundancy

  • Safe reporting without impacting cluster performance