Cara Backup dan Restore MariaDB dengan Mariabackup: Hot Backup untuk Production

· Februari 3, 2026 · 8 menit baca

Untuk database production yang harus selalu available, cold backup dengan mysqldump seringkali tidak bisa diterima. Mariabackup adalah fork dari Percona XtraBackup yang memungkinkan hot backup tanpa locking tabel. Artikel ini akan membahas setup Mariabackup yang optimal.

Keunggulan Mariabackup vs Mysqldump

Fitur	Mysqldump	Mariabackup
Backup Type	Logical (SQL)	Physical (binary)
Locking	Ya (FLUSH TABLES WITH READ LOCK)	Tidak (hot backup)
Restore Speed	Lambat (hours untuk large DB)	Cepat (copy files)
Size	Larger (text format)	Smaller (compressed)
Incremental	Tidak	Ya
Compression	Manual	Built-in
Point-in-Time Recovery	Terbatas	Ya

Instalasi Mariabackup

1. Install dari Repository

Ubuntu/Debian:

# Install mariadb-backup package sudo apt update sudo apt install mariadb-backup -y Verifikasi instalasi

which mariabackup mariabackup --version

CentOS/RHEL:

sudo yum install MariaDB-backup -y
# atau
sudo dnf install MariaDB-backup -y

2. Buat User Backup

-- Login ke MariaDB
mysql -u root -p
-- Buat user khusus backup
CREATE USER 'mariabackup'@'localhost' IDENTIFIED BY 'Backup#Pass123!';
-- Grant privileges
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, SUPER ON 
. TO 'mariabackup'@'localhost';
GRANT PROCESS ON 
. TO 'mariabackup'@'localhost';
GRANT SELECT ON mysql.user TO 'mariabackup'@'localhost';
FLUSH PRIVILEGES;

Verifikasi privileges:

SHOW GRANTS FOR 'mariabackup'@'localhost';

Full Backup dengan Mariabackup

1. Full Backup

# Setup direktori backup sudo mkdir -p /backup/mariadb sudo chown -R mysql:mysql /backup/mariadb Jalankan full backup mariabackup --backup \ --target-dir=/backup/mariadb/full-$(date +%Y%m%d-%H%M%S) \ --user=mariabackup \ --password='Backup#Pass123!' \ --databases="app_database" \ --compress Atau backup semua databases

mariabackup --backup \ --target-dir=/backup/mariadb/full-$(date +%Y%m%d-%H%M%S) \ --user=mariabackup \ --password='Backup#Pass123!' \ --compress

2. Prepare Backup

Sebelum restore, backup harus diprepare:

# Prepare full backup mariabackup --prepare \ --target-dir=/backup/mariadb/full-20260115-020000 Jika menggunakan compression

mariabackup --prepare \ --target-dir=/backup/mariadb/full-20260115-020000 \ --decompress

3. Verifikasi Backup

# Cek file backup ls -lh /backup/mariadb/full-*/Cek log backup

cat /backup/mariadb/full-*/xtrabackup_info

Incremental Backup

1. First Incremental Backup

# Full backup (base)
mariabackup --backup \
    --target-dir=/backup/mariadb/base \
    --user=mariabackup \
    --password='Backup#Pass123!'
Incremental backup (1)
mariabackup --backup \
--target-dir=/backup/mariadb/inc1 \
--incremental-basedir=/backup/mariadb/base \
--user=mariabackup \
--password='Backup#Pass123!'

2. Second Incremental Backup

# Incremental backup (2)
mariabackup --backup \
    --target-dir=/backup/mariadb/inc2 \
    --incremental-basedir=/backup/mariadb/inc1 \
    --user=mariabackup \
    --password='Backup#Pass123!'

3. Prepare Incremental Backups

# Prepare base mariabackup --prepare --apply-log-only \ --target-dir=/backup/mariadb/base Apply incremental 1 mariabackup --prepare --apply-log-only \ --target-dir=/backup/mariadb/base \ --incremental-dir=/backup/mariadb/inc1 Apply incremental 2

