Backup & Restore Database

Backup adalah asuransi terhadap kehilangan data. Artikel ini membahas cara melakukan backup logis dan fisik, melakukan restore, serta menyusun strategi backup yang andal untuk MySQL.

Pendahuluan

Data adalah aset paling berharga dalam sebuah aplikasi. Hardware bisa rusak, human error bisa terjadi, dan serangan siber bisa datang kapan saja. Tanpa backup yang baik, satu insiden kecil bisa menghapus data bertahun-tahun.

Artikel ini membahas:

  • Jenis-jenis backup (logis vs fisik).
  • Cara backup dan restore dengan mysqldump.
  • Backup fisik dengan mysqlbackup / file-level.
  • Backup otomatis dengan cron.
  • Strategi backup yang andal.

Asumsi: Anda memiliki akses ke server MySQL dan bisa menjalankan perintah shell. Contoh menggunakan database toko_online.


Jenis-Jenis Backup

1. Logical Backup

Backup berupa perintah SQL (CREATE TABLE, INSERT, dll.) yang bisa dieksekusi ulang untuk membangun database.

  • Kelebihan: Portabel (bisa dipulihkan di versi MySQL lain atau bahkan database lain), mudah dibaca, bisa backup per tabel.
  • Kekurangan: Lambat untuk database besar, ukuran file bisa sangat besar.
  • Alat: mysqldump, mysqlpump, mysqldumper.

2. Physical Backup

Backup berupa file data mentah (file .ibd, .frm, file log, dll.).

  • Kelebihan: Sangat cepat, cocok untuk database besar.
  • Kekurangan: Kurang portabel (terikat versi dan platform), tidak bisa backup per tabel dengan mudah.
  • Alat: mysqlbackup (Enterprise), Percona XtraBackup, cp/rsync (dengan database berhenti).

3. Snapshot Backup

Backup menggunakan snapshot dari filesystem atau storage (LVM, ZFS, cloud snapshot).

  • Sangat cepat, tetapi memerlukan dukungan infrastruktur.

4. Binary Log Backup

Backup incremental menggunakan binary log (binlog) untuk menangkap perubahan sejak backup terakhir.

  • Berguna untuk point-in-time recovery.

Backup Logis dengan mysqldump

mysqldump adalah alat bawaan MySQL untuk backup logis. Tersedia di semua instalasi MySQL.

Sintaks Dasar

mysqldump -u username -p nama_database > file_backup.sql

Contoh: Backup Satu Database

mysqldump -u root -p toko_online > toko_online_backup.sql

Setelah dijalankan, Anda akan diminta password. File toko_online_backup.sql berisi semua perintah SQL untuk membangun ulang database.

Backup Beberapa Database

mysqldump -u root -p --databases toko_online db_lain > backup_gabungan.sql

Opsi --databases menambahkan perintah CREATE DATABASE dan USE ke file backup.

Backup Semua Database

mysqldump -u root -p --all-databases > semua_database.sql

⚠️ Peringatan: Backup semua database mencakup database sistem (mysql, information_schema, dll.). Hati-hati saat restore — bisa menimpa user dan privilege.

Backup Satu Tabel

mysqldump -u root -p toko_online pelanggan > pelanggan_backup.sql

Backup Beberapa Tabel

mysqldump -u root -p toko_online pelanggan produk pesanan > sebagian_backup.sql

Opsi Penting mysqldump

Opsi Fungsi
--single-transaction Backup konsisten untuk InnoDB tanpa mengunci tabel
--quick Mengambil baris satu per satu, hemat memori
--lock-tables=false Tidak mengunci tabel (gunakan dengan --single-transaction)
--routines Sertakan stored procedure dan function
--triggers Sertakan trigger (default aktif)
--events Sertakan event scheduler
--no-data Backup struktur saja, tanpa data
--no-create-info Backup data saja, tanpa CREATE TABLE
--compress Kompres data saat transfer
--where="kondisi" Backup baris tertentu saja
--ignore-table=db.tabel Lewati tabel tertentu

Contoh Backup Lengkap (Direkomendasikan)

mysqldump -u root -p \
  --single-transaction \
  --quick \
  --routines \
  --triggers \
  --events \
  --set-gtid-purged=OFF \
  toko_online > toko_online_backup.sql

Penjelasan:

  • --single-transaction — konsisten untuk InnoDB, tidak mengunci tabel.
  • --quick — hemat memori untuk tabel besar.
  • --routines --triggers --events — sertakan objek-objek database.
  • --set-gtid-purged=OFF — hindari peringatan GTID saat restore di server lain.

Backup Terkompresi

mysqldump -u root -p toko_online | gzip > toko_online_backup.sql.gz

