Cara Backup & Restore Database MySQL (mysqldump)

Kehilangan database bisa menghancurkan sebuah aplikasi. Server bisa rusak, ada perintah DELETE tanpa WHERE, migrasi yang salah, atau hosting yang tiba-tiba bermasalah. Karena itu, backup rutin wajib dilakukan begitu aplikasi Anda live. Artikel ini menjelaskan cara backup dan restore database MySQL memakai mysqldump, plus cara menjadwalkannya otomatis, mengamankan password di script, dan mengatasi error yang paling sering muncul saat restore.

Semua perintah di bawah berlaku untuk MySQL 8 maupun MariaDB, baik di Linux (VPS), macOS, maupun Windows (XAMPP/Laragon). Perbedaan kecil antar platform akan disebutkan di bagian masing-masing.

Apa Itu mysqldump dan Cara Kerjanya

mysqldump adalah program bawaan MySQL yang membuat logical backup. Artinya, hasil backup berupa file teks berisi perintah SQL: CREATE TABLE untuk membuat ulang struktur tabel, lalu INSERT INTO untuk mengisi datanya kembali. Karena berbentuk teks, file ini bisa dibuka dengan editor biasa, dipindahkan ke server lain, bahkan di-restore ke versi MySQL yang berbeda.

Kelebihannya: mudah dipakai, portabel, dan tidak butuh tool tambahan. Kekurangannya: untuk database yang sangat besar (puluhan GB ke atas), proses backup dan terutama restore bisa memakan waktu lama karena setiap baris harus dieksekusi ulang sebagai SQL. Untuk kebanyakan aplikasi web, toko online kecil-menengah, atau blog, mysqldump sudah lebih dari cukup.

Catatan untuk pengguna MariaDB versi baru: nama programnya kini mariadb-dump dan mariadb. Di banyak instalasi, nama mysqldump dan mysql masih tersedia sebagai alias, jadi perintah di artikel ini tetap jalan. Kalau muncul peringatan nama program usang, cukup ganti dengan nama barunya, opsinya sama.

Backup Database dengan mysqldump

Perintah dasar untuk membuat backup satu database:

mysqldump -u username -p nama_database > backup.sql

Anda akan diminta memasukkan password. Hasilnya adalah file backup.sql berisi seluruh struktur dan data. Perintah ini dijalankan dari terminal, bukan dari dalam prompt mysql>. Kalau belum terbiasa dengan command line, baca dulu cara menggunakan terminal dan command line untuk developer.

Opsi yang sebaiknya selalu dipakai

Perintah dasar di atas sudah berfungsi, tetapi untuk aplikasi produksi ada beberapa opsi yang membuat backup lebih aman dan lengkap:

mysqldump -u username -p \
  --single-transaction \
  --routines --triggers --events \
  --default-character-set=utf8mb4 \
  nama_database > backup.sql

• --single-transaction: membuat snapshot yang konsisten untuk tabel InnoDB tanpa mengunci tabel. Aplikasi tetap bisa membaca dan menulis selama backup berjalan. Ini opsi paling penting untuk server yang sedang dipakai pengunjung.
• --routines dan --events: ikut menyimpan stored procedure, function, dan event scheduler. Secara default keduanya tidak ikut, sehingga banyak orang kaget prosedurnya hilang setelah restore.
• --triggers: sebenarnya sudah aktif secara default, ditulis eksplisit supaya jelas.
• --default-character-set=utf8mb4: mencegah karakter khusus atau emoji di data berubah menjadi tanda tanya.

Catatan: --single-transaction hanya menjamin konsistensi untuk tabel InnoDB. Tabel MyISAM tetap bisa berubah selama proses backup. Untungnya, sejak MySQL 5.5 engine default sudah InnoDB, dan aplikasi modern seperti Laravel juga memakai InnoDB.

Backup tabel tertentu, struktur saja, atau data saja

Kadang Anda tidak butuh seluruh database. Beberapa variasi yang sering berguna:

# hanya tabel users dan orders
mysqldump -u username -p nama_database users orders > sebagian.sql

# hanya struktur (tanpa data), cocok untuk dokumentasi skema
mysqldump -u username -p --no-data nama_database > struktur.sql

# hanya data (tanpa CREATE TABLE)
mysqldump -u username -p --no-create-info nama_database > data.sql

# semua tabel kecuali tabel log yang besar
mysqldump -u username -p nama_database --ignore-table=nama_database.activity_logs > tanpa_log.sql

