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
- Jenis-Jenis Backup
- Backup Logis dengan
mysqldump- Sintaks Dasar
- Contoh: Backup Satu Database
- Backup Beberapa Database
- Backup Semua Database
- Backup Satu Tabel
- Backup Beberapa Tabel
- Opsi Penting
mysqldump - Contoh Backup Lengkap (Direkomendasikan)
- Backup Terkompresi
- Backup Hanya Struktur
- Backup Hanya Data
- Backup dengan Kondisi
- Backup Tanpa Tabel Tertentu
- Restore Logis
- Backup Fisik
- Backup Incremental dengan Binary Log
- Backup Otomatis dengan Cron
- Strategi Backup yang Andal
- Alat Backup Alternatif
- Ringkasan
Daftar isi
- Pendahuluan
- Jenis-Jenis Backup
- Backup Logis dengan
mysqldump- Sintaks Dasar
- Contoh: Backup Satu Database
- Backup Beberapa Database
- Backup Semua Database
- Backup Satu Tabel
- Backup Beberapa Tabel
- Opsi Penting
mysqldump - Contoh Backup Lengkap (Direkomendasikan)
- Backup Terkompresi
- Backup Hanya Struktur
- Backup Hanya Data
- Backup dengan Kondisi
- Backup Tanpa Tabel Tertentu
- Restore Logis
- Backup Fisik
- Backup Incremental dengan Binary Log
- Backup Otomatis dengan Cron
- Strategi Backup yang Andal
- Alat Backup Alternatif
- Ringkasan
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:
- Restore ke database
toko_online_test. - Jalankan query verifikasi (jumlah baris, cek integritas).
- 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-transactionuntuk 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.