Panduan Lengkap mysqldump: Backup, Restore, dan Otomatisasi Database MySQL

mysqldump adalah utilitas baris perintah yang disertakan bersama MySQL dan MariaDB yang menghasilkan backup logis dengan melakukan serialisasi objek database dan data sebagai serangkaian pernyataan SQL. File dump yang dihasilkan dapat membuat ulang database yang identik di server yang kompatibel, menjadikannya alat standar industri untuk backup, migrasi antar server, peningkatan versi, dan alur kerja pemulihan bencana.

Tidak seperti alat backup fisik seperti Percona XtraBackup atau MySQL Enterprise Backup, mysqldump beroperasi di lapisan SQL — ia membaca data langsung melalui protokol MySQL dan menulis SQL yang portabel dan dapat dibaca manusia. Portabilitas ini adalah kekuatan terbesarnya dan, pada skala besar, merupakan batasan utamanya.

Apa yang Sebenarnya Dilakukan mysqldump di Balik Layar

Ketika Anda menjalankan mysqldump, klien terhubung ke server MySQL, melakukan kueri pada information schema dan data dictionary, serta menghasilkan aliran pernyataan `CREATE DATABASE`, `CREATE TABLE`, `INSERT`, dan DDL ke output standar. Anda mengarahkan aliran tersebut ke file, pipe, atau utilitas kompresi.

Untuk tabel InnoDB dengan `–single-transaction`, mysqldump membuka transaksi repeatable-read sebelum membaca data apa pun. Ini memberikan snapshot point-in-time yang konsisten tanpa memperoleh global read lock — database tetap dapat ditulis sepenuhnya selama proses dump. Untuk tabel MyISAM, mekanisme seperti itu tidak ada; mysqldump beralih ke `FLUSH TABLES WITH READ LOCK`, yang sebentar memblokir penulisan.

Memahami perbedaan ini sangat penting sebelum Anda memilih mysqldump untuk beban kerja produksi. Jika skema Anda mencampur tabel InnoDB dan MyISAM, `–single-transaction` saja tidak cukup — Anda akan membutuhkan `–lock-all-tables` atau jendela pemeliharaan.

Prasyarat dan Hak Akses yang Diperlukan

Sebelum menjalankan perintah dump apa pun, verifikasi hal-hal berikut:

• MySQL atau MariaDB terinstal dan dapat diakses (socket lokal atau TCP/IP).
• Pengguna backup memiliki hak akses minimum yang diperlukan:
• `SELECT` pada semua tabel target
• `LOCK TABLES` (diperlukan kecuali `–single-transaction` digunakan secara eksklusif dengan InnoDB)
• `SHOW VIEW` untuk menyertakan view
• `TRIGGER` untuk menyertakan trigger
• `PROCESS` saat menggunakan `–single-transaction` pada MySQL 8+
• `RELOAD` untuk `FLUSH TABLES WITH READ LOCK`
• `REPLICATION CLIENT` jika Anda memerlukan koordinat binary log untuk pengaturan replikasi

Buat pengguna backup khusus daripada menjalankan dump sebagai root:

“`sql

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPassword!';

GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, PROCESS, RELOAD, REPLICATION CLIENT

ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

“`

Menjalankan mysqldump sebagai root dengan kata sandi yang tertanam dalam perintah shell mengekspos kredensial dalam daftar proses dan riwayat shell — risiko keamanan yang signifikan pada sistem bersama atau multi-pengguna mana pun.

Sintaks Dasar

“`

mysqldump [OPTIONS] database_name [table1 table2 …] > backup_file.sql

“`

Referensi Opsi Lengkap

Opsi Koneksi

Opsi Cakupan

Opsi Konsistensi dan Penguncian

Opsi Output dan Konten

mysqldump vs. Metode Backup Alternatif

Memilih strategi backup yang tepat bergantung pada ukuran database, persyaratan RTO/RPO, dan infrastruktur. Berikut adalah perbandingan mysqldump dengan alternatif yang paling umum:

Untuk database di bawah 10–20 GB pada lingkungan VPS Hosting, mysqldump tetap menjadi solusi yang paling praktis dan portabel. Di atas ambang batas tersebut, alat backup fisik menawarkan jendela backup dan restore yang jauh lebih cepat.

Contoh Penggunaan Praktis

Contoh 1: Backup Satu Database

“`bash

mysqldump -u backup_user -p database_name > /backups/database_name_$(date +%F).sql

“`

Substitusi `$(date +%F)` secara otomatis menambahkan tanggal ISO (misalnya, `2025-07-15`) ke nama file, mencegah penimpaan.

