Eko Sudarmaji

Cara Backup Database MySQL Otomatis di Ubuntu Server

Database merupakan salah satu komponen paling penting dalam sebuah server. Ketika database mengalami kerusakan, terhapus, atau VPS mengalami masalah, aplikasi dapat kehilangan data penting jika tidak memiliki backup.

Karena itu, backup database sebaiknya tidak dilakukan hanya ketika administrator mengingatnya. Backup dapat dibuat secara otomatis menggunakan mysqldump dan cron job di Ubuntu Server.

Dengan konfigurasi yang tepat, server dapat membuat backup MySQL secara berkala, menyimpan file backup dalam direktori tertentu, mengompresnya, serta menghapus backup lama berdasarkan kebijakan retensi.

Artikel ini membahas cara membuat sistem backup MySQL otomatis di Ubuntu Server dari awal sampai proses restore.

Kenapa Backup Database Penting?

Database dapat mengalami masalah karena berbagai alasan, misalnya:

  • Human error.
  • Perintah SQL yang salah.
  • Database corruption.
  • Disk VPS mengalami masalah.
  • Aplikasi mengalami bug.
  • Server terkena serangan.
  • Kesalahan konfigurasi.
  • Penghapusan data secara tidak sengaja.
  • Kegagalan saat deployment atau maintenance.

Backup memberikan titik pemulihan ketika data utama mengalami masalah.

Production Database
        │
        │ backup
        ↓
   Backup File
        │
        ↓
Storage Backup
        │
        │ jika terjadi masalah
        ↓
     Restore
        │
        ↓
Database Pulih

Namun perlu dipahami bahwa backup yang belum pernah diuji restore bukanlah backup yang benar-benar terverifikasi.

Apa Itu mysqldump?

mysqldump adalah utility command-line yang dapat digunakan untuk membuat dump database MySQL.

Dump biasanya berisi instruksi SQL untuk membuat kembali struktur database dan memasukkan data.

Contoh sederhana:

mysqldump -u root -p nama_database > backup.sql

Setelah command dijalankan, MySQL akan meminta password.

File:

backup.sql

kemudian dapat digunakan untuk proses restore.

Memeriksa mysqldump

Pastikan utility tersedia di server:

mysqldump --version

Jika command belum tersedia, instal client MySQL sesuai sistem Ubuntu dan versi MySQL yang digunakan.

Backup Satu Database MySQL

Misalnya database aplikasi bernama:

myapp

Backup dapat dibuat menggunakan:

mysqldump -u root -p myapp > myapp.sql

File myapp.sql akan dibuat pada direktori saat command dijalankan.

Lebih baik menentukan lokasi backup secara eksplisit.

mkdir -p /var/backups/mysql

mysqldump -u root -p myapp > /var/backups/mysql/myapp.sql

Backup dengan Timestamp

Jika setiap backup menggunakan nama file yang sama, backup sebelumnya akan tertimpa.

Karena itu, kita dapat menambahkan timestamp pada nama file.

mysqldump -u root -p myapp \
  > "/var/backups/mysql/myapp_$(date +%Y-%m-%d_%H-%M-%S).sql"

Contoh nama file:

myapp_2026-09-04_02-00-00.sql

Dengan demikian setiap proses backup menghasilkan file baru.

Kenapa Backup Sebaiknya Dikompresi?

File SQL hasil dump dapat berukuran besar, terutama jika database memiliki banyak data.

File tersebut dapat dikompresi menggunakan gzip.

mysqldump -u root -p myapp | gzip > myapp.sql.gz

File yang dihasilkan:

myapp.sql.gz

Ukuran file biasanya lebih kecil dibandingkan SQL dump tanpa kompresi.

Restore Backup .sql

Jika backup berupa file SQL biasa, restore dapat dilakukan menggunakan:

mysql -u root -p myapp < backup.sql

Database target harus sudah tersedia jika dump tidak berisi perintah pembuatan database.

Misalnya:

mysql -u root -p -e "CREATE DATABASE myapp;"

Kemudian:

mysql -u root -p myapp < backup.sql

Restore File .sql.gz

Jika backup dikompresi dengan gzip, file tidak harus diekstrak terlebih dahulu.

Kita dapat menggunakan:

gunzip -c backup.sql.gz | mysql -u root -p myapp

Alurnya:

backup.sql.gz
      │
      ↓
   gunzip
      │
      ↓
   SQL dump
      │
      ↓
    MySQL
      │
      ↓
   Database

Membuat User Khusus untuk Backup