Atau dengan pigz (paralel, lebih cepat):

mysqldump -u root -p toko_online | pigz > toko_online_backup.sql.gz

Backup Hanya Struktur

mysqldump -u root -p --no-data toko_online > toko_online_schema.sql

Berguna untuk versioning skema di Git.

Backup Hanya Data

mysqldump -u root -p --no-create-info toko_online > toko_online_data.sql

Backup dengan Kondisi

mysqldump -u root -p toko_online pesanan \
  --where="dibuat_pada >= '2022-01-01'" > pesanan_2022.sql

Hanya backup pesanan tahun 2022.

Backup Tanpa Tabel Tertentu

mysqldump -u root -p toko_online \
  --ignore-table=toko_online.log_stok \
  --ignore-table=toko_online.audit_log > toko_online_backup.sql

Restore Logis

Restore Satu Database

mysql -u root -p toko_online < toko_online_backup.sql

Database toko_online harus sudah ada. Jika belum:

mysql -u root -p -e "CREATE DATABASE toko_online CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -u root -p toko_online < toko_online_backup.sql

Restore dari Backup Terkompresi

gunzip < toko_online_backup.sql.gz | mysql -u root -p toko_online

Atau dengan zcat:

zcat toko_online_backup.sql.gz | mysql -u root -p toko_online

Restore Backup dengan --databases

Jika backup dibuat dengan --databases, file sudah berisi CREATE DATABASE dan USE:

mysql -u root -p < backup_gabungan.sql

Restore Satu Tabel

mysql -u root -p toko_online < pelanggan_backup.sql

Restore ke Database Berbeda

mysql -u root -p toko_online_test < toko_online_backup.sql

Berguna untuk menguji backup tanpa menyentuh database produksi.

Verifikasi Setelah Restore

USE toko_online;
SHOW TABLES;
SELECT COUNT(*) FROM pelanggan;
SELECT COUNT(*) FROM pesanan;

Bandingkan jumlah baris dengan database asli.


Backup Fisik

Percona XtraBackup (Rekomendasi untuk InnoDB)

XtraBackup adalah alat open source populer untuk backup fisik MySQL/Percona Server. Mendukung hot backup (tanpa menghentikan server) untuk InnoDB.

Instalasi (Ubuntu):

sudo apt install percona-xtrabackup-80

Backup:

xtrabackup --backup --target-dir=/backup/mysql/full --user=root --password=...

Prepare (menerapkan log):

xtrabackup --prepare --target-dir=/backup/mysql/full

Restore:

sudo systemctl stop mysql
sudo rsync -avrP /backup/mysql/full/ /var/lib/mysql/
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql

Catatan: XtraBackup memerlukan versi yang sesuai dengan versi MySQL. Periksa kompatibilitas sebelum menggunakannya.

File-Level Backup (Server Berhenti)

Jika Anda bisa menghentikan server sementara:

sudo systemctl stop mysql
sudo rsync -av /var/lib/mysql/ /backup/mysql/cold/
sudo systemctl start mysql

Cara ini sederhana tetapi memerlukan downtime.

Snapshot (LVM, ZFS, Cloud)

Jika filesystem mendukung snapshot:

sudo lvcreate --size 10G --snapshot --name mysql_snap /dev/vg0/mysql_lv

Snapshot bisa di-mount dan di-backup tanpa menghentikan server.


Backup Incremental dengan Binary Log

Binary log (binlog) mencatat semua perubahan data. Dengan kombinasi full backup + binlog, Anda bisa melakukan point-in-time recovery (PITR) — memulihkan database ke detik tertentu.

Mengaktifkan Binary Log

Edit my.cnf:

[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7

Restart MySQL:

sudo systemctl restart mysql

Verifikasi:

SHOW VARIABLES LIKE 'log_bin';
SHOW BINARY LOGS;

Point-in-Time Recovery

Skenario: backup penuh dilakukan pukul 02:00, lalu terjadi kesalahan DELETE pukul 10:30. Kita ingin memulihkan ke pukul 10:29.

1. Restore full backup:

mysql -u root -p toko_online < full_backup_0200.sql

2. Terapkan binlog hingga pukul 10:29:

mysqlbinlog --stop-datetime="2022-12-17 10:29:00" \
  /var/log/mysql/mysql-bin.000001 | mysql -u root -p toko_online

Database kini berada di kondisi pukul 10:29, sebelum DELETE yang merusak.

Melihat Isi Binary Log

mysqlbinlog /var/log/mysql/mysql-bin.000001 | less

Atau via SQL:

SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 20;

Backup Otomatis dengan Cron

Backup manual tidak cukup — harus otomatis dan terjadwal.

Skrip Backup Sederhana

Buat file /usr/local/bin/backup_mysql.sh:

#!/bin/bash

# Konfigurasi
DB_USER="backup_user"
DB_PASS="BackupP@ss!2022"
DB_NAME="toko_online"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y-%m-%d_%H-%M-%S)
FILE="$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"
RETENTION_DAYS=7