mariabackup --prepare \ --target-dir=/backup/mariadb/base \ --incremental-dir=/backup/mariadb/inc2

Restore dari Mariabackup

1. Full Restore

# Stop MariaDB sudo systemctl stop mariadb Backup data lama (jika perlu) sudo mv /var/lib/mysql /var/lib/mysql-old Restore dari backup mariabackup --copy-back \ --target-dir=/backup/mariadb/full-20260115-020000 \ --datadir=/var/lib/mysql Set permission sudo chown -R mysql:mysql /var/lib/mysql sudo chmod -R 750 /var/lib/mysql Start MariaDB

sudo systemctl start mariadb

2. Partial Restore (Single Database)

# Export single database dari backup mariabackup --export \ --target-dir=/backup/mariadb/full-20260115-020000 \ --databases="app_database" Import ke running server mysql -u root -p -e "CREATE DATABASE app_database_restore;"

mariabackup --copy-back \ --target-dir=/backup/mariadb/full-20260115-020000 \ --databases="app_database" \ --datadir=/var/lib/mysql/app_database_restore

3. Point-in-Time Recovery

# Siapkan base backup mariabackup --prepare --apply-log-only \ --target-dir=/backup/mariadb/base Apply binlog ke point-in-time

mysqlbinlog --start-datetime="2026-01-15 10:00:00" \ --stop-datetime="2026-01-15 12:00:00" \ /var/log/mysql/mariadb-bin.000001 | mysql -u root -p

Automated Backup Script

1. Full Backup Script

sudo nano /usr/local/bin/mariadb_full_backup.sh

#!/bin/bash
Konfigurasi
BACKUP_USER="mariabackup"
BACKUP_PASS="Backup#Pass123!"
BACKUP_DIR="/backup/mariadb"
DATE=$(date +%Y%m%d-%H%M%S)
RETENTION_DAYS=7
LOG_FILE="/var/log/mariabackup.log"
Logging function
log() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a $LOG_FILE
}
Buat direktori
BACKUP_PATH="$BACKUP_DIR/full-$DATE"
mkdir -p $BACKUP_PATH
log "=== Memulai full backup ==="
log "Backup path: $BACKUP_PATH"
Jalankan backup
mariabackup --backup \
--target-dir=$BACKUP_PATH \
--user=$BACKUP_USER \
--password=$BACKUP_PASS \
--compress \
--parallel=4 \
2>&1 | tee -a $LOG_FILE
BACKUP_EXIT_CODE=${PIPESTATUS[0]}
if [ $BACKUP_EXIT_CODE -eq 0 ]; then
log "Backup berhasil"
# Get backup size
BACKUP_SIZE=$(du -sh $BACKUP_PATH | cut -f1)
log &quot;Backup size: $BACKUP_SIZE&quot;

# Prepare backup untuk restore cepat
log &quot;Memprepare backup...&quot;
mariabackup --prepare --target-dir=$BACKUP_PATH 2&gt;&amp;1 | tee -a $LOG_FILE

log &quot;Prepare selesai&quot;
else
log "ERROR: Backup gagal dengan exit code $BACKUP_EXIT_CODE"
exit 1
fi
Hapus backup lama
log "Membersihkan backup lama (> $RETENTION_DAYS hari)"
find $BACKUP_DIR -name "full-*" -type d -mtime +$RETENTION_DAYS -exec rm -rf {} + 2>/dev/nulllog "=== Full backup selesai ==="
2. Incremental Backup Script
sudo nano /usr/local/bin/mariadb_incremental_backup.sh