Perhatikan bahwa --ignore-table wajib ditulis lengkap dengan nama database di depannya, dan diulang untuk setiap tabel yang ingin dilewati.

Backup dengan Kompresi (Hemat Ruang)

Untuk database besar, kompres langsung agar file lebih kecil:

mysqldump -u username -p nama_database | gzip > backup.sql.gz

Karena isi file SQL berupa teks yang banyak pengulangan, hasil kompresi biasanya jauh lebih kecil dari file aslinya. Cara ini juga lebih hemat disk karena file SQL mentah tidak pernah ditulis ke server. Untuk mengecek apakah file gzip utuh dan tidak terpotong, jalankan gzip -t backup.sql.gz. Jika tidak ada output, berarti file aman.

Restore Database

Untuk mengembalikan backup ke database (pastikan database tujuan sudah dibuat):

mysql -u username -p nama_database < backup.sql

Kalau database tujuan belum ada, buat dulu dengan charset yang benar:

mysql -u username -p -e "CREATE DATABASE nama_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"

Jika file terkompresi:

gunzip < backup.sql.gz | mysql -u username -p nama_database

Ingat bahwa restore akan menimpa tabel dengan nama yang sama, karena file hasil mysqldump secara default berisi DROP TABLE IF EXISTS sebelum setiap CREATE TABLE. Jadi, sebelum restore ke database produksi, sebaiknya backup dulu kondisi saat ini. Lebih aman lagi, restore ke database baru (misalnya nama_database_restore), periksa isinya, baru alihkan aplikasi.

Backup Semua Database Sekaligus

mysqldump -u username -p --all-databases > full_backup.sql

Opsi --all-databases butuh user dengan hak akses ke semua database, biasanya root. File hasilnya sudah berisi perintah CREATE DATABASE dan USE, jadi saat restore tidak perlu menyebut nama database:

mysql -u root -p < full_backup.sql

Kalau hanya ingin beberapa database sekaligus, pakai --databases:

mysqldump -u username -p --databases toko_db blog_db > dua_database.sql

Backup di Windows (XAMPP)

Di XAMPP, program mysqldump.exe ada di folder C:\xampp\mysql\bin. Buka Command Prompt lalu jalankan:

cd C:\xampp\mysql\bin
mysqldump -u root -p nama_database --result-file=C:\backup\backup.sql

Opsi --result-file lebih aman dibanding tanda > di Windows. Di Windows PowerShell 5.1, redirect > menulis file dalam encoding UTF-16, sehingga file SQL bisa gagal di-restore. Untuk restore, PowerShell juga tidak mendukung operator <. Gunakan Command Prompt biasa, atau perintah source dari dalam klien MySQL:

mysql -u root -p nama_database -e "source C:/backup/backup.sql"

Menyimpan Password dengan Aman

Menulis password langsung di perintah (-p'password') memang praktis untuk script, tetapi ada risikonya: password bisa terlihat oleh user lain di daftar proses dan tersimpan di history shell. MySQL juga akan menampilkan peringatan Using a password on the command line interface can be insecure. Ada dua cara yang lebih rapi.

Cara 1: file opsi .my.cnf (berlaku untuk MySQL dan MariaDB). Buat file ~/.my.cnf:

[client]
user=backup_user
password=PasswordKuatAnda

chmod 600 ~/.my.cnf

Setelah itu, mysqldump nama_database > backup.sql akan otomatis memakai kredensial tersebut tanpa -u dan -p. Kalau ingin memakai file di lokasi lain, gunakan --defaults-extra-file=/path/ke/file.cnf, dan opsi ini harus ditulis paling pertama sebelum opsi lain.

Cara 2: mysql_config_editor (khusus MySQL, tidak ada di MariaDB). Password disimpan dalam file terenkripsi ringan:

mysql_config_editor set --login-path=backup --host=localhost --user=backup_user --password
mysqldump --login-path=backup nama_database > backup.sql

Sebagai tambahan, buat user khusus backup dengan hak akses minimal, jangan memakai root:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'PasswordKuatAnda';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT ON nama_database.* TO 'backup_user'@'localhost';

Di MySQL 8, user tanpa hak PROCESS bisa mendapat error saat mysqldump mencoba membaca informasi tablespace. Solusinya tambahkan opsi --no-tablespaces pada perintah backup. Untuk backup aplikasi biasa, opsi ini tidak mengurangi isi backup yang Anda butuhkan.

