Backup Otomatis MariaDB dengan Cron Job dan Script Shell: Setup Production

Kehilangan data adalah mimpi buruk bagi setiap database administrator. Setelah mengalami kecelakaan hardware yang menyebabkan hilangnya 3 hari data, saya mempelajari pentingnya backup otomatis yang reliable. Artikel ini akan membantu Anda setup backup otomatis MariaDB yang robust.

Kenapa Backup Otomatis?

Manual backup sering terlupakan karena:
– Sibuk dengan tugas lain
– Lupa schedule
– Human error
– Tidak konsisten

Otomatisasi memastikan:
– Backup berjalan sesuai jadwal
– Tidak ada human error
– Konsisten dan terdokumentasi
– Monitoring yang mudah

Persiapan Environment

1. Buat User Backup

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupPass123!';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

2. Setup Direktori Backup

# Buat direktori
sudo mkdir -p /backup/mariadb/{daily,weekly,monthly}

# Set permission
sudo chown -R mysql:mysql /backup/mariadb
sudo chmod -R 750 /backup/mariadb

3. Install Tools Pendukung

# Install mailutils untuk notifikasi
sudo apt install mailutils bc

# Install rclone untuk cloud backup (opsional)
curl https://rclone.org/install.sh | sudo bash

Script Backup Harian

1. Buat Script Backup

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

#!/bin/bash

# Konfigurasi
DB_USER="backup_user"
DB_PASS="BackupPass123!"
BACKUP_DIR="/backup/mariadb/daily"
DATE=$(date +%Y%m%d_%H%M%S)
DAY_OF_WEEK=$(date +%u)
RETENTION_DAYS=7
ADMIN_EMAIL="admin@example.com"
LOG_FILE="/var/log/mariadb_backup.log"

# Fungsi logging
log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" >> $LOG_FILE
}

# Fungsi kirim email
send_email() {
    local subject=$1
    local body=$2
    echo "$body" | mail -s "$subject" $ADMIN_EMAIL
}

log_message "=== Memulai backup harian ==="

# Cek koneksi database
if ! mysql -u $DB_USER -p$DB_PASS -e "SELECT 1;" &>/dev/null; then
    log_message "ERROR: Tidak bisa terhubung ke database"
    send_email "[URGENT] Backup MariaDB Gagal" "Gagal terhubung ke database pada $(date)"
    exit 1
fi

log_message "Koneksi database OK"

# Backup setiap database secara individual
DATABASES=$(mysql -u $DB_USER -p$DB_PASS -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|sys)")

for DB in $DATABASES; do
    log_message "Membackup database: $DB"

    mysqldump -u $DB_USER -p$DB_PASS \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        --hex-blob \
        --lock-tables=false \
        $DB > $BACKUP_DIR/${DB}_$DATE.sql

    if [ $? -eq 0 ]; then
        # Kompresi
        gzip -f $BACKUP_DIR/${DB}_$DATE.sql
        FILE_SIZE=$(du -h $BACKUP_DIR/${DB}_$DATE.sql.gz | cut -f1)
        log_message "Backup $DB berhasil: ${DB}_$DATE.sql.gz ($FILE_SIZE)"
    else
        log_message "ERROR: Backup $DB gagal"
        send_email "[WARNING] Backup Database $DB Gagal" "Backup database $DB gagal pada $(date)"
    fi
done

# Backup full (semua database)
log_message "Membackup full database"
mysqldump -u $DB_USER -p$DB_PASS \
    --all-databases \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --hex-blob > $BACKUP_DIR/full_backup_$DATE.sql

if [ $? -eq 0 ]; then
    gzip -f $BACKUP_DIR/full_backup_$DATE.sql
    FULL_SIZE=$(du -h $BACKUP_DIR/full_backup_$DATE.sql.gz | cut -f1)
    log_message "Full backup berhasil: full_backup_$DATE.sql.gz ($FULL_SIZE)"
else
    log_message "ERROR: Full backup gagal"
    send_email "[URGENT] Full Backup Gagal" "Full backup MariaDB gagal pada $(date)"
fi

# Rotasi backup (hapus file lama)
log_message "Membersihkan backup lama (> $RETENTION_DAYS hari)"
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

# Hitung total size
TOTAL_SIZE=$(du -sh $BACKUP_DIR | cut -f1)
log_message "Backup harian selesai. Total size: $TOTAL_SIZE"

