Cara Backup Database MySQL dan PostgreSQL Otomatis

Kapan terakhir kamu benar-benar memulihkan backup database, bukan sekadar melihat filenya ada? Banyak server punya backup yang tidak pernah diuji, dan baru ketahuan rusak atau kosong saat dibutuhkan. Backup otomatis yang baik bukan hanya soal membuat file setiap malam, tapi juga memastikan file itu bisa dipakai.

Rumusnya sederhana: satu skrip yang membuat dump, mengompres, membuang yang lama, dan mengirim salinan ke tempat lain. Lalu skrip itu dijadwalkan, dipantau, dan diuji. Langkah di bawah berlaku untuk MySQL atau MariaDB dan PostgreSQL di server Linux.

Apakah dump logis cukup untuk database kamu?

mysqldump dan pg_dump membuat backup logis: isi database diekspor menjadi perintah SQL atau format arsip. Kelebihannya, file mudah dipindah antarversi dan antarserver, dan kamu bisa memulihkan satu tabel saja bila perlu.

Kekurangannya, dump butuh waktu lebih lama untuk database besar, dan hasilnya hanya potret pada satu titik waktu. Kalau backup dibuat pukul 02.00 dan server rusak pukul 17.00, data di antaranya hilang. Untuk database yang sangat besar atau yang tidak boleh kehilangan data beberapa jam, pertimbangkan backup fisik dan pengarsipan log transaksi (binlog di MySQL, WAL di PostgreSQL). Untuk sebagian besar aplikasi bisnis kecil sampai menengah, dump harian yang rapi sudah memberi perlindungan yang berarti.

Langkah membuat backup database otomatis

Langkah 1. Buat user khusus backup

Jangan memakai akun root database di skrip. Buat user yang cukup hak untuk membaca.

Untuk MySQL atau MariaDB:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'password-kuat';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS ON *.* TO 'backup'@'localhost';

Bila server MySQL memakai GTID (gtid_mode=ON), --single-transaction juga butuh hak RELOAD atau FLUSH_TABLES, jadi tambahkan salah satunya.

Untuk PostgreSQL, cara paling ringkas adalah menjalankan skrip sebagai user sistem postgres, atau membuat role dengan hak baca. Di PostgreSQL 14 ke atas tersedia role bawaan pg_read_all_data untuk keperluan ini.

Langkah 2. Simpan kredensial di file, bukan di skrip

Password yang ditulis di baris perintah bisa terlihat di daftar proses. Gunakan file opsi.

MySQL membaca file ~/.my.cnf milik user yang menjalankan skrip:

[client]
user=backup
password=password-kuat

PostgreSQL memakai ~/.pgpass dengan format host:port:database:user:password. Untuk keduanya, batasi izinnya dengan chmod 600.

Langkah 3. Tulis skripnya

Simpan sebagai /usr/local/bin/backup-db.sh:

#!/usr/bin/env bash
set -euo pipefail

TUJUAN=/var/backups/db
SIMPAN_HARI=14
TGL=$(date +%F_%H%M)
mkdir -p "$TUJUAN"

# MySQL / MariaDB: satu file per database
for db in $(mysql -N -e "SHOW DATABASES" | grep -Ev '^(information_schema|performance_schema|sys)$'); do
  mysqldump --single-transaction --routines --triggers --events "$db" \
    | gzip > "$TUJUAN/mysql_${db}_${TGL}.sql.gz"
done

# PostgreSQL: format custom, sudah terkompresi
for db in $(sudo -u postgres psql -At -c "SELECT datname FROM pg_database WHERE NOT datistemplate"); do
  sudo -u postgres pg_dump -Fc "$db" > "$TUJUAN/pg_${db}_${TGL}.dump"
done
sudo -u postgres pg_dumpall --globals-only | gzip > "$TUJUAN/pg_globals_${TGL}.sql.gz"

# Rotasi: hapus file lebih tua dari SIMPAN_HARI
find "$TUJUAN" -type f -mtime +"$SIMPAN_HARI" -delete

