Salin dan Bagikan

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.

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

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

Kenapa Backup Otomatis?

Persiapan Environment

1. Buat User Backup

2. Setup Direktori Backup

3. Install Tools Pendukung

Script Backup Harian

1. Buat Script Backup

2. Beri Izin Eksekusi

3. Test Manual

Setup Cron Job

1. Edit Crontab

2. Tambahkan Job Backup

3. Verifikasi Cron

Script Backup Mingguan

1. Buat Script

2. Beri Izin

Script Backup Bulanan

Cloud Backup dengan Rclone

1. Konfigurasi Rclone

2. Script Sync ke Cloud

3. Tambahkan ke Cron

Monitoring dan Alerting

1. Script Health Check

2. Setup Check Cron

Restore dari Backup Otomatis

1. Restore Database Tertentu

2. Restore Full Backup

Kesimpulan

Artikel Terkait