Dunia MySQL & MariaDB

mysqldump: Panduan Lengkap Backup & Restore Database MySQL

Pencadangan dan Pemulihan
佐川 直弘 | Naohiro Sagawa
By 佐川 直弘 | Naohiro Sagawa (運営者)
※記事内に広告を含みます。Amazonアソシエイトとして適格販売により収入を得ています。　
目次

Penggunaan dan Praktik Terbaik mysqldump

1. Pendahuluan

Pencadangan dan pemulihan basis data adalah fondasi manajemen data yang penting untuk operasi yang andal. “mysqldump” MySQL adalah alat yang banyak digunakan untuk membuat cadangan basis data secara efisien dan fleksibel. Panduan ini akan menjelaskan secara rinci mulai dari penggunaan dasar mysqldump, cara memanfaatkan opsi lanjutan, metode pemulihan (restore), hingga pemecahan masalah (troubleshooting). Di akhir artikel, kami juga akan memperkenalkan praktik terbaik dan sumber daya referensi. Silakan gunakan ini sebagai bantuan untuk menguasai mysqldump.

2. Apa itu mysqldump?

2.1 Ikhtisar mysqldump

mysqldump adalah alat baris perintah untuk membuat cadangan basis data MySQL. Anda dapat membuang (dump) seluruh basis data, tabel tertentu, atau data yang cocok dengan kriteria tertentu sebagai skrip SQL. File dump ini digunakan untuk memulihkan data atau memigrasikan data ke server baru.

2.2 Kasus Penggunaan

  • Pencadangan (Backup): Membuat cadangan secara berkala untuk mempersiapkan diri menghadapi kegagalan sistem atau kehilangan data.
  • Migrasi Data: Digunakan untuk migrasi basis data antar server atau menyalin data ke lingkungan pengembangan.
  • Analisis Data: Mengekstrak kumpulan data tertentu untuk analisis dan validasi.

3. Penggunaan Dasar

3.1 Sintaks Perintah Dasar

Sintaks perintah dasar mysqldump adalah sebagai berikut:

mysqldump -u nama_pengguna -p nama_basis_data > nama_file_output.sql
  • -u nama_pengguna: Nama pengguna untuk mengakses basis data.
  • -p: Meminta input password.
  • nama_basis_data: Nama basis data yang akan dicadangkan.
  • > nama_file_output.sql: Lokasi penyimpanan file dump.

3.2 Opsi Autentikasi Pengguna

  • -h nama_host: Nama host server basis data (defaultnya adalah localhost).
  • -P nomor_port: Nomor port untuk koneksi (defaultnya adalah 3306).

3.3 Contoh: Pencadangan Seluruh Basis Data

mysqldump -u root -p mydatabase > backup.sql

Perintah ini akan membuat cadangan seluruh data dari mydatabase ke dalam file backup.sql. Menyertakan tanggal dalam nama file cadangan untuk manajemen versi akan memudahkan pelacakan riwayat.

4. Penjelasan Opsi Utama

4.1 --all-databases (-A)

Opsi ini untuk membuat cadangan semua basis data sekaligus. Berguna ketika Anda ingin membuat cadangan seluruh server.

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

4.2 --no-data (-d)

Digunakan ketika Anda ingin membuat cadangan hanya skema tabel tanpa menyertakan data. Contohnya, digunakan ketika menyiapkan lingkungan pengembangan dengan hanya mengambil struktur tabel.

mysqldump -u root -p mydatabase --no-data > schema_only_backup.sql

4.3 --where (-w)

Digunakan ketika Anda ingin membuat cadangan hanya data yang cocok dengan kriteria tertentu. Misalnya, untuk membuat cadangan hanya record di mana kolom is_active adalah 1:

mysqldump -u root -p mydatabase --where="is_active=1" > filtered_data_backup.sql

4.4 --ignore-table

