Backup Database MariaDB & MySQL Otomatis dan Terjadwal

Backup yang tidak terjadwal atau tidak pernah diuji restore-nya sama saja dengan tidak punya backup sama sekali. Panduan ini menjelaskan cara membangun sistem backup otomatis untuk MariaDB/MySQL di qVM menggunakan mysqldump, dengan compression, retention policy untuk menghapus backup lama, penjadwalan menggunakan cron lengkap studi kasus penerapan nyata, notifikasi kegagalan menggunakan Telegram, dan logging supaya kegagalan backup langsung ketahuan.

Pengenalan

mysqldump adalah tool logical backup bawaan MySQL/MariaDB — meng-export struktur dan data database menjadi file .sql berisi statement CREATE TABLE dan INSERT yang bisa di-replay untuk restore. Berbeda dari physical backup (menyalin raw data file di /var/lib/mysql), logical backup lebih portable (bisa di-restore lintas versi MySQL/MariaDB) meski lebih lambat untuk dataset yang sangat besar.

Flag --single-transaction penting dipakai untuk tabel InnoDB — memanfaatkan MVCC (Multi-Version Concurrency Control) supaya mysqldump mengambil snapshot konsisten tanpa mengunci tabel (FLUSH TABLES WITH READ LOCK yang dipakai backup non-transactional akan memblokir write selama proses backup berjalan, bisa jadi masalah besar di production).

Panduan ini mengasumsikan MariaDB/MySQL sudah terpasang dan berjalan di server Anda.

Baja Juga :

• Cara Instalasi Database MariaDB di Ubuntu 24.04 LTS
• Cara Instalasi Database MySQL Community Server di Ubuntu 24.04 LTS

Persyaratan

Software

• MariaDB atau MySQL Server sudah terpasang dan berjalan
• Akses root atau user dengan hak sudo
• User database dengan privilege SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER (minimal untuk mysqldump yang konsisten)

Hardware / Spesifikasi VPS qVM

Panduan ini menggunakan paket qVM-2, dengan spesifikasi:

• vCPU: 1 core
• RAM: 2 GiB
• Storage: 50 GiB

Pastikan storage qVM punya ruang cukup untuk menyimpan beberapa generasi backup sekaligus — retention policy pada panduan ini membantu membatasi pertumbuhan, tapi tetap monitor df -h secara berkala.

Tahapan

1. Buat User Khusus Backup

Jangan pakai user root untuk automated backup. Buat user dengan privilege terbatas khusus untuk keperluan ini:

mysql -u root -p

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'PasswordBackupKuatAnda';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD, REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Privilege RELOAD dan REPLICATION CLIENT dibutuhkan untuk flag --master-data apabila server ini juga primary dalam setup replication.

2. Simpan Kredensial di File Terpisah (Bukan Hardcode di Script)

mkdir -p /root/.mysql-backup
vim /root/.mysql-backup/credentials.cnf

Isi dengan:

[client]
user=backup_user
password=PasswordBackupKuatAnda

Amankan permission file supaya cuma root yang bisa baca:

chmod 600 /root/.mysql-backup/credentials.cnf

Pola --defaults-extra-file ini mencegah password terlihat di ps aux output maupun bash history — lebih aman dibanding menaruh password langsung di command line atau di dalam script.

3. Buat Script Backup

mkdir -p /var/backups/mysql
vim /usr/local/bin/mysql-backup.sh

Isi dengan:

#!/bin/bash
set -euo pipefail

BACKUP_DIR="/var/backups/mysql"
CREDENTIALS="/root/.mysql-backup/credentials.cnf"
DATE=$(date +%Y-%m-%d_%H-%M-%S)
RETENTION_DAYS=7
LOG_FILE="/var/log/mysql-backup.log"

DATABASES=$(mysql --defaults-extra-file="$CREDENTIALS" -e "SHOW DATABASES;" | grep -Ev "Database|information_schema|performance_schema|sys")

echo "[$DATE] Memulai backup..." >> "$LOG_FILE"

