[MARIADB] BACKUP DATABASE USING MARIADB‐BACKUP - fourslickz/notes GitHub Wiki

MAIN SCRIPT

#!/bin/bash

# Konfigurasi
BACKUP_DIR="/backup/mariadb"
MYSQL_USER="root"
MYSQL_PASSWORD="yourpassword"
REMOTE_USER="user"      # User untuk server B
REMOTE_HOST="serverB"   # Hostname/IP server B
REMOTE_DIR="/backup/mariadb"  # Lokasi backup di server B
RETENTION_DAYS=7        # Simpan backup selama 7 hari

# Dapatkan tanggal dan waktu sekarang
DATE=$(date +%F)  # Format YYYY-MM-DD
HOUR=$(date +%H)  # Format HH

# Direktori backup lokal
FULL_BACKUP_DIR="$BACKUP_DIR/full-$DATE"
INC_BACKUP_DIR="$BACKUP_DIR/inc-$DATE-$HOUR"

# Jika waktu 00:00, lakukan full backup
if [ "$HOUR" == "00" ]; then
    echo "Melakukan full backup ke: $FULL_BACKUP_DIR"
    mariabackup --backup --target-dir="$FULL_BACKUP_DIR" --user="$MYSQL_USER" --password="$MYSQL_PASSWORD"
    
    # Transfer backup penuh ke server B
    echo "Mengirim full backup ke server B..."
    rsync -avz "$FULL_BACKUP_DIR" "$REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR/"

else
    # Cari backup penuh terbaru
    LATEST_FULL=$(ls -d $BACKUP_DIR/full-* 2>/dev/null | tail -n 1)

    if [ -z "$LATEST_FULL" ]; then
        echo "Tidak ada full backup ditemukan! Harus menjalankan full backup dulu."
        exit 1
    fi

    echo "Melakukan incremental backup ke: $INC_BACKUP_DIR"
    mariabackup --backup --target-dir="$INC_BACKUP_DIR" --incremental-basedir="$LATEST_FULL" --user="$MYSQL_USER" --password="$MYSQL_PASSWORD"

    # Transfer backup incremental ke server B
    echo "Mengirim incremental backup ke server B..."
    rsync -avz "$INC_BACKUP_DIR" "$REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR/"
fi

# 🔹 Rotasi Backup: Hapus backup lebih dari 7 hari
echo "Menghapus backup yang lebih tua dari $RETENTION_DAYS hari..."
find "$BACKUP_DIR" -type d -name "full-*" -mtime +$RETENTION_DAYS -exec rm -rf {} \;
find "$BACKUP_DIR" -type d -name "inc-*" -mtime +$RETENTION_DAYS -exec rm -rf {} \;

# 🔹 Hapus juga di server B
ssh "$REMOTE_USER@$REMOTE_HOST" "find $REMOTE_DIR -type d -name 'full-*' -mtime +$RETENTION_DAYS -exec rm -rf {} \;"
ssh "$REMOTE_USER@$REMOTE_HOST" "find $REMOTE_DIR -type d -name 'inc-*' -mtime +$RETENTION_DAYS -exec rm -rf {} \;"

echo "Backup selesai: $(date)"

0 * * * * /usr/local/bin/backup_mariadb.sh >> /var/log/mariadb_backup.log 2>&1

1. Masuk ke Server B dan cari backup terbaru:

ls -ltr /backup/mariadb/

2. Persiapkan sebelum restore:

mariabackup --prepare --apply-log-only --target-dir=/backup/mariadb/full-YYYY-MM-DD/
mariabackup --prepare --apply-log-only --target-dir=/backup/mariadb/full-YYYY-MM-DD/ --incremental-dir=/backup/mariadb/inc-YYYY-MM-DD-HH/
mariabackup --prepare --target-dir=/backup/mariadb/full-YYYY-MM-DD/

3. Restore ke direktori MariaDB:

systemctl stop mariadb
mv /var/lib/mysql /var/lib/mysql_old
mkdir /var/lib/mysql
mariabackup --copy-back --target-dir=/backup/mariadb/full-YYYY-MM-DD/
chown -R mysql:mysql /var/lib/mysql
systemctl start mariadb