Menggunakan akun root database untuk script otomatis bukan pendekatan yang ideal.

Lebih baik membuat user khusus backup dengan privilege yang diperlukan.

Contoh:

CREATE USER 'backup_user'@'localhost'
IDENTIFIED BY 'PASSWORD_KUAT';

GRANT SELECT, SHOW VIEW, TRIGGER, EVENT
ON myapp.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

Privilege yang dibutuhkan dapat berbeda tergantung struktur database, fitur MySQL yang digunakan, dan metode backup.

Untuk database production, pastikan privilege benar-benar diuji dengan proses dump sebelum automation diaktifkan.

Menyimpan Password MySQL untuk Script

Masalah yang sering muncul adalah bagaimana membuat mysqldump berjalan otomatis tanpa administrator mengetik password setiap kali.

Salah satu pendekatan adalah menggunakan MySQL client option file dengan permission yang ketat.

Contohnya file:

/root/.my.cnf

Isi sederhananya:

[client]
user=backup_user
password=PASSWORD_KUAT
host=localhost

Kemudian batasi permission:

chmod 600 /root/.my.cnf

Setelah itu command berikut dapat menggunakan credential dari file tersebut:

mysqldump myapp > backup.sql

Lokasi dan metode penyimpanan credential harus disesuaikan dengan user yang menjalankan script. Jangan membuat file credential dapat dibaca oleh user lain.

Kenapa Password Jangan Ditulis di Command?

Hindari pola seperti:

mysqldump -u backup_user -pPASSWORD myapp > backup.sql

Password dapat berisiko terekspos melalui history, proses, atau mekanisme lain tergantung environment.

Gunakan mekanisme credential yang lebih aman dan batasi permission file.

Membuat Script Backup

Daripada menulis command panjang langsung di cron, lebih mudah membuat script khusus.

Misalnya:

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

Isi script:

#!/bin/bash

BACKUP_DIR="/var/backups/mysql"
DATABASE="myapp"
TIMESTAMP=$(date +"%Y-%m-%d_%H-%M-%S")
BACKUP_FILE="${BACKUP_DIR}/${DATABASE}_${TIMESTAMP}.sql.gz"

mkdir -p "$BACKUP_DIR"

mysqldump "$DATABASE" | gzip > "$BACKUP_FILE"

if [ $? -eq 0 ]; then
    echo "Backup berhasil: $BACKUP_FILE"
else
    echo "Backup gagal"
    rm -f "$BACKUP_FILE"
    exit 1
fi

Kemudian jadikan executable:

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

Uji secara manual:

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

Periksa hasilnya:

ls -lh /var/backups/mysql

Menambahkan Retensi Backup

Jika backup dilakukan setiap hari tetapi file lama tidak pernah dihapus, storage VPS akhirnya dapat penuh.

Karena itu, backup membutuhkan kebijakan retensi.

Misalnya kita ingin menyimpan backup selama 7 hari.

find "$BACKUP_DIR" \
  -type f \
  -name "${DATABASE}_*.sql.gz" \
  -mtime +7 \
  -delete

Command tersebut mencari file backup yang lebih lama dari batas tertentu kemudian menghapusnya.

Retensi sebaiknya disesuaikan dengan kebutuhan aplikasi dan kapasitas storage.

Script Backup dengan Retensi

Contoh script yang lebih lengkap:

#!/bin/bash

set -u

BACKUP_DIR="/var/backups/mysql"
DATABASE="myapp"
TIMESTAMP=$(date +"%Y-%m-%d_%H-%M-%S")
BACKUP_FILE="${BACKUP_DIR}/${DATABASE}_${TIMESTAMP}.sql.gz"

mkdir -p "$BACKUP_DIR"

if mysqldump "$DATABASE" | gzip > "$BACKUP_FILE"; then
    echo "$(date '+%Y-%m-%d %H:%M:%S') Backup berhasil: $BACKUP_FILE"
else
    echo "$(date '+%Y-%m-%d %H:%M:%S') Backup gagal"
    rm -f "$BACKUP_FILE"
    exit 1
fi

find "$BACKUP_DIR" \
    -type f \
    -name "${DATABASE}_*.sql.gz" \
    -mtime +7 \
    -delete

echo "$(date '+%Y-%m-%d %H:%M:%S') Retensi backup selesai"

Perhatikan bahwa detail script production perlu disesuaikan dengan kebutuhan, versi MySQL, permission, dan strategi backup yang digunakan.

Menambahkan Logging

