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

· Februari 3, 2026 · 8 menit baca

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="[email protected]"
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 &gt; $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 &quot;Backup $DB berhasil: ${DB}_$DATE.sql.gz ($FILE_SIZE)&quot;
else
    log_message &quot;ERROR: Backup $DB gagal&quot;
    send_email &quot;[WARNING] Backup Database $DB Gagal&quot; &quot;Backup database $DB gagal pada $(date)&quot;
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"
filog_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" [email protected]
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="[email protected]"
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.

Ditulis oleh

Hendra Wijaya

Artikel Sebelumnya Apa itu SEO dan Mengapa Penting untuk Bisnis Online Anda Artikel Selanjutnya Cara Membuat Theme Hugo dari Nol dengan Tailwind CSS

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

Kenapa Backup Otomatis?

Persiapan Environment

1. Buat User Backup

2. Setup Direktori Backup

Set permission

3. Install Tools Pendukung

Install rclone untuk cloud backup (opsional)

Script Backup Harian

1. Buat Script Backup

Konfigurasi

Fungsi logging

Fungsi kirim email

Cek koneksi database

Backup setiap database secara individual

Backup full (semua database)

Rotasi backup (hapus file lama)

Hitung total size

Kirim summary email (hanya hari Senin)

2. Beri Izin Eksekusi

3. Test Manual

Setup Cron Job

1. Edit Crontab

2. Tambahkan Job Backup

Backup mingguan setiap Minggu jam 3 pagi

Backup bulanan setiap tanggal 1 jam 4 pagi

3. Verifikasi Cron

Script Backup Mingguan

1. Buat Script

Full backup dengan routines dan events

Hapus backup lama (> 4 minggu)

2. Beri Izin

Script Backup Bulanan

Backup dengan struktur lengkap

Hapus backup lama (> 6 bulan)

Cloud Backup dengan Rclone

1. Konfigurasi Rclone

2. Script Sync ke Cloud

Sync ke cloud

3. Tambahkan ke Cron

Monitoring dan Alerting

1. Script Health Check

Cek backup terakhir

Cek ukuran backup

2. Setup Check Cron

Restore dari Backup Otomatis

1. Restore Database Tertentu

Restore

2. Restore Full Backup

Kesimpulan

Artikel Terkait

Tinggalkan Komentar