Salin dan Bagikan
MariaDB Backup Database Administration
Cara Backup dan Restore MariaDB dengan Mariabackup: Hot Backup untuk Production - Panduan lengkap menggunakan Mariabackup untuk hot backup dan restore MariaDB tanpa locking tabel …

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

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

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

FiturMysqldumpMariabackup
Backup TypeLogical (SQL)Physical (binary)
LockingYa (FLUSH TABLES WITH READ LOCK)Tidak (hot backup)
Restore SpeedLambat (hours untuk large DB)Cepat (copy files)
SizeLarger (text format)Smaller (compressed)
IncrementalTidakYa
CompressionManualBuilt-in
Point-in-Time RecoveryTerbatasYa

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 "Backup size: $BACKUP_SIZE"
    
    # Prepare backup untuk restore cepat
    log "Memprepare backup..."
    mariabackup --prepare --target-dir=$BACKUP_PATH 2>&1 | tee -a $LOG_FILE
    
    log "Prepare selesai"
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/null

log "=== 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="admin@example.com"
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" admin@example.com
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.

Artikel Terkait

Link Postingan : https://www.tirinfo.com/cara-backup-restore-mariadb-mariabackup/

Hendra WIjaya
Tirinfo
6 minutes.
3 February 2026