#!/bin/bash
BACKUP_USER="mariabackup"
BACKUP_PASS="Backup#Pass123!"
BACKUP_DIR="/backup/mariadb"
DATE=$(date +%Y%m%d-%H%M%S)
LOG_FILE="/var/log/mariabackup.log"
log() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a $LOG_FILE
}
Cari base backup terakhir
BASE_BACKUP=$(find $BACKUP_DIR -name "full-*" -type d -mtime -1 | sort | tail -1)
if [ -z "$BASE_BACKUP" ]; then
log "ERROR: Tidak ada base backup dalam 24 jam terakhir"
exit 1
fi
log "Base backup: $BASE_BACKUP"
Buat direktori incremental
INC_PATH="$BACKUP_DIR/inc-$DATE"
mkdir -p $INC_PATH
log "=== Memulai incremental backup ==="
log "Incremental path: $INC_PATH"
Jalankan incremental backup
mariabackup --backup \
--target-dir=$INC_PATH \
--incremental-basedir=$BASE_BACKUP \
--user=$BACKUP_USER \
--password=$BACKUP_PASS \
--compress \
2>&1 | tee -a $LOG_FILE
if [ $? -eq 0 ]; then
INC_SIZE=$(du -sh $INC_PATH | cut -f1)
log "Incremental backup berhasil: $INC_SIZE"
else
log "ERROR: Incremental backup gagal"
exit 1
fi
Hapus incremental lama
find $BACKUP_DIR -name "inc-*" -type d -mtime +3 -exec rm -rf {} + 2>/dev/null
log "=== Incremental backup selesai ==="
3. Setup Cron
sudo chmod +x /usr/local/bin/mariadb_full_backup.sh
sudo chmod +x /usr/local/bin/mariadb_incremental_backup.sh
sudo crontab -e
# Full backup setiap hari jam 2 pagi
0 2 * * * /usr/local/bin/mariadb_full_backup.sh
Incremental backup setiap 6 jam
0 
/6  
  /usr/local/bin/mariadb_incremental_backup.sh
Streaming Backup ke Remote
1. Backup via SSH Stream
# Backup dan stream ke remote server
mariabackup --backup \
    --stream=xbstream \
    --user=mariabackup \
    --password='Backup#Pass123!' | \
    ssh user@remote-server "xbstream -x -C /backup/mariadb/"
Atau dengan compression
mariabackup --backup \
--stream=xbstream \
--user=mariabackup \
--password='Backup#Pass123!' | \
gzip -c | \
ssh user@remote-server "cat > /backup/mariadb/backup.xbstream.gz"
2. Restore dari Stream
# Extract di remote
ssh user@remote-server "cat /backup/mariadb/backup.xbstream.gz" | \
    gunzip -c | \
    xbstream -x -C /var/lib/mysql
Cloud Backup dengan Mariabackup
1. Backup ke S3
# Install awscli
sudo apt install awscli -y
aws configure
Backup dan upload ke S3
mariabackup --backup \
--target-dir=/tmp/mariadb-backup \
--user=mariabackup \
--password='Backup#Pass123!' \
--compress
Upload ke S3
aws s3 sync /tmp/mariadb-backup s3://my-bucket/mariadb-backups/full-$(date +%Y%m%d)/
Hapus local backup
rm -rf /tmp/mariadb-backup
2. Restore dari S3
# Download dari S3
aws s3 sync s3://my-bucket/mariadb-backups/full-20260115/ /restore/mariadb/
Prepare dan restore
mariabackup --prepare --target-dir=/restore/mariadb/
mariabackup --copy-back --target-dir=/restore/mariadb/ --datadir=/var/lib/mysql
Monitoring dan Verifikasi
1. Health Check Script
sudo nano /usr/local/bin/check_mariabackup.sh