Contoh 2: Backup Beberapa Database Tertentu

“`bash

mysqldump -u backup_user -p –databases app_db analytics_db > /backups/multi_db_backup.sql

“`

Flag `–databases` menyebabkan mysqldump menghasilkan pernyataan `CREATE DATABASE` dan `USE`, membuat dump menjadi mandiri untuk restore.

Contoh 3: Backup Semua Database

“`bash

mysqldump -u backup_user -p –all-databases –events –routines –triggers

> /backups/full_server_$(date +%F).sql

“`

Selalu sertakan `–events`, `–routines`, dan `–triggers` dalam dump server penuh. Objek-objek ini dihilangkan secara diam-diam tanpa flag eksplisit.

Contoh 4: Backup InnoDB yang Konsisten (Aman untuk Produksi)

“`bash

mysqldump -u backup_user -p

–single-transaction

–flush-logs

–source-data=2

–routines –triggers –events

database_name > /backups/database_name_$(date +%F).sql

“`

`–flush-logs` merotasi binary log di awal dump. `–source-data=2` menulis nama file binary log saat ini dan posisinya sebagai komentar SQL, memungkinkan pemulihan point-in-time dengan memutar ulang binlog berikutnya dari posisi tersebut.

Contoh 5: Backup Terkompresi dengan gzip

“`bash

mysqldump -u backup_user -p database_name | gzip -9 > /backups/database_name_$(date +%F).sql.gz

“`

Untuk server yang terbatas CPU, ganti dengan `pigz` (parallel gzip) untuk memanfaatkan beberapa core:

“`bash

mysqldump -u backup_user -p database_name | pigz -9 > /backups/database_name_$(date +%F).sql.gz

“`

Contoh 6: Backup Skema Saja (Struktur Tanpa Data)

“`bash

mysqldump -u backup_user -p –no-data database_name > /backups/schema_only.sql

“`

Berguna untuk mengontrol versi skema Anda di Git atau melakukan deployment ke lingkungan staging tanpa menyalin data produksi.

Contoh 7: Backup Data Saja (Tanpa Skema)

“`bash

mysqldump -u backup_user -p –no-create-info database_name > /backups/data_only.sql

“`

Gunakan ini ketika skema target sudah ada dan Anda hanya perlu mengisi atau menyegarkan data.

Contoh 8: Backup Satu Tabel

“`bash

mysqldump -u backup_user -p database_name orders > /backups/orders_table_$(date +%F).sql

“`

Contoh 9: Ekspor Subset Baris yang Difilter

“`bash

mysqldump -u backup_user -p database_name orders

–where="created_at >= '2025-01-01' AND status='completed'"

> /backups/orders_2025_completed.sql

“`

Opsi `–where` jarang digunakan tetapi sangat powerful untuk ekspor parsial, pengarsipan data, dan debugging kumpulan record tertentu.

Contoh 10: Kecualikan Tabel Tertentu

“`bash

mysqldump -u backup_user -p database_name

–ignore-table=database_name.cache

–ignore-table=database_name.sessions

> /backups/database_name_no_cache.sql

“`

Mengecualikan tabel besar yang bersifat sementara (cache, penyimpanan sesi, tabel log) dapat mengurangi ukuran dan durasi dump secara signifikan.

Contoh 11: Menyertakan Stored Procedure, Fungsi, dan Trigger

“`bash

mysqldump -u backup_user -p –routines –triggers –events database_name > /backups/full_backup.sql

“`

Contoh 12: Backup Database Jarak Jauh

“`bash

mysqldump -u backup_user -p -h 192.168.1.100 -P 3306 database_name

“`

Saat melakukan backup server jarak jauh, lalu lintas melewati jaringan tanpa enkripsi secara default. Tambahkan flag `–ssl-ca`, `–ssl-cert`, dan `–ssl-key` atau terowongi melalui SSH:

“`bash

ssh user@remote-server "mysqldump -u backup_user -p database_name | gzip"

> /backups/remote_db_$(date +%F).sql.gz

“`

Memulihkan Backup mysqldump

Pulihkan Satu Database

“`bash

mysql -u root -p database_name < /backups/database_name_2025-07-15.sql

“`

Jika database target belum ada, buat terlebih dahulu:

“`bash

mysql -u root -p -e "CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

mysql -u root -p database_name < /backups/database_name_2025-07-15.sql

“`

Pulihkan Semua Database dari Dump Server Penuh

“`bash

mysql -u root -p < /backups/full_server_2025-07-15.sql

“`