Digunakan ketika ingin mengecualikan tabel tertentu dari pencadangan. Berguna jika ada tabel yang tidak ingin dicadangkan.

mysqldump -u root -p mydatabase --ignore-table=mydatabase.table1 > partial_backup.sql

5. Contoh Praktis

5.1 Dump Hanya Tabel Tertentu

Jika Anda ingin membuat cadangan hanya tabel tertentu, tentukan nama tabel setelah nama basis data.

mysqldump -u root -p mydatabase table1 > table1_backup.sql

Perintah ini hanya menyimpan data dari table1 ke dalam table1_backup.sql.

5.2 Dump Hanya Data / Hanya Skema

  • Hanya Data: mysqldump -u root -p mydatabase --no-create-info > data_only_backup.sql Membuat cadangan hanya data, tidak menyertakan struktur tabel.
  • Hanya Skema:
    bash mysqldump -u root -p mydatabase --no-data > schema_only_backup.sql
    Membuat cadangan hanya skema tabel.

5.3 Dump Bersyarat

Untuk membuat cadangan hanya data yang cocok dengan kriteria tertentu, gunakan opsi --where.

mysqldump -u root -p mydatabase --where="created_at >= '2023-01-01'" > recent_data_backup.sql

Perintah ini akan membuat cadangan hanya data di mana created_at setelah atau sama dengan 1 Januari 2023.

6. Metode Pemulihan (Restore)

Untuk memulihkan basis data yang dicadangkan dengan mysqldump, gunakan perintah mysql. Restore adalah proses memulihkan status basis data menggunakan file cadangan.

6.1 Sintaks Dasar Pemulihan (Restore)

mysql -u nama_pengguna -p nama_basis_data < file_dump.sql
  • -u nama_pengguna: Nama pengguna untuk terhubung ke basis data.
  • -p: Meminta input password.
  • nama_basis_data: Nama basis data tujuan pemulihan.
  • < file_dump.sql: File dump yang digunakan untuk pemulihan.

6.2 Contoh: Melakukan Pemulihan (Restore)

mysql -u root -p mydatabase < backup.sql

Dengan perintah ini, data akan dipulihkan ke mydatabase dari file backup.sql.

6.3 Poin Perhatian Saat Pemulihan (Restore)

  • Jika basis data tujuan pemulihan tidak ada, Anda harus membuatnya terlebih dahulu.
  • Memulihkan data dalam jumlah besar mungkin memerlukan waktu, jadi penting untuk membuat rencana sebelumnya.

7. Praktik Terbaik mysqldump

7.1 Penjadwalan Pencadangan

Buat skrip mysqldump dan otomatiskan pencadangan secara berkala menggunakan penjadwal seperti cron. Contoh skrip shell di bawah ini akan membuat cadangan seluruh basis data setiap tengah malam.

#!/bin/bash
mysqldump -u root -p'password' --all-databases > /path/to/backup/all_databases_$(date +%F).sql

7.2 Enkripsi File Cadangan

Karena file cadangan berisi informasi sensitif, disarankan untuk mengenkripsinya menggunakan alat seperti gpg.

gpg -c /path/to/backup/all_databases_$(date +%F).sql

7.3 Kompatibilitas Antar Versi

Ketika memigrasikan data antar versi MySQL yang berbeda, perlu diperhatikan masalah ketidakcocokan (inkompatibilitas). Simulasikan prosedur pencadangan dan pemulihan di lingkungan pengujian sebelum memperbarui versi untuk memastikan kompatibilitas.

  1. Pemulihan (Restore) Definisi Tabel: mysqldump --all-databases --no-data --routines --events > dump-defs.sql Dengan perintah ini, hanya struktur tabel yang dibuang (dumped), lalu dipulihkan di lingkungan versi baru untuk memeriksa kompatibilitas.
  2. Pemulihan (Restore) Data: mysqldump --all-databases --no-create-info > dump-data.sql Setelah definisi tabel dipastikan kompatibel, pulihkan hanya datanya.
  3. Verifikasi di Lingkungan Pengujian:
    Untuk memastikan kompatibilitas antar versi, lakukan pencadangan dan pemulihan di lingkungan pengujian dan pastikan berfungsi dengan benar sebelum melakukan migrasi di lingkungan produksi.