Menjadwalkan Backup Otomatis dengan Cron

Buat script backup sederhana, misalnya /home/user/backup.sh:

#!/bin/bash
TANGGAL=$(date +%F)
mysqldump -u username -p'password' nama_database | gzip > /home/user/backups/db-$TANGGAL.sql.gz

Script tiga baris di atas sudah bekerja, tetapi punya dua kelemahan: password tertulis terang, dan kalau mysqldump gagal, gzip tetap menghasilkan file kecil yang terlihat seperti backup sukses. Versi yang lebih aman untuk produksi:

#!/bin/bash
set -euo pipefail

DB="nama_database"
DIR="/home/user/backups"
TANGGAL=$(date +%F_%H%M)
FILE="$DIR/db-$DB-$TANGGAL.sql.gz"

mkdir -p "$DIR"

# kredensial dibaca dari ~/.my.cnf
mysqldump --single-transaction --routines --triggers --events \
  --no-tablespaces "$DB" | gzip > "$FILE"

# pastikan file gzip utuh
gzip -t "$FILE"

# hapus backup yang lebih tua dari 7 hari
find "$DIR" -name "db-$DB-*.sql.gz" -mtime +7 -delete

echo "Backup selesai: $FILE"

set -o pipefail (bagian dari set -euo pipefail) membuat script berhenti dengan status gagal bila salah satu perintah dalam pipe gagal, termasuk mysqldump. Perintah find ... -mtime +7 -delete menjaga disk agar tidak penuh oleh backup lama.

Beri izin eksekusi, lalu daftarkan di crontab agar jalan tiap hari jam 2 pagi:

chmod +x /home/user/backup.sh
crontab -e

# jalankan backup tiap hari pukul 02:00
0 2 * * * /home/user/backup.sh

Supaya mudah dilacak bila gagal, arahkan output ke file log:

0 2 * * * /home/user/backup.sh >> /home/user/backups/backup.log 2>&1

Satu jebakan klasik: jika Anda menulis perintah date +%F langsung di baris crontab (bukan di dalam script), tanda % harus di-escape menjadi \%, karena cron menganggap % sebagai baris baru. Menaruh logika di file script seperti contoh di atas menghindari masalah ini sekaligus membuat crontab tetap rapi.

Menjalankan backup dari scheduler Laravel

Kalau aplikasi Anda memakai Laravel dan cron untuk schedule:run sudah terpasang, script yang sama bisa dijadwalkan dari kode. Di Laravel 11 dan 12, tambahkan di routes/console.php:

use Illuminate\Support\Facades\Schedule;

Schedule::exec('/home/user/backup.sh')
    ->dailyAt('02:00')
    ->withoutOverlapping();

Cara memasang cron untuk scheduler Laravel dijelaskan di panduan Cron Job dan Task Scheduler di Laravel.

Tips Penting

• Simpan backup di lokasi berbeda (mis. cloud storage), bukan hanya di server yang sama. Kalau server rusak atau diretas, backup di disk yang sama ikut hilang. Tool seperti rclone bisa menyalin folder backup ke Google Drive, S3, atau penyimpanan lain setelah remote dikonfigurasi, misalnya rclone copy /home/user/backups remote:db-backups.
• Hapus backup lama secara berkala agar tidak memenuhi disk. Pola umum: simpan harian selama 7 hari, lalu satu backup mingguan untuk beberapa minggu.
• Uji proses restore sesekali. Backup yang tidak pernah diuji sering ternyata rusak atau tidak lengkap saat benar-benar dibutuhkan.
• Lakukan backup manual tepat sebelum menjalankan migrasi besar, import data massal, atau upgrade versi MySQL.
• Jangan simpan file backup di folder publik web (misalnya public_html atau public), karena siapa pun yang tahu namanya bisa mengunduh seluruh data Anda.

Error yang Sering Muncul Saat Backup dan Restore

Untuk dua kasus di tengah tabel, perbaikannya bisa dilakukan dengan sed di Linux sebelum restore. Simpan dulu salinan file aslinya:

# ganti collation MySQL 8 ke collation yang didukung MariaDB/MySQL lama
sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' backup.sql

# hapus klausa DEFINER=`user`@`host`
sed -i 's/DEFINER=`[^`]*`@`[^`]*`//g' backup.sql