Karena `–all-databases` menyematkan pernyataan `CREATE DATABASE` dan `USE`, tidak diperlukan argumen database target.

Pulihkan dari Backup Terkompresi

“`bash

gunzip < /backups/database_name_2025-07-15.sql.gz | mysql -u root -p database_name

“`

Atau menggunakan process substitution:

“`bash

mysql -u root -p database_name < <(gunzip -c /backups/database_name_2025-07-15.sql.gz)

“`

Pulihkan Satu Tabel dari Dump Database Penuh

Ini adalah skenario operasional umum yang membuat file dump asli menjadi tidak mudah. Gunakan `sed` atau `grep` untuk mengekstrak bagian yang relevan:

“`bash

sed -n '/^– Table structure for table `orders`/,/^– Table structure for table `/p'

backup_file.sql | head -n -1 | mysql -u root -p database_name

“`

Atau, gunakan `mysql_extract_table.sh` atau impor ke database sementara dan salin tabelnya:

“`bash

mysql -u root -p temp_restore < backup_file.sql

mysql -u root -p -e "INSERT INTO database_name.orders SELECT * FROM temp_restore.orders;"

“`

Pemulihan Point-in-Time Menggunakan Binary Log

Jika dump Anda diambil dengan `–source-data=2` dan binary logging diaktifkan, Anda dapat memulihkan ke titik mana pun setelah dump:

1. Identifikasi posisi binary log dari komentar header file dump.
2. Pulihkan dump dasar.
3. Terapkan event binary log berikutnya hingga timestamp yang diinginkan:

“`bash

mysqlbinlog –start-position=154 –stop-datetime="2025-07-15 14:30:00"

/var/lib/mysql/binlog.000042 | mysql -u root -p database_name

“`

Mengotomatiskan Backup dengan Cron

Pekerjaan Backup Harian Dasar

Simpan kredensial di `~/.my.cnf` daripada menyematkannya dalam perintah cron:

“`ini

[mysqldump]

user=backup_user

password=StrongPassword!

“`

Tetapkan izin yang ketat:

“`bash

chmod 600 ~/.my.cnf

“`

Kemudian buat pekerjaan cron:

“`bash

crontab -e

“`

“`

Daily compressed backup at 02:00, retained for 30 days

0 2 * * * mysqldump –single-transaction –routines –triggers –events database_name

Delete backups older than 30 days

10 2 * * * find /backups/ -name "*.sql.gz" -mtime +30 -delete

“`

Skrip Backup Tingkat Produksi

Untuk Dedicated Server yang menghosting beberapa database, skrip yang lebih robust menangani pencatatan error, pemeriksaan ruang disk, dan offloading jarak jauh:

“`bash

#!/bin/bash

BACKUP_DIR="/backups/mysql"

RETENTION_DAYS=30

LOG_FILE="/var/log/mysql_backup.log"

DATE=$(date +%F_%H-%M)