Backup otomatis sebaiknya memiliki log agar kita dapat mengetahui apakah proses berjalan atau gagal.

Misalnya:

LOG_FILE="/var/log/mysql-backup.log"

echo "$(date '+%Y-%m-%d %H:%M:%S') Backup dimulai" >> "$LOG_FILE"

Command backup dapat diarahkan ke log:

/usr/local/bin/backup-mysql.sh >> /var/log/mysql-backup.log 2>&1

Untuk melihat log:

tail -f /var/log/mysql-backup.log

Menjalankan Backup dengan Cron

Setelah script berjalan dengan baik secara manual, kita dapat membuat cron job.

Buka crontab:

crontab -e

Contoh menjalankan backup setiap hari pukul 02:00:

0 2 * * * /usr/local/bin/backup-mysql.sh >> /var/log/mysql-backup.log 2>&1

Format cron tersebut berarti:

0 2 * * *
│ │ │ │ │
│ │ │ │ └── Hari dalam minggu
│ │ │ └──── Bulan
│ │ └────── Hari dalam bulan
│ └──────── Jam
└────────── Menit

Dengan demikian script akan dijalankan setiap hari pada pukul 02:00.

Jangan Langsung Memasukkan Cron Sebelum Testing

Urutan yang lebih aman adalah:

  1. Buat script.
  2. Pastikan permission benar.
  3. Jalankan script secara manual.
  4. Pastikan file backup dibuat.
  5. Pastikan file dapat dibaca.
  6. Lakukan test restore.
  7. Baru masukkan ke cron.

Dengan cara ini, masalah dapat ditemukan sebelum automation dijalankan setiap hari.

Memeriksa Apakah Cron Berjalan

Jika backup tidak muncul sesuai jadwal, periksa service cron:

systemctl status cron

Log cron juga dapat diperiksa tergantung konfigurasi Ubuntu:

journalctl -u cron

Selain itu, periksa log yang dibuat oleh script backup.

Backup Beberapa Database

Jika VPS memiliki beberapa database, kita dapat membuat daftar database.

DATABASES=(
    "app_production"
    "wordpress"
    "iot"
)

Kemudian melakukan dump untuk setiap database:

for DATABASE in "${DATABASES[@]}"; do

    TIMESTAMP=$(date +"%Y-%m-%d_%H-%M-%S")

    mysqldump "$DATABASE" \
        | gzip \
        > "/var/backups/mysql/${DATABASE}_${TIMESTAMP}.sql.gz"

done

Pendekatan ini cocok ketika setiap database perlu memiliki file backup terpisah.

Backup Semua Database

MySQL juga menyediakan opsi untuk melakukan dump semua database:

mysqldump --all-databases | gzip > all-databases.sql.gz

Ini dapat berguna untuk kebutuhan tertentu, tetapi untuk sistem yang memiliki banyak database, backup per database sering lebih fleksibel ketika proses restore hanya membutuhkan satu aplikasi.

Backup Struktur Database Saja

Kadang kita hanya membutuhkan struktur tabel tanpa data.

Gunakan:

mysqldump --no-data myapp > schema.sql

File tersebut berisi struktur database tanpa data.

Backup Data Tanpa Struktur

Sebaliknya, kita dapat menggunakan:

mysqldump --no-create-info myapp > data.sql

Namun untuk backup production normal, biasanya struktur dan data perlu disimpan bersama sesuai kebutuhan restore.

Transaksi dan Konsistensi Backup

Backup database production perlu memperhatikan konsistensi data.

Untuk database yang menggunakan transactional storage engine seperti InnoDB, opsi yang umum digunakan adalah:

mysqldump \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  myapp \
  | gzip > myapp.sql.gz

--single-transaction dapat membantu membuat dump konsisten untuk tabel transactional tanpa melakukan lock global seperti pendekatan tertentu lainnya.

Namun opsi backup yang tepat tetap bergantung pada engine tabel, workload, ukuran database, dan kebutuhan recovery.

Jangan Lupa Stored Procedure, Trigger, dan Event

Data tabel bukan satu-satunya komponen database.

Database dapat memiliki:

  • Stored procedure.
  • Function.
  • Trigger.
  • Event scheduler.
  • View.

Jika komponen tersebut diperlukan oleh aplikasi, pastikan metode dump yang digunakan benar-benar menyertakannya.

Backup Database Bukan Backup Server

Ini adalah perbedaan penting.

Jika kita hanya menjalankan:

mysqldump myapp > backup.sql

yang dibackup adalah database, bukan seluruh server.