Untuk error max_allowed_packet, Anda bisa menaikkan batas di sisi klien saat restore, misalnya mysql --max-allowed-packet=256M -u username -p nama_database < backup.sql. Batas di sisi server juga harus cukup besar, diatur lewat file konfigurasi MySQL di bagian [mysqld].

Backup di cPanel

Kalau memakai shared hosting, cPanel juga menyediakan menu Backup dan phpMyAdmin → Export untuk backup manual tanpa command line. Di phpMyAdmin, pilih database, buka tab Export, lalu pilih format SQL. Untuk database kecil, cara ini cukup praktis. Namun untuk file besar, fitur Import di phpMyAdmin sering terbentur batas ukuran upload dan batas waktu eksekusi PHP. Jika hosting menyediakan akses SSH atau Terminal di cPanel, restore dengan perintah mysql jauh lebih andal.

Menu Backup bawaan cPanel biasanya menyediakan unduhan backup per database dalam format .sql.gz. Tetap simpan salinannya di komputer atau cloud Anda sendiri, jangan hanya mengandalkan backup dari penyedia hosting.

Kasus Nyata: Memindahkan Database ke Server Baru

Skenario yang sering terjadi: aplikasi dipindah dari shared hosting ke VPS. Langkah ringkasnya:

1. Di server lama, buat backup terkompresi dengan opsi lengkap (--single-transaction --routines --triggers --events).
2. Pindahkan file ke server baru, misalnya dengan scp backup.sql.gz user@ip-server-baru:/home/user/.
3. Di server baru, buat database dan user, lalu restore dengan gunzip < backup.sql.gz | mysql ....
4. Bandingkan jumlah baris beberapa tabel penting di kedua server, misalnya dengan SELECT COUNT(*) FROM users;.
5. Perbarui kredensial database di file .env aplikasi, lalu uji login dan fitur utama.

Kalau migrasinya sekalian memindahkan aplikasi Laravel, langkah lengkap menyiapkan servernya ada di cara deploy Laravel ke VPS Ubuntu dengan Nginx.

Kesimpulan

Backup database MySQL dengan mysqldump itu sederhana namun sangat krusial. Pakai --single-transaction agar backup konsisten tanpa mengunci aplikasi, simpan password di file opsi alih-alih di perintah, kombinasikan dengan kompresi dan jadwal cron agar backup berjalan otomatis, dan selalu simpan salinan di lokasi terpisah. Yang paling sering dilupakan: uji restore secara berkala. Untuk memahami penjadwalan cron lebih lanjut, lihat panduan kami tentang Task Scheduler di Laravel.

FAQ

Apakah mysqldump mengganggu website yang sedang berjalan?

Dengan opsi --single-transaction pada tabel InnoDB, backup berjalan tanpa mengunci tabel sehingga pengunjung tetap bisa memakai aplikasi. Beban CPU dan disk tetap naik selama proses, jadi jadwalkan backup di jam sepi seperti dini hari.

Apa beda mysqldump dengan export di phpMyAdmin?

Hasil keduanya sama-sama file SQL. Bedanya, phpMyAdmin berjalan lewat PHP dan browser sehingga bisa terkena batas waktu dan ukuran, sedangkan mysqldump berjalan langsung di server dan lebih cocok untuk database besar maupun backup otomatis.

Bisakah file backup MySQL 8 di-restore ke MariaDB?

Umumnya bisa, tetapi error yang paling sering muncul adalah collation utf8mb4_0900_ai_ci yang tidak dikenali MariaDB. Ganti dulu menjadi utf8mb4_unicode_ci di file dump seperti contoh di atas, lalu restore seperti biasa.

Seberapa sering sebaiknya backup dilakukan?

Tergantung seberapa banyak data yang rela Anda hilangkan. Untuk blog yang jarang berubah, backup harian sudah cukup. Untuk toko online atau aplikasi dengan transaksi terus-menerus, pertimbangkan backup beberapa kali sehari ditambah backup manual sebelum setiap perubahan besar.

Bagaimana cara restore satu tabel saja dari backup penuh?

Cara paling aman adalah restore seluruh file ke database sementara, lalu salin tabel yang dibutuhkan ke database utama. Ke depannya, Anda juga bisa membuat backup terpisah per tabel penting dengan menyebut nama tabel setelah nama database pada perintah mysqldump.

Baca juga: Panduan Belajar MySQL untuk Pemula: dari Dasar sampai Mahir, peta belajar lengkap mulai dari desain tabel, JOIN, index, hingga backup database.