DATABASES=$(mysql –defaults-file=/etc/mysql/backup.cnf -e "SHOW DATABASES;"

mkdir -p "$BACKUP_DIR"

for DB in $DATABASES; do

OUTPUT="$BACKUP_DIR/${DB}_${DATE}.sql.gz"

mysqldump –defaults-file=/etc/mysql/backup.cnf

–single-transaction –routines –triggers –events

"$DB" | gzip -9 > "$OUTPUT"

if [ $? -eq 0 ]; then

echo "$(date): SUCCESS – $DB -> $OUTPUT" >> "$LOG_FILE"

else

echo "$(date): FAILURE – $DB" >> "$LOG_FILE"

fi

done

find "$BACKUP_DIR" -name "*.sql.gz" -mtime +"$RETENTION_DAYS" -delete

“`

Penguatan Keamanan untuk Operasi mysqldump

Manajemen kredensial adalah aspek keamanan backup yang paling sering diabaikan. Jangan pernah meneruskan `-pYourPassword` langsung di baris perintah — ini terlihat dalam output `ps aux` dan riwayat shell. Gunakan salah satu pendekatan berikut:

• `~/.my.cnf` dengan `chmod 600` (per-pengguna)
• `/etc/mysql/backup.cnf` dengan `chmod 640`, dimiliki oleh root, dapat dibaca oleh grup backup
• Variabel lingkungan `MYSQL_PWD` (terlihat di `/proc`, gunakan hanya di container yang terisolasi)
• MySQL Vault atau HashiCorp Vault untuk lingkungan enterprise

Izin file backup harus bersifat restriktif:

“`bash

chmod 640 /backups/database_name_2025-07-15.sql.gz

chown root:backup_group /backups/database_name_2025-07-15.sql.gz

“`

Enkripsi saat istirahat: Untuk data sensitif, enkripsi file backup sebelum menyimpan atau mentransfernya:

“`bash

mysqldump –single-transaction database_name

> /backups/database_name_$(date +%F).sql.gz.enc

“`

Enkripsi transport: Saat melakukan dump dari server jarak jauh, selalu gunakan SSL/TLS atau terowongan SSH. Pada lingkungan VPS dengan cPanel, antarmuka backup cPanel menangani ini secara otomatis, tetapi operasi mysqldump manual memerlukan flag SSL eksplisit.

Kesalahan Umum dan Cara Menghindarinya

Ketidakcocokan set karakter adalah penyebab paling umum dari restore yang rusak. Selalu tentukan set karakter secara eksplisit:

“`bash

mysqldump –default-character-set=utf8mb4 database_name > backup.sql

mysql –default-character-set=utf8mb4 database_name < backup.sql

“`

Hilangnya `–column-statistics=0` menyebabkan kegagalan ketika klien MySQL 8.0 melakukan dump dari server MySQL 5.7 atau MariaDB. Klien MySQL 8 mencoba melakukan dump statistik kolom yang tidak didukung oleh server yang lebih lama:

“`bash

mysqldump –column-statistics=0 -u backup_user -p database_name > backup.sql

“`

Melupakan `–routines`, `–triggers`, dan `–events` secara diam-diam menghilangkan objek database yang penting. Flag-flag ini tidak diaktifkan secara default (kecuali `–triggers`) dan sering terlupakan dalam dump ad-hoc.

Dump tabel besar menyebabkan OOM: mysqldump menyangga seluruh kumpulan hasil dalam memori secara default. Untuk tabel yang sangat besar, tambahkan `–quick` (diaktifkan secara default di sebagian besar versi, tetapi perlu diverifikasi) untuk mengalirkan baris satu per satu daripada menyangga:

“`bash

mysqldump –quick –single-transaction database_name > backup.sql

“`

Memulihkan ke versi MySQL yang berbeda: Dump dari MySQL 8.0 mungkin mengandung sintaks yang tidak didukung di MySQL 5.7 (misalnya, functional index, invisible column). Selalu uji restore di lingkungan yang cocok versinya sebelum mengandalkan migrasi lintas versi.

Pergeseran nilai auto-increment: Jika Anda memulihkan tabel ke dalam skema yang sudah ada yang sudah memiliki baris, pernyataan `INSERT` akan gagal pada konflik primary key kecuali Anda menyertakan `–add-drop-table` atau secara manual melakukan truncate pada tabel target terlebih dahulu.

Menggunakan mysqldump untuk Migrasi Database

mysqldump adalah pendekatan standar untuk memigrasikan database antar server — misalnya, saat memindahkan situs WordPress dari Shared Web Hosting ke VPS, atau melakukan replatforming ke lingkungan VPS Control Panel dengan lebih banyak sumber daya.

Alur kerja migrasi yang direkomendasikan:

1. Dump database sumber dengan opsi lengkap:

“`bash

mysqldump –single-transaction –routines –triggers –events

–default-character-set=utf8mb4 source_db | gzip > migration.sql.gz

“`

1. Transfer dengan aman menggunakan rsync melalui SSH:

“`bash

rsync -avz -e ssh migration.sql.gz user@target-server:/tmp/

“`

1. Buat database target dengan set karakter yang cocok:

“`bash

mysql -u root -p -e "CREATE DATABASE target_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

“`

1. Pulihkan dan verifikasi:

“`bash

gunzip < /tmp/migration.sql.gz | mysql -u root -p target_db

mysql -u root -p target_db -e "SHOW TABLES; SELECT COUNT(*) FROM critical_table;"

“`

1. Perbarui konfigurasi aplikasi untuk mengarah ke host database baru.

Untuk aplikasi yang juga mengandalkan infrastruktur email, pastikan record DNS dan konfigurasi Email Hosting diperbarui secara bersamaan dengan migrasi database untuk menghindari gangguan layanan.

Memverifikasi Integritas Backup

Backup yang belum pernah diuji bukanlah backup — itu adalah asumsi yang belum teruji. Terapkan rutinitas verifikasi:

“`bash

#!/bin/bash

Restore backup to a test database and verify row counts

TEST_DB="backup_verify_$(date +%s)"

BACKUP_FILE="/backups/database_name_$(date +%F).sql.gz"

mysql -u root -p -e "CREATE DATABASE $TEST_DB;"

gunzip < "$BACKUP_FILE" | mysql -u root -p "$TEST_DB"

PROD_COUNT=$(mysql -u root -p -sN -e "SELECT COUNT(*) FROM database_name.orders;")

TEST_COUNT=$(mysql -u root -p -sN -e "SELECT COUNT(*) FROM $TEST_DB.orders;")

if [ "$PROD_COUNT" -eq "$TEST_COUNT" ]; then

echo "Backup verified: row counts match ($PROD_COUNT rows)"

else

echo "BACKUP VERIFICATION FAILED: prod=$PROD_COUNT, test=$TEST_COUNT"

fi

mysql -u root -p -e "DROP DATABASE $TEST_DB;"

“`

Jalankan skrip verifikasi ini setiap minggu melalui cron dan beri peringatan jika gagal.

Matriks Keputusan: Kapan Menggunakan mysqldump

Daftar Periksa Poin Teknis Utama

• Selalu gunakan `–single-transaction` untuk database khusus InnoDB guna menghindari write lock selama backup.
• Selalu sertakan `–routines –triggers –events` dalam dump apa pun yang dimaksudkan sebagai backup lengkap.
• Simpan kredensial di `~/.my.cnf` atau `/etc/mysql/backup.cnf` dengan `chmod 600/640` — jangan pernah secara langsung dalam skrip atau perintah cron.
• Tambahkan `–column-statistics=0` saat menggunakan klien MySQL 8.0 terhadap server MySQL 5.7 atau MariaDB.
• Selalu tentukan `–default-character-set=utf8mb4` pada dump dan restore untuk mencegah korupsi encoding karakter.
• Kompres semua backup dengan gzip atau pigz; enkripsi dump sensitif dengan AES-256 sebelum transfer offsite.
• Sertakan `–flush-logs –source-data=2` dalam dump produksi untuk memungkinkan pemulihan point-in-time melalui binary log.
• Otomatiskan pembersihan retensi dengan `find … -mtime +N -delete` untuk mencegah kehabisan disk.
• Uji restore secara terjadwal — verifikasi jumlah baris dan periksa integritas data terhadap produksi.
• Untuk skema mesin campuran, gunakan `–lock-all-tables` daripada `–single-transaction` untuk menjamin konsistensi.

Pertanyaan yang Sering Diajukan

Apakah mysqldump mengunci tabel selama backup?

Dengan `–single-transaction` pada database InnoDB murni, tidak ada table lock yang diperoleh selain flush awal yang singkat. Tabel MyISAM selalu memerlukan read lock (`LOCK TABLES`) karena tidak memiliki dukungan transaksi. Skema mesin campuran memerlukan `–lock-all-tables` untuk snapshot yang konsisten, yang memblokir penulisan selama durasi dump.

Bagaimana cara melakukan backup hanya skema database tanpa data apa pun?

Gunakan flag `–no-data`: `mysqldump -u backup_user -p –no-data database_name > schema.sql`. Ini mengekspor semua `CREATE TABLE`, `CREATE VIEW`, stored procedure, dan trigger tanpa pernyataan `INSERT` apa pun.

Mengapa mysqldump saya gagal dengan error “column statistics”?

Ini terjadi ketika klien MySQL 8.0 terhubung ke server MySQL 5.7 atau MariaDB. Tambahkan `–column-statistics=0` ke perintah Anda. Atau, perbarui server ke MySQL 8.0 atau gunakan binary klien yang cocok dengan versi server.

Bisakah mysqldump melakukan backup inkremental?

Tidak. mysqldump selalu menghasilkan dump logis penuh dari cakupan yang ditentukan. Kemampuan backup inkremental memerlukan pengarsipan binary log (`mysqlbinlog`) yang dikombinasikan dengan mysqldump dasar yang diambil dengan `–flush-logs –source-data=2`. Backup fisik inkremental yang sesungguhnya memerlukan Percona XtraBackup atau MySQL Enterprise Backup.

Apa cara paling aman untuk mengotomatiskan mysqldump tanpa mengekspos kata sandi?

Buat pengguna backup MySQL khusus dengan hak akses minimum yang diperlukan, simpan kredensialnya di bagian `[mysqldump]` dari `~/.my.cnf` atau file opsi terpisah dengan `chmod 600`, dan referensikan dengan `–defaults-file=/path/to/backup.cnf`. Pendekatan ini menjaga kredensial sepenuhnya keluar dari daftar proses, riwayat shell, dan definisi pekerjaan cron.