Practical MySQL & MariaDB

Panduan mysqldump: Cara Membuat Cadangan dan Memulihkan Database MySQL (Dengan Contoh)

Backup & Pemulihan
目次

1. Pendahuluan

Backup dan pemulihan basis data adalah hal mendasar dalam manajemen data dan penting untuk operasi yang dapat diandalkan. mysqldump MySQL banyak digunakan sebagai alat yang efisien dan fleksibel untuk membuat backup basis data. Dalam panduan ini, kami akan menjelaskan segala hal mulai dari penggunaan dasar mysqldump hingga opsi lanjutan, metode pemulihan, dan pemecahan masalah secara detail. Di akhir artikel, kami juga memperkenalkan praktik terbaik dan sumber daya tambahan, jadi silakan gunakan ini sebagai referensi yang membantu untuk menguasai mysqldump.

2. Apa itu mysqldump?

2.1 Gambaran Umum mysqldump

mysqldump adalah alat baris perintah untuk membuat backup basis data MySQL. Anda dapat mengekspor seluruh basis data, tabel tertentu, atau hanya data yang memenuhi kondisi tertentu sebagai skrip SQL. File dump ini dapat digunakan untuk memulihkan data atau memigrasikannya ke server baru.

2.2 Kasus Penggunaan Umum

  • Backup : Lakukan backup secara rutin untuk mempersiapkan kegagalan sistem atau kehilangan data.
  • Migrasi Data : Pindahkan basis data antar server atau salin data ke lingkungan pengembangan.
  • Analisis Data : Ekstrak dataset tertentu untuk analisis dan validasi.

3. Penggunaan Dasar

3.1 Sintaks Perintah Dasar

Sintaks perintah dasar untuk mysqldump adalah sebagai berikut:

mysqldump -u username -p database_name > output_file.sql
  • -u username : Nama pengguna yang digunakan untuk mengakses basis data.
  • -p : Meminta Anda memasukkan kata sandi.
  • database_name : Nama basis data yang ingin Anda backup.
  • > output_file.sql : Jalur/nama tujuan untuk file dump.

3.2 Opsi Autentikasi Pengguna

  • -h hostname : Nama host server basis data (default adalah localhost).
  • -P port_number : Nomor port untuk terhubung (default adalah 3306).

3.3 Contoh: Cadangkan Seluruh Database

mysqldump -u root -p mydatabase > backup.sql

Perintah ini mencadangkan semua data dalam mydatabase ke file backup.sql. Jika Anda menyertakan tanggal dalam nama file backup untuk kontrol versi, akan menjadi lebih mudah melacak riwayat backup Anda.

4. Penjelasan Opsi Kunci

4.1 --all-databases (-A)

Opsi ini mencadangkan semua basis data sekaligus. Ini berguna ketika Anda ingin melakukan backup penuh seluruh server.

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

4.2 --no-data (-d)

Gunakan opsi ini ketika Anda hanya ingin mencadangkan skema tabel tanpa menyertakan data apa pun. Misalnya, ini berguna ketika Anda ingin mengekspor hanya struktur tabel untuk menyiapkan lingkungan pengembangan.

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

4.3 --where (-w)

Gunakan opsi ini ketika Anda hanya ingin mencadangkan data yang memenuhi kondisi tertentu. Misalnya, untuk mencadangkan hanya baris di mana kolom is_active bernilai 1:

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

4.4 --ignore-table

Gunakan opsi ini untuk mengecualikan tabel tertentu dari backup. Ini berguna ketika ada tabel yang tidak ingin Anda sertakan.

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

5. Contoh Praktis

5.1 Dump Hanya Tabel Tertentu

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

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

Perintah ini menyimpan hanya 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 Mencadangkan hanya data dan tidak menyertakan struktur tabel.
  • Hanya skema : bash mysqldump -u root -p mydatabase --no-data > schema_only_backup.sql Mencadangkan hanya skema tabel.

5.3 Dump Kondisional

Untuk mencadangkan hanya data yang memenuhi kondisi tertentu, gunakan opsi --where.

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

Perintah ini hanya mencadangkan data di mana created_at pada atau setelah 1 Januari 2023.

6. Cara Memulihkan

Untuk memulihkan basis data yang dicadangkan dengan mysqldump, gunakan perintah mysql. Pemulihan adalah proses menggunakan file cadangan untuk mengembalikan basis data ke keadaan sebelumnya.

6.1 Sintaks Dasar Pemulihan

mysql -u username -p database_name < dump_file.sql
  • -u username : Nama pengguna yang digunakan untuk terhubung ke basis data.
  • -p : Meminta Anda memasukkan kata sandi.
  • database_name : Nama basis data tujuan.
  • < dump_file.sql : File dump yang digunakan untuk pemulihan.

6.2 Contoh: Menjalankan Pemulihan

mysql -u root -p mydatabase < backup.sql

Perintah ini memulihkan data ke dalam mydatabase dari file backup.sql.

6.3 Catatan Penting untuk Pemulihan

  • Jika basis data yang ingin Anda pulihkan tidak ada, Anda harus membuatnya terlebih dahulu.
  • Memulihkan sejumlah besar data dapat memakan waktu, jadi penting untuk merencanakan sebelumnya.

7. Praktik Terbaik untuk mysqldump

7.1 Menjadwalkan Cadangan

Otomatisasikan cadangan rutin dengan menulis skrip mysqldump dan menggunakan penjadwal seperti cron. Pada contoh skrip shell berikut, cadangan penuh semua basis data diambil setiap hari pada tengah malam.

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

7.2 Mengenkripsi File Cadangan

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

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

7.3 Kompatibilitas Versi

Saat memigrasikan data antara versi MySQL yang berbeda, Anda perlu memperhatikan masalah kompatibilitas. Sebelum melakukan upgrade, simulasi prosedur cadangan dan pemulihan di lingkungan uji dan verifikasi kompatibilitas.

  1. Pulihkan definisi tabel : mysqldump --all-databases --no-data --routines --events > dump-defs.sql Perintah ini hanya mengekspor struktur tabel, kemudian Anda memulihkannya di lingkungan versi yang lebih baru untuk memeriksa kompatibilitas.
  2. Pulihkan data : mysqldump --all-databases --no-create-info > dump-data.sql Setelah memastikan definisi tabel kompatibel, pulihkan hanya data.
  3. Validasi di lingkungan uji : Untuk memverifikasi kompatibilitas lintas versi, lakukan cadangan dan pemulihan di lingkungan uji. Setelah memastikan semuanya berfungsi dengan benar, lanjutkan migrasi di lingkungan produksi.

7.4 Menyimpan dan Memverifikasi Cadangan

  • Simpan cadangan dengan aman : Simpan file cadangan di penyimpanan eksternal atau di cloud, dan perbarui secara teratur. Penyimpanan di luar lokasi membantu melindungi data dari kegagalan fisik.
  • Verifikasi pemulihan secara rutin : Lakukan tes pemulihan secara berkala untuk memastikan bahwa cadangan dapat dipulihkan dengan benar. Penting untuk tidak melewatkan verifikasi pemulihan jika cadangan menjadi tidak valid.

8. Pemecahan Masalah

8.1 Kesalahan Umum dan Solusinya

  • Error: @@GLOBAL.GTID_PURGED cannot be changed : Kesalahan ini muncul ketika ada masalah terkait GTID di MySQL 8.0. Anda dapat menghindarinya dengan mengomentari pengaturan GTID menggunakan opsi --set-gtid-purged=COMMENTED. mysqldump -u root -p mydatabase --set-gtid-purged=COMMENTED > backup.sql
  • Error: Not enough disk space : Jika Anda kehabisan ruang disk saat mencadangkan basis data besar, kompres cadangan atau ubah tujuan. Misalnya, Anda dapat mengompres cadangan dengan gzip seperti ini: mysqldump -u root -p mydatabase | gzip > backup.sql.gz
  • Error: Insufficient privileges : Jika pengguna basis data tidak memiliki hak istimewa yang cukup, proses cadangan atau pemulihan akan gagal. Berikan hak istimewa yang diperlukan (seperti SELECT, LOCK TABLES, SHOW VIEW, dll.) dan coba lagi.

8.2 Masalah Kompatibilitas Versi

Masalah kompatibilitas antara versi MySQL yang berbeda dapat diselesaikan dengan melakukan pengujian sebelum melakukan upgrade. Khususnya, saat memigrasi dari MySQL 5.7 ke 8.0, disarankan untuk memulihkan hanya definisi tabel menggunakan opsi --no-data dan memverifikasi kompatibilitas.

  • Uji ketidakcocokan : Sebelum melakukan upgrade, simulasi migrasi di lingkungan pengujian untuk mengidentifikasi potensi masalah. Perhatikan fitur atau sintaks yang tidak kompatibel, dan modifikasi skrip SQL sesuai kebutuhan.

9. Ringkasan

mysqldump adalah alat yang andal dan kuatadangkan dan memulihkan basis data MySQL. Dalam artikel ini, kami membahas segala hal mulai dari penggunaan dasar hingga opsi lanjutan, praktik terbaik, dan pemecahan masalah. Dengan menerapkan pengetahuan ini, Anda dapat melindungi dan mengelola basis data Anda lebih efisien menggunakan mysqldump.

Dengan mengintegrasikan praktik terbaik seperti menjadwalkan pencadangan dan mengenkripsi file, Anda dapat meningkatkan keamanan data dan meningkatkan keandalan operasi basis data Anda. Gunakan mysqldump dengan tepat untuk mempersiapkan potensi masalah basis data.

10. Referensi dan Sumber Daya Tambahan

Rujuk ke sumber ini untuk mempelajari lebih lanjut tentang mysqldump dan menerapkannya dalam situasi dunia nyata. Selain itu, dengan melakukan pencadangan rutin dan verifikasi pemulihan, Anda dapat menjaga keamanan basis data dan siap menghadapi kehilangan data yang tidak terduga.