#!/bin/bash
BACKUP_DIR="/backup/mariadb"
ALERT_EMAIL="[email protected]"
MAX_AGE_HOURS=26
Cek backup terbaru
LATEST_BACKUP=$(find $BACKUP_DIR -name "full-
" -type d -mmin -$((MAX_AGE_HOURS  60)) | head -1)
if [ -z "$LATEST_BACKUP" ]; then
echo "WARNING: No backup in last $MAX_AGE_HOURS hours" | \
mail -s "[WARNING] MariaDB Backup Missing" $ALERT_EMAIL
exit 1
fi
Cek size backup
BACKUP_SIZE=$(du -sm $LATEST_BACKUP | cut -f1)
MIN_SIZE_MB=10
if [ $BACKUP_SIZE -lt $MIN_SIZE_MB ]; then
echo "WARNING: Backup too small: ${BACKUP_SIZE}MB" | \
mail -s "[WARNING] MariaDB Backup Size Issue" $ALERT_EMAIL
exit 1
fi
Cek log untuk error
if grep -q "ERROR" $LATEST_BACKUP/xtrabackup_logfile 2>/dev/null; then
echo "WARNING: Backup log contains errors" | \
mail -s "[WARNING] MariaDB Backup Error" $ALERT_EMAIL
exit 1
fi
echo "Backup health check: OK - $LATEST_BACKUP (${BACKUP_SIZE}MB)"
2. Test Restore Regularly
# Weekly test restore
0 6 * * 0 /usr/local/bin/test_mariabackup_restore.sh

#!/bin/bash
# Script test restore
TEST_DIR="/tmp/restore-test-$(date +%s)"
mkdir -p $TEST_DIR
LATEST=$(find /backup/mariadb -name "full-*" -type d | sort | tail -1)
mariabackup --copy-back --target-dir=$LATEST --datadir=$TEST_DIR
if [ $? -eq 0 ]; then
echo "Test restore successful: $LATEST"
rm -rf $TEST_DIR
else
echo "Test restore FAILED: $LATEST" | mail -s "[CRITICAL] Backup Restore Test Failed" [email protected]
fi
Kesimpulan
Mariabackup adalah tool esensial untuk database production:
– Hot backup tanpa locking
– Incremental backups untuk efisiensi
– Fast restore untuk disaster recovery
– Point-in-time recovery capability
Investasi waktu untuk setup Mariabackup akan membayar saat terjadi kecelakaan data.

Ditulis oleh

Hendra Wijaya

Artikel Sebelumnya A/B Testing untuk Meningkatkan AdSense Revenue 30-50% Artikel Selanjutnya Cara Membuat Instagram Carousel yang Viral dan High-Engagement

Cara Backup dan Restore MariaDB dengan Mariabackup: Hot Backup untuk Production

Keunggulan Mariabackup vs Mysqldump

Instalasi Mariabackup

1. Install dari Repository

Verifikasi instalasi

2. Buat User Backup

Full Backup dengan Mariabackup

1. Full Backup

Jalankan full backup

Atau backup semua databases

2. Prepare Backup

Jika menggunakan compression

3. Verifikasi Backup

Cek log backup

Incremental Backup

1. First Incremental Backup

Incremental backup (1)

2. Second Incremental Backup

3. Prepare Incremental Backups

Apply incremental 1

Apply incremental 2

Restore dari Mariabackup

1. Full Restore

Backup data lama (jika perlu)

Restore dari backup

Set permission

Start MariaDB

2. Partial Restore (Single Database)

Import ke running server

3. Point-in-Time Recovery

Apply binlog ke point-in-time

Automated Backup Script

1. Full Backup Script

Konfigurasi

Logging function

Buat direktori

Jalankan backup

Hapus backup lama

2. Incremental Backup Script

Cari base backup terakhir

Buat direktori incremental

Jalankan incremental backup

Hapus incremental lama

3. Setup Cron

Incremental backup setiap 6 jam

Streaming Backup ke Remote

1. Backup via SSH Stream

Atau dengan compression

2. Restore dari Stream

Cloud Backup dengan Mariabackup

1. Backup ke S3

Backup dan upload ke S3

Upload ke S3

Hapus local backup

2. Restore dari S3

Prepare dan restore

Monitoring dan Verifikasi

1. Health Check Script

Cek backup terbaru

Cek size backup

Cek log untuk error

2. Test Restore Regularly

Kesimpulan

Artikel Terkait

Tinggalkan Komentar