File aplikasi, konfigurasi Nginx, SSL certificate, upload, environment, Docker volume, dan konfigurasi sistem tidak otomatis ikut dibackup.

Untuk server production, strategi backup dapat mencakup:

Server Backup Strategy
│
├── Database
├── Application Source
├── Upload / Storage
├── Configuration
├── SSL / Certificate
├── Docker Volume
└── Server Configuration

3-2-1 Backup Rule

Salah satu prinsip backup yang populer adalah pendekatan 3-2-1:

  • 3 salinan data.
  • 2 media atau lokasi penyimpanan berbeda.
  • 1 salinan berada di lokasi berbeda atau off-site.

Contohnya:

Production Database
       │
       ├── VPS Local Backup
       │
       ├── Backup Storage
       │
       └── Off-site Backup

Backup lokal membantu pemulihan cepat, sedangkan backup off-site membantu ketika VPS atau storage utama mengalami masalah besar.

Jangan Hanya Menyimpan Backup di VPS yang Sama

Misalnya database berada di:

VPS
└── MySQL

dan backup juga berada di:

VPS
└── /var/backups/mysql

Jika VPS tersebut hilang atau disk mengalami kerusakan, database dan backup lokal dapat hilang bersamaan.

Karena itu, backup penting sebaiknya disalin ke storage terpisah.

Contoh Arsitektur Backup yang Lebih Aman

                Production VPS
                       │
                       ↓
                 MySQL Database
                       │
                       ↓
                Local Backup
                       │
                       ↓
                Remote Storage
                       │
             ┌─────────┴─────────┐
             ↓                   ↓
        Backup Retention     Backup Monitoring

Remote storage dapat berupa server backup, object storage, atau media lain yang sesuai kebutuhan organisasi.

Backup dan Encryption

Database backup dapat mengandung data sensitif.

Jika backup disimpan di lokasi lain, pertimbangkan encryption at rest dan keamanan credential akses ke storage tersebut.

Contoh sederhana menggunakan GPG:

gpg --symmetric --cipher-algo AES256 backup.sql.gz

Perlu diperhatikan bahwa encryption hanya berguna jika key atau password juga dikelola dengan aman.

Monitoring Backup

Backup otomatis yang gagal selama berminggu-minggu tanpa diketahui merupakan masalah serius.

Karena itu, sistem backup idealnya memiliki mekanisme untuk mengetahui:

  • Kapan backup terakhir berhasil.
  • Ukuran backup terakhir.
  • Apakah proses dump berhasil.
  • Apakah storage masih tersedia.
  • Apakah upload ke remote storage berhasil.

Minimal, script dapat mencatat status ke log.

Memeriksa Ukuran Backup

Periksa ukuran file:

ls -lh /var/backups/mysql

Jika ukuran backup biasanya sekitar 500 MB kemudian tiba-tiba hanya 1 KB, kondisi tersebut patut diperiksa.

Ukuran file bukan satu-satunya indikator validitas, tetapi dapat menjadi sinyal awal adanya masalah.

Test Restore Secara Berkala

Backup sebaiknya diuji restore secara berkala.

Contoh workflow:

Backup
  │
  ↓
Copy to Test Server
  │
  ↓
Create Test Database
  │
  ↓
Restore
  │
  ↓
Verify Tables
  │
  ↓
Verify Application

Dengan test restore, kita dapat mengetahui apakah backup benar-benar dapat digunakan ketika terjadi keadaan darurat.

Contoh Prosedur Restore

Misalnya file backup:

myapp_2026-09-04_02-00-00.sql.gz

Buat database target:

mysql -u root -p -e "CREATE DATABASE myapp_restore;"

Kemudian restore:

gunzip -c myapp_2026-09-04_02-00-00.sql.gz \
  | mysql -u root -p myapp_restore

Setelah selesai, periksa database:

mysql -u root -p myapp_restore

Kemudian jalankan:

SHOW TABLES;

Untuk aplikasi production, proses verifikasi dapat dilanjutkan dengan pengecekan data dan fungsi aplikasi.

Full Backup vs Incremental Backup

mysqldump umumnya digunakan untuk logical dump. Untuk database yang besar, kebutuhan backup dapat menjadi lebih kompleks.

Beberapa strategi backup antara lain:

StrategiKarakteristik
Logical dumpMenghasilkan representasi SQL/data yang dapat direstore
Physical backupMembackup file database secara fisik
IncrementalMenyimpan perubahan sejak backup tertentu
Full backupMenyimpan keseluruhan dataset sesuai metode yang digunakan

Untuk database kecil hingga menengah, logical dump sering kali sudah cukup. Database yang sangat besar atau memiliki kebutuhan recovery time dan recovery point yang ketat mungkin memerlukan strategi yang lebih khusus.

Backup MySQL di Docker

Jika MySQL berjalan di Docker, konsep backup tetap sama, tetapi command dapat dijalankan melalui container.

Misalnya:

docker exec mysql-container \
  mysqldump -u backup_user -p myapp \
  | gzip > myapp.sql.gz

Untuk penggunaan production, credential dan pipe perlu dikonfigurasi dengan hati-hati agar password tidak terekspos.

Jika database menggunakan Docker volume, volume tersebut juga perlu dipahami dalam strategi backup.

Kesalahan Umum Backup MySQL

1. Backup Hanya Disimpan di Server yang Sama

Jika server hilang, backup dapat ikut hilang.

2. Tidak Pernah Test Restore

File backup belum tentu dapat digunakan hanya karena file tersebut berhasil dibuat.

3. Tidak Menggunakan Retensi

Backup yang menumpuk dapat memenuhi disk VPS.

4. Menggunakan Root untuk Semua Hal

Gunakan user backup dengan privilege yang sesuai.

5. Tidak Memantau Kegagalan Cron

Automation tanpa monitoring dapat gagal diam-diam.

6. Mengabaikan Data Non-Database

Upload, source code, konfigurasi, dan storage aplikasi juga dapat menjadi bagian penting dari disaster recovery.

7. Tidak Memperhitungkan Ukuran Database

Database besar membutuhkan strategi backup yang lebih matang daripada sekadar menjalankan dump setiap beberapa jam.

Contoh Checklist Backup MySQL Production

  • Backup berjalan otomatis.
  • Backup memiliki timestamp.
  • Backup dikompresi jika sesuai kebutuhan.
  • Ada retensi backup.
  • Backup memiliki log.
  • Backup menggunakan credential khusus.
  • Permission file backup dibatasi.
  • Backup disalin ke lokasi berbeda.
  • Backup memiliki encryption jika diperlukan.
  • Restore diuji secara berkala.
  • Monitoring kegagalan tersedia.
  • Kapasitas storage dipantau.
  • RPO dan RTO sudah dipahami.

Memahami RPO dan RTO

Dalam disaster recovery, dua konsep penting adalah RPO dan RTO.

RPO — Recovery Point Objective

RPO menjawab pertanyaan:

Berapa banyak data yang masih dapat kita terima jika terjadi kegagalan?

Misalnya backup dilakukan setiap 24 jam. Dalam skenario terburuk, perubahan data sejak backup terakhir dapat hilang.

RTO — Recovery Time Objective

RTO menjawab pertanyaan:

Berapa lama waktu yang dapat ditoleransi sampai sistem kembali berjalan?

RPO dan RTO membantu menentukan seberapa sering backup harus dilakukan dan seberapa cepat proses restore harus dapat dilakukan.

Contoh Strategi Backup Sederhana

Untuk VPS aplikasi kecil, strategi awal dapat berupa:

Setiap hari
    │
    ↓
mysqldump
    │
    ↓
gzip
    │
    ↓
Local Backup
    │
    ↓
Remote Storage
    │
    ↓
Retensi 7-30 hari
    │
    ↓
Test Restore Berkala

Strategi tersebut kemudian dapat dikembangkan sesuai kebutuhan bisnis.

Kesimpulan

Backup database MySQL di Ubuntu Server sebaiknya dibuat otomatis agar tidak bergantung pada aktivitas manual administrator.

Dengan mysqldump, kita dapat membuat logical backup database. Dengan gzip, file backup dapat dikompresi. Dengan cron, proses backup dapat dijalankan secara terjadwal.

MySQL
  │
  ↓
mysqldump
  │
  ↓
gzip
  │
  ↓
Backup File
  │
  ├── Retention
  ├── Logging
  ├── Remote Copy
  └── Restore Test

Hal yang paling penting bukan sekadar memiliki file backup, tetapi memastikan backup tersebut tersedia, aman, memiliki retensi yang jelas, tersimpan di lokasi berbeda, dan benar-benar dapat direstore.

Untuk server production, backup database sebaiknya menjadi bagian dari strategi disaster recovery yang lebih luas, bersama backup file aplikasi, storage, konfigurasi server, monitoring, dan prosedur pemulihan.

Artikel Terkait