MariaDB Galera Cluster — Backup & Restore Guide

[Amey Bhargave]

================================================================================
                MariaDB Galera Cluster — Backup Restore Guide                 
                Step-by-Step Recovery Procedure for Ubuntu VMs               
================================================================================

================================================================================
OVERVIEW
================================================================================
This guide explains how to restore a MariaDB Galera Cluster backup to a fresh Ubuntu VM. The backup is stored as an encrypted ZIP file in Azure Blob Storage.

--- Backup Details ---
Backup tool: mariabackup with Galera support
Compression: ZIP with password encryption
Storage: Azure Blob Storage
Retention: 7 daily backups
Original DB version: MariaDB 10.8.8

--- What This Guide Covers ---
- Preparing a fresh Ubuntu VM
- Mounting and using a secondary data disk
- Installing MariaDB 10.11
- Installing phpMyAdmin
- Extracting backup ZIP
- Disk space planning
- Restore using mariabackup
- Verification
- Troubleshooting

[!] WARNING: Never run this on a production server.

================================================================================
PREREQUISITES
================================================================================
* Ubuntu VM (20.04 / 22.04)
* 512GB data disk
* Backup ZIP file
* Root/sudo access
* Backup password

================================================================================
SECTION 1: PREPARE THE VM
================================================================================

--- Step 1.1 — Check disks ---

Code:

lsblk

--- Step 1.2 — Check filesystem ---

Code:

file -s /dev/sda1

If empty:

Code:

mkfs.ext4 /dev/sda1

--- Step 1.3 — Mount disk ---

Code:

mkdir -p /mnt/data
mount /dev/sda1 /mnt/data
df -h

--- Step 1.4 — Make persistent ---

Code:

echo '/dev/sda1 /mnt/data ext4 defaults 0 2' >> /etc/fstab
cat /etc/fstab

--- Step 1.5 — Check files ---

Code:

ls -lh /mnt/data/

--- Step 1.6 — Cleanup OS disk ---

Code:

apt clean
apt autoremove -y

================================================================================
SECTION 2: INSTALL MARIADB
================================================================================

--- Step 2.1 ---

Code:

apt update && apt upgrade -y

--- Step 2.2 ---

Code:

apt install curl wget unzip software-properties-common -y

--- Step 2.3 ---

Code:

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

--- Step 2.4 ---

Code:

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

--- Step 2.5 ---

Code:

mariadb --version
systemctl status mariadb

================================================================================
SECTION 3: INSTALL PHPMYADMIN (OPTIONAL)
================================================================================

--- Step 3.1 ---

Code:

apt install apache2 -y

--- Step 3.2 ---

Code:

apt install phpmyadmin -y

--- Step 3.3 ---

Code:

systemctl status apache2

--- Step 3.4 ---
Open port 80 in Azure NSG

--- Step 3.5 ---

Code:

mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourPassword123';
FLUSH PRIVILEGES;
EXIT;

--- Step 3.6 ---

Code:

curl ifconfig.me

Access:
http://YOUR_PUBLIC_IP/phpmyadmin

================================================================================
SECTION 4: EXTRACT BACKUP
================================================================================

--- Step 4.1 ---

Code:

cd /mnt/data/24x7
ls -lh

--- Step 4.2 ---

Code:

df -h /mnt/data

--- Step 4.3 ---

Code:

unzip -P 'YOUR_PASSWORD' backup.zip -d /mnt/data/restored

--- Step 4.4 ---

Code:

watch -n 10 'du -sh /mnt/data/restored/'

--- Step 4.5 ---

Code:

ls -lh /mnt/data/restored/backup/mysql/

================================================================================
SECTION 5: DISK SPACE PLANNING
================================================================================

--- Step 5.1 ---

Code:

du -sh /mnt/data/restored/backup/mysql/

--- Step 5.2 ---

Code:

df -h /
df -h /var/lib/mysql

--- Option A (copy) ---

Code:

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

--- Option B (move) ---

Code:

mariabackup --move-back \
--target-dir=/mnt/data/restored/backup/mysql/ \
--datadir=/var/lib/mysql/

--- Option C (recommended) ---

Code:

mkdir -p /mnt/data/mysql
chown mysql:mysql /mnt/data/mysql

Edit config:

Code:

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

Set:

Code:

datadir = /mnt/data/mysql

================================================================================
SECTION 6: RESTORE DATABASE
================================================================================

--- Step 6.1 ---

Code:

systemctl stop mariadb

--- Step 6.2 ---

Code:

rm -rf /var/lib/mysql/*

--- Step 6.3 ---

Code:

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

--- Step 6.4 ---

Code:

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

--- Step 6.5 ---

Code:

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

Add:

Code:

[mariadb]
wsrep_on=OFF

--- Step 6.6 ---

Code:

systemctl start mariadb

================================================================================
SECTION 7: VERIFY RESTORE
================================================================================

--- Step 7.1 ---

Code:

mysql -u root -e "SHOW DATABASES;"

--- Step 7.2 ---

Code:

SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_NAME LIKE '%transaction%';

================================================================================
SECTION 8: SHUTDOWN
================================================================================

Code:

systemctl stop mariadb
systemctl stop apache2
shutdown -h now

================================================================================
SECTION 9: TROUBLESHOOTING
================================================================================

--- MariaDB not starting ---

Code:

journalctl -xe | grep mariadb
tail -100 /var/log/mysql/error.log

Fix:
- chown -R mysql:mysql /var/lib/mysql/
- wsrep_on=OFF
- re-run restore

--- Disk full ---

Code:

df -h

Fix:
- use --move-back
- use /mnt/data/mysql

--- Unzip error ---

Code:

unzip -P 'PASSWORD' file.zip

--- phpMyAdmin issue ---

Code:

systemctl status apache2
curl ifconfig.me

================================================================================
QUICK REFERENCE
================================================================================

Data disk: /dev/sda1
Mount: /mnt/data
Backup: /mnt/data/24x7/
Extract: /mnt/data/restored/
Datadir: /var/lib/mysql OR /mnt/data/mysql
MariaDB: 10.11