7.4 Penyimpanan dan Verifikasi Cadangan

  • Penyimpanan Cadangan yang Aman:
    Simpan file cadangan di penyimpanan eksternal atau cloud dan perbarui secara berkala. Penyimpanan di luar lokasi (off-site) dapat melindungi data dari kerusakan fisik.
  • Verifikasi Pemulihan (Restore) Berkala:
    Lakukan uji pemulihan (restore test) secara berkala untuk memastikan bahwa cadangan dapat dipulihkan dengan benar. Penting untuk tidak mengabaikan verifikasi pemulihan untuk berjaga-jaga jika cadangan tidak valid.

8. Pemecahan Masalah (Troubleshooting)

8.1 Kesalahan Umum dan Cara Mengatasinya

  • Kesalahan: @@GLOBAL.GTID_PURGED cannot be changed:
    Kesalahan ini muncul jika terjadi masalah terkait GTID di MySQL 8.0. Dapat dihindari dengan menggunakan opsi --set-gtid-purged=COMMENTED untuk mengomentari pengaturan GTID. mysqldump -u root -p mydatabase --set-gtid-purged=COMMENTED > backup.sql
  • Kesalahan: Ruang Disk Tidak Cukup:
    Jika ruang disk tidak cukup saat mencadangkan basis data skala besar, kompres cadangan atau ubah lokasi penyimpanan. Cadangkan dengan kompresi gzip seperti di bawah ini. mysqldump -u root -p mydatabase | gzip > backup.sql.gz
  • Kesalahan: Hak Akses Tidak Cukup:
    Jika pengguna basis data tidak memiliki hak akses yang memadai, pencadangan atau pemulihan akan gagal. Berikan hak akses yang diperlukan (SELECT, LOCK TABLES, SHOW VIEW, dll.) lalu coba lagi.

8.2 Masalah Kompatibilitas Versi

Masalah kompatibilitas antar versi MySQL yang berbeda diselesaikan dengan pengujian sebelum memperbarui versi. Khususnya saat migrasi dari MySQL 5.7 ke 8.0, disarankan untuk memulihkan hanya definisi tabel dengan opsi --no-data dan memeriksa kompatibilitas.

  • Pengujian Ketidakcocokan (Inkompatibilitas):
    Sebelum memperbarui versi, simulasikan migrasi di lingkungan pengujian untuk mengidentifikasi potensi masalah. Perhatikan fitur atau sintaks yang tidak kompatibel dan perbaiki skrip SQL jika perlu.

9. Kesimpulan

mysqldump adalah alat yang andal dan kuat untuk melakukan pencadangan dan pemulihan basis data MySQL. Artikel ini telah menjelaskan secara komprehensif mulai dari penggunaan dasar, opsi lanjutan, praktik terbaik, hingga pemecahan masalah. Dengan memanfaatkan pengetahuan ini, Anda dapat melakukan perlindungan dan pengelolaan basis data menggunakan mysqldump secara efisien.

Dengan menerapkan praktik terbaik seperti penjadwalan pencadangan dan enkripsi file, Anda dapat memastikan keamanan data dan meningkatkan keandalan operasi basis data. Manfaatkan mysqldump dengan benar untuk bersiap menghadapi masalah basis data.

10. Referensi dan Sumber Daya Tambahan

Silakan lihat sumber daya ini untuk mempelajari lebih dalam tentang mysqldump dan menerapkannya dalam praktik. Selain itu, dengan melakukan pencadangan berkala dan verifikasi pemulihan (restore), Anda dapat menjaga keamanan basis data dan bersiap menghadapi kemungkinan kehilangan data.