# Kirim summary email (hanya hari Senin)
if [ $DAY_OF_WEEK -eq 1 ]; then
    SUMMARY=$(cat <<EOF
Ringkasan Backup MariaDB - $(date +%Y-%m-%d)

Status: SUKSES
Total Size: $TOTAL_SIZE
Backup File:
$(ls -lh $BACKUP_DIR/*.sql.gz | tail -5)

Log lengkap: $LOG_FILE
EOF
)
    send_email "[INFO] Ringkasan Backup Mingguan MariaDB" "$SUMMARY"
fi

log_message "=== Backup harian selesai ==="
exit 0

2. Beri Izin Eksekusi

sudo chmod +x /usr/local/bin/mariadb_daily_backup.sh

3. Test Manual

sudo /usr/local/bin/mariadb_daily_backup.sh

Cek log:

tail -f /var/log/mariadb_backup.log

Setup Cron Job

1. Edit Crontab

sudo crontab -e

2. Tambahkan Job Backup

# Backup harian jam 2 pagi
0 2 * * * /usr/local/bin/mariadb_daily_backup.sh

# Backup mingguan setiap Minggu jam 3 pagi
0 3 * * 0 /usr/local/bin/mariadb_weekly_backup.sh

# Backup bulanan setiap tanggal 1 jam 4 pagi
0 4 1 * * /usr/local/bin/mariadb_monthly_backup.sh

3. Verifikasi Cron

sudo crontab -l
sudo systemctl status cron

Script Backup Mingguan

1. Buat Script

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

#!/bin/bash

DB_USER="backup_user"
DB_PASS="BackupPass123!"
BACKUP_DIR="/backup/mariadb/weekly"
DATE=$(date +%Y%m%d)
RETENTION_WEEKS=4

mkdir -p $BACKUP_DIR

# Full backup dengan routines dan events
mysqldump -u $DB_USER -p$DB_PASS \
    --all-databases \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --master-data=2 \
    --hex-blob \
    --databases $(mysql -u $DB_USER -p$DB_PASS -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|sys)" | tr '\n' ' ') \
    | gzip > $BACKUP_DIR/weekly_full_$DATE.sql.gz

# Hapus backup lama (> 4 minggu)
find $BACKUP_DIR -name "*.sql.gz" -mtime +$((RETENTION_WEEKS * 7)) -delete

echo "[$(date)] Weekly backup completed: weekly_full_$DATE.sql.gz" >> /var/log/mariadb_backup.log

2. Beri Izin

sudo chmod +x /usr/local/bin/mariadb_weekly_backup.sh

Script Backup Bulanan

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

#!/bin/bash

DB_USER="backup_user"
DB_PASS="BackupPass123!"
BACKUP_DIR="/backup/mariadb/monthly"
DATE=$(date +%Y%m)
RETENTION_MONTHS=6

mkdir -p $BACKUP_DIR

# Backup dengan struktur lengkap
mysqldump -u $DB_USER -p$DB_PASS \
    --all-databases \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --hex-blob \
    --complete-insert \
    --extended-insert=FALSE \
    --databases $(mysql -u $DB_USER -p$DB_PASS -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|sys)" | tr '\n' ' ') \
    | gzip > $BACKUP_DIR/monthly_archive_$DATE.sql.gz

# Hapus backup lama (> 6 bulan)
find $BACKUP_DIR -name "*.sql.gz" -mtime +$((RETENTION_MONTHS * 30)) -delete

echo "[$(date)] Monthly backup completed: monthly_archive_$DATE.sql.gz" >> /var/log/mariadb_backup.log

Cloud Backup dengan Rclone

1. Konfigurasi Rclone

rclone config

Pilih cloud provider (Google Drive, AWS S3, dll)

2. Script Sync ke Cloud

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

#!/bin/bash

LOCAL_DIR="/backup/mariadb"
REMOTE_NAME="gdrive"
REMOTE_DIR="backups/mariadb"

# Sync ke cloud
rclone sync $LOCAL_DIR $REMOTE_NAME:$REMOTE_DIR \
    --include "*.sql.gz" \
    --max-age 7d \
    --log-file=/var/log/mariadb_cloud_sync.log

if [ $? -eq 0 ]; then
    echo "[$(date)] Cloud sync berhasil" >> /var/log/mariadb_backup.log
else
    echo "[$(date)] ERROR: Cloud sync gagal" >> /var/log/mariadb_backup.log
    echo "Cloud sync gagal" | mail -s "[WARNING] MariaDB Cloud Backup Gagal" admin@example.com
fi

3. Tambahkan ke Cron

0 6 * * * /usr/local/bin/mariadb_cloud_sync.sh

Monitoring dan Alerting

1. Script Health Check

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

#!/bin/bash

BACKUP_DIR="/backup/mariadb/daily"
LOG_FILE="/var/log/mariadb_backup.log"
ALERT_EMAIL="admin@example.com"

# Cek backup terakhir
LAST_BACKUP=$(find $BACKUP_DIR -name "*.sql.gz" -mtime -1 | head -1)

if [ -z "$LAST_BACKUP" ]; then
    echo "[URGENT] Tidak ada backup dalam 24 jam terakhir!" | mail -s "[CRITICAL] MariaDB Backup Missing" $ALERT_EMAIL
    exit 1
fi

# Cek ukuran backup
BACKUP_SIZE=$(stat -c%s "$LAST_BACKUP")
MIN_SIZE=1024  # Minimal 1KB

if [ $BACKUP_SIZE -lt $MIN_SIZE ]; then
    echo "[WARNING] Backup terlalu kecil: $LAST_BACKUP ($BACKUP_SIZE bytes)" | mail -s "[WARNING] MariaDB Backup Size Issue" $ALERT_EMAIL
    exit 1
fi

echo "Backup health check: OK - $LAST_BACKUP ($(du -h $LAST_BACKUP | cut -f1))"

2. Setup Check Cron

0 9 * * * /usr/local/bin/mariadb_backup_check.sh

Restore dari Backup Otomatis

1. Restore Database Tertentu

# Extract dari gz
gunzip daily/app_database_20260115_020045.sql.gz

# Restore
mysql -u root -p app_database < daily/app_database_20260115_020045.sql

2. Restore Full Backup

gunzip < weekly/weekly_full_20260112.sql.gz | mysql -u root -p

Kesimpulan

Backup otomatis adalah investasi untuk ketenangan pikiran. Dengan setup yang tepat, Anda memiliki multiple layer protection untuk data bisnis yang berharga.