Hapus bagian yang tidak kamu pakai. Beberapa hal penting di sini:

• --single-transaction membuat dump yang konsisten untuk tabel InnoDB tanpa mengunci tabel lama-lama.
• -Fc di pg_dump menghasilkan format custom yang sudah terkompresi dan bisa dipulihkan sebagian dengan pg_restore.
• pg_dumpall --globals-only menyimpan role dan hak akses, yang tidak ikut di pg_dump per database.
• set -euo pipefail membuat skrip berhenti dengan status gagal bila salah satu perintah error, termasuk di tengah pipe.

Jadikan bisa dieksekusi dengan chmod 700 /usr/local/bin/backup-db.sh.

Langkah 4. Jadwalkan

Cara paling umum adalah cron. Jalankan sudo crontab -e lalu tambahkan:

30 2 * * * /usr/local/bin/backup-db.sh >> /var/log/backup-db.log 2>&1

Artinya skrip jalan setiap hari pukul 02.30 dan output disimpan ke log. Penjelasan sintaks dan jebakan PATH ada di cara membuat cron job di Linux. Bila kamu lebih suka systemd timer, buat service bertipe oneshot lalu timer dengan OnCalendar=*-*-* 02:30:00 dan Persistent=true, supaya jadwal yang terlewat saat server mati dijalankan setelah server hidup. Dasar unit systemd dibahas di membuat service systemd.

Langkah 5. Kirim salinan ke luar server

Backup yang hanya ada di server yang sama akan hilang bersama servernya. Kirim salinan ke lokasi lain, misalnya object storage S3 atau server cadangan. Dua alat yang umum:

# ke server lain lewat SSH
rsync -a /var/backups/db/ backup@server-cadangan:/data/db/

# ke object storage, setelah remote dikonfigurasi dengan rclone config
rclone copy /var/backups/db remote-s3:nama-bucket/db

Tambahkan baris ini di akhir skrip. Kalau bisa, gunakan kredensial yang hanya boleh menulis, sehingga orang yang membobol server tidak bisa menghapus backup di tujuan. Alasan satu server tidak cukup dijelaskan di backup website otomatis.

Langkah 6. Uji restore secara rutin

Inilah langkah yang membedakan backup sungguhan dari harapan. Pulihkan ke database uji, bukan ke produksi:

# MySQL
mysql -e "CREATE DATABASE uji_restore"
gunzip < mysql_toko_2026-11-05_0230.sql.gz | mysql uji_restore

# PostgreSQL
sudo -u postgres createdb uji_restore
sudo -u postgres pg_restore -d uji_restore pg_toko_2026-11-05_0230.dump

Lalu cek isinya: jumlah baris tabel utama, data terbaru, dan apakah aplikasi bisa membaca. Jadwalkan uji ini misalnya sebulan sekali, dan catat berapa lama restore berjalan. Angka itu menjadi perkiraan berapa lama layananmu akan mati bila harus memulihkan.

Bagaimana tahu backup gagal tanpa membuka log tiap hari?

Skrip yang gagal diam-diam adalah masalah klasik. Beberapa cara memantaunya:

• Kirim notifikasi hanya bila skrip keluar dengan status gagal, misalnya lewat email atau webhook.
• Pakai pemantau "heartbeat": skrip memanggil URL tertentu setiap kali sukses, dan kamu diberi tahu bila panggilan itu tidak datang. Fitur seperti ini ada di alat pemantauan seperti yang dibahas di Uptime Kuma.
• Periksa ukuran file. File dump yang tiba-tiba jauh lebih kecil dari biasanya patut dicurigai.

Ringkasan perintah MySQL dan PostgreSQL

Cek ulang sebelum menganggap selesai:

• User backup terpisah dengan hak terbatas
• Kredensial di ~/.my.cnf atau ~/.pgpass dengan izin 600
• Skrip berhenti dengan status gagal bila ada error
• Jadwal aktif dan output masuk ke log
• Salinan terkirim ke luar server
• Uji restore terakhir berhasil dan tercatat waktunya