for DB in $DATABASES; do
  FILE="$BACKUP_DIR/${DB}_${DATE}.sql.gz"
  if mysqldump --defaults-extra-file="$CREDENTIALS" \
      --single-transaction \
      --routines \
      --triggers \
      --events \
      --quick \
      "$DB" | gzip > "$FILE"; then
    echo "[$DATE] Backup $DB berhasil -> $FILE" >> "$LOG_FILE"
  else
    echo "[$DATE] GAGAL backup $DB" >> "$LOG_FILE"
    exit 1
  fi
done

find "$BACKUP_DIR" -name "*.sql.gz" -mtime +"$RETENTION_DAYS" -delete
echo "[$DATE] Backup selesai, retention $RETENTION_DAYS hari diterapkan." >> "$LOG_FILE"

Beberapa poin penting dari script di atas:

• set -euo pipefail menghentikan script begitu ada command yang gagal, mencegah backup "silently corrupt" karena error yang tidak ketahuan.
• Loop per-database (bukan --all-databases sekaligus) membuat setiap database punya file backup terpisah — restore satu database tidak perlu extract dari dump raksasa gabungan semua database.
• --routines --triggers --events memastikan stored procedure, trigger, dan scheduled event ikut ter-backup — sering terlewat di script backup yang naif.
• find -mtime +7 -delete adalah retention policy sederhana: hapus file lebih tua dari 7 hari. Sesuaikan RETENTION_DAYS dengan kebutuhan compliance/storage Anda.

Beri permission executable:

chmod +x /usr/local/bin/mysql-backup.sh

4. Uji Coba Manual

Jalankan script secara manual dulu sebelum dijadwalkan, pastikan tidak ada error:

/usr/local/bin/mysql-backup.sh

ls -lh /var/backups/mysql/
cat /var/log/mysql-backup.log

5. Jadwalkan Menggunakan Cron

crontab -e

Tambahkan baris berikut untuk menjalankan backup setiap hari jam 2 pagi (waktu low-traffic, meminimalkan dampak --single-transaction terhadap performa production):

0 2 * * * /usr/local/bin/mysql-backup.sh

6. Studi Kasus: Backup Harian app_db di qVM 161.248.116.110

Skenario: qVM production dengan IP 161.248.116.110 menjalankan MariaDB dengan database app_db (dibuat pada panduan instalasi MariaDB). Target: backup otomatis setiap hari jam 2 pagi waktu server, retention 7 hari, dan tim langsung tahu menggunakan Telegram kalau backup gagal — bukan baru sadar pas butuh restore.

Pastikan timezone server sudah benar sebelum menjadwalkan apa pun — jadwal cron mengikuti waktu sistem, bukan waktu lokal Anda:

timedatectl

Apabila Time zone belum sesuai (misalnya masih UTC padahal Anda mau jadwal jam 2 pagi WIB), set ke Asia/Jakarta:

timedatectl set-timezone Asia/Jakarta

Verifikasi cron job sudah terdaftar dengan benar:

crontab -l

Output seharusnya menampilkan baris 0 2 * * * /usr/local/bin/mysql-backup.sh persis seperti yang diketik pada tahap 5. Lima kolom di awal itu menit jam tanggal bulan hari — 0 2 * * * berarti menit ke-0, jam 2, setiap tanggal, setiap bulan, setiap hari (* = wildcard/semua).

Untuk memastikan daemon cron benar-benar mengeksekusi job ini (bukan cuma terdaftar tapi tidak jalan), cek log sistem keesokan harinya:

grep CRON /var/log/syslog | grep mysql-backup

Contoh output yang menandakan berhasil ter-eksekusi oleh cron:

Aug 17 02:00:01 qvm-db CRON[8842]: (root) CMD (/usr/local/bin/mysql-backup.sh)

Setelah beberapa hari berjalan, /var/log/mysql-backup.log akan terlihat seperti ini — pola inilah yang jadi indikator sehat/tidaknya backup harian:

[2026-08-14_02-00-01] Memulai backup...
[2026-08-14_02-00-03] Backup app_db berhasil -> /var/backups/mysql/app_db_2026-08-14_02-00-01.sql.gz
[2026-08-14_02-00-03] Backup selesai, retention 7 hari diterapkan.
[2026-08-15_02-00-01] Memulai backup...
[2026-08-15_02-00-04] Backup app_db berhasil -> /var/backups/mysql/app_db_2026-08-15_02-00-01.sql.gz
[2026-08-15_02-00-04] Backup selesai, retention 7 hari diterapkan.
[2026-08-16_02-00-01] Memulai backup...
[2026-08-16_02-00-03] Backup app_db berhasil -> /var/backups/mysql/app_db_2026-08-16_02-00-01.sql.gz
[2026-08-16_02-00-03] Backup selesai, retention 7 hari diterapkan.

Tambahan: notifikasi kegagalan menggunakan Telegram. Script pada tahap 3 sudah exit 1 begitu backup gagal, tapi tanpa notifikasi, kegagalan itu cuma tercatat di log yang jarang dicek. Tambahkan pengiriman notifikasi ke Telegram menggunakan Bot API saat backup gagal.

Siapkan dulu bot dan chat tujuan (cukup dilakukan sekali):

1. Chat ke @BotFather di Telegram, kirim /newbot, ikuti instruksinya, lalu catat bot token yang diberikan (formatnya seperti 123456789:AAExxxxxxxxxxxxxxxxxxxxxxxxxxxxx).
2. Tambahkan bot tersebut ke channel/group monitoring Anda, atau chat langsung ke bot-nya.
3. Ambil chat ID tujuan menggunakan endpoint getUpdates, setelah mengirim satu pesan apa saja ke bot/channel-nya terlebih dahulu:

curl -s "https://api.telegram.org/bot<BOT_TOKEN>/getUpdates"

Cari nilai "chat":{"id": ... } pada response JSON — angka itulah CHAT_ID yang dipakai pada langkah berikutnya (untuk group, biasanya berupa angka negatif).

Sisipkan baris berikut menggantikan bagian else pada script mysql-backup.sh:

  else
    echo "[$DATE] GAGAL backup $DB" >> "$LOG_FILE"
    curl -s -X POST "https://api.telegram.org/bot<BOT_TOKEN>/sendMessage" \
      -d chat_id="<CHAT_ID>" \
      -d text="❌ Backup MariaDB GAGAL di qVM 161.248.116.110 — database: $DB, waktu: $DATE"
    exit 1
  fi

Ganti <BOT_TOKEN> dan <CHAT_ID> dengan nilai milik bot dan channel monitoring Anda. Dengan ini, kegagalan backup langsung masuk notifikasi tim menggunakan Telegram, bukan cuma diam di dalam log file yang mungkin baru dicek setelah data hilang.

7. Verifikasi Backup Bisa Dibaca (Bukan Cuma "Ada")

File backup yang ada belum tentu valid. Uji integritas gzip dan struktur SQL-nya secara berkala:

gzip -t /var/backups/mysql/app_db_2026-08-16_02-00-01.sql.gz && echo "File gzip valid"

zcat /var/backups/mysql/app_db_2026-08-16_02-00-01.sql.gz | head -n 20

Pastikan output menampilkan header dump MySQL yang wajar (-- MySQL dump, CREATE TABLE, dan seterusnya), bukan file kosong atau terpotong.

Kesimpulan

Sampai tahap ini, qVM sudah punya sistem backup MariaDB/MySQL otomatis: user backup dengan privilege terbatas, kredensial tersimpan aman terpisah dari script, backup per-database dengan compression, retention policy 7 hari, penjadwalan harian menggunakan cron, dan notifikasi Telegram setiap kali backup gagal — bukan cuma tercatat diam-diam di log file. Untuk keandalan lebih tinggi, pertimbangkan menyalin hasil backup ke storage off-site (S3-compatible object storage, atau qVM terpisah menggunakan rsync/scp) — backup yang cuma disimpan di server yang sama tidak melindungi dari kegagalan disk atau insiden pada server itu sendiri. Uji proses restore-nya secara berkala di environment terpisah untuk memastikan file backup benar-benar bisa dipakai saat dibutuhkan, bukan cuma diasumsikan valid.