# Buat direktori jika belum ada
mkdir -p "$BACKUP_DIR"

# Backup
mysqldump -u "$DB_USER" -p"$DB_PASS" \
  --single-transaction \
  --quick \
  --routines \
  --triggers \
  --events \
  "$DB_NAME" | gzip > "$FILE"

# Cek hasil
if [ $? -eq 0 ]; then
  echo "[$(date)] Backup berhasil: $FILE"
else
  echo "[$(date)] Backup GAGAL!" >&2
  exit 1
fi

# Hapus backup lama
find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "[$(date)] Backup selesai."

Beri izin eksekusi:

sudo chmod +x /usr/local/bin/backup_mysql.sh

Jadwalkan dengan Cron

Edit crontab:

crontab -e

Tambahkan baris berikut untuk backup setiap hari pukul 02:00:

0 2 * * * /usr/local/bin/backup_mysql.sh >> /var/log/backup_mysql.log 2>&1

Backup User Khusus

Buat user khusus backup dengan privilege minimal:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupP@ss!2022';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
GRANT RELOAD ON *.* TO 'backup_user'@'localhost';

Strategi Backup yang Andal

Aturan 3-2-1

  • 3 salinan data (asli + 2 backup).
  • 2 media berbeda (misalnya disk lokal + cloud).
  • 1 salinan di lokasi off-site (cloud atau remote server).

Contoh Jadwal

Frekuensi Jenis Retensi
Setiap hari Full logical backup (mysqldump) 7 hari
Setiap minggu Full physical backup (XtraBackup) 4 minggu
Setiap bulan Full backup + arsip ke cloud 12 bulan
Berkelanjutan Binary log 7 hari

Uji Restore Secara Berkala

Backup yang tidak pernah diuji restore bukanlah backup. Lakukan uji restore minimal sebulan sekali ke server percobaan:

  1. Restore ke database toko_online_test.
  2. Jalankan query verifikasi (jumlah baris, cek integritas).
  3. Bandingkan dengan produksi.

Monitoring Backup

  • Kirim notifikasi (email/Slack) jika backup gagal.
  • Pantau ukuran file backup — lonjakan atau penurunan drastis bisa menandakan masalah.
  • Cek log backup secara berkala.

Enkripsi Backup

Backup berisi data sensitif. Enkripsi file backup:

mysqldump -u root -p toko_online | gzip | \
  openssl enc -aes-256-cbc -salt -out toko_online_backup.sql.gz.enc

Restore:

openssl enc -d -aes-256-cbc -in toko_online_backup.sql.gz.enc | \
  gunzip | mysql -u root -p toko_online

Simpan passphrase enkripsi terpisah dari file backup.


Alat Backup Alternatif

Alat Jenis Kelebihan
mysqldump Logis Bawaan MySQL, portabel
mysqlpump Logis Paralel, lebih cepat dari mysqldump (deprecated di MySQL 8.0.34+)
Percona XtraBackup Fisik Hot backup, open source
mysqlbackup Fisik Fitur lengkap (MySQL Enterprise)
mydumper / myloader Logis Paralel, cepat untuk database besar
Cloud snapshot Fisik Cepat, terintegrasi dengan infrastruktur

Ringkasan

Kebutuhan Alat / Perintah
Backup logis satu database mysqldump -u root -p db > file.sql
Backup terkompresi mysqldump ... | gzip > file.sql.gz
Restore mysql -u root -p db < file.sql
Restore terkompresi zcat file.sql.gz | mysql -u root -p db
Backup fisik hot xtrabackup --backup
Point-in-time recovery Full backup + mysqlbinlog
Backup otomatis Cron + skrip backup
Backup terenkripsi openssl enc

Poin penting:

  • Uji restore secara berkala — backup yang tidak diuji bukan backup.
  • Gunakan --single-transaction untuk backup InnoDB yang konsisten tanpa mengunci tabel.
  • Terapkan aturan 3-2-1 untuk redundansi.
  • Aktifkan binary log untuk point-in-time recovery.
  • Enkripsi backup yang berisi data sensitif.
  • Simpan backup di lokasi terpisah dari server produksi.

Pada artikel berikutnya, kita akan membahas Transaksi & ACID — memastikan integritas data dalam operasi kompleks.

Top