Impor dan Ekspor Database MySQL Menggunakan Command Line

Menguasai operasi impor dan ekspor database MySQL dari command line adalah keterampilan yang tidak bisa ditawar bagi administrator database atau engineer backend mana pun. Utilitas `mysqldump` mengekspor database ke dalam file `.sql` yang portabel yang berisi semua pernyataan DDL dan DML yang diperlukan untuk merekonstruksi skema dan data secara penuh, sementara perintah klien `mysql` menangani kebalikannya — mengalirkan file tersebut kembali ke dalam instance MySQL yang sedang berjalan.

Panduan ini mencakup setiap skenario praktis: ekspor database tunggal, dump multi-database, backup hanya struktur, transfer terkompresi, penanganan character set, dan alur kerja impor yang aman — termasuk kasus-kasus khusus yang menyebabkan kerusakan data diam-diam atau kegagalan pemulihan di lingkungan produksi.

Prasyarat

Sebelum menjalankan perintah apa pun dalam panduan ini, verifikasi hal-hal berikut:

• MySQL Server (5.7, 8.0, atau 8.4) telah terinstal dan proses `mysqld` sedang berjalan
• Biner `mysqldump` dan `mysql` ada di `PATH` sistem Anda (konfirmasi dengan `which mysqldump`)
• Anda memiliki akun MySQL dengan setidaknya hak akses `SELECT`, `LOCK TABLES`, `SHOW VIEW`, dan `TRIGGER` untuk ekspor; `CREATE`, `INSERT`, `ALTER`, dan `DROP` untuk impor
• Ruang disk yang cukup tersedia di tujuan — dump terkompresi dapat mengembang 5–10x saat diimpor
• Anda memiliki akses shell ke server (terminal lokal, SSH, atau lingkungan VPS Hosting yang dikelola)

Mengekspor Database dengan mysqldump

`mysqldump` adalah alat backup logis standar yang disertakan bersama MySQL. Alat ini mengubah objek database menjadi skrip SQL yang dapat dibaca manusia. Tidak seperti alat backup fisik seperti Percona XtraBackup, `mysqldump` bersifat agnostik terhadap storage engine dan bekerja di berbagai versi MySQL bahkan fork MariaDB.

1. Ekspor Database Tunggal

“`bash

mysqldump -u [username] -p [database_name] > [filename].sql

“`

Penjelasan parameter:

• `-u [username]` — Akun MySQL yang digunakan untuk koneksi
• `-p` — Memicu prompt password interaktif (jangan pernah meneruskan password secara inline sebagai `-p[password]` pada sistem bersama; hal itu terlihat di output `ps aux`)
• `[database_name]` — Skema target yang akan diekspor
• `> [filename].sql` — Mengalihkan stdout ke file output

Contoh:

“`bash

mysqldump -u root -p mydatabase > mydatabase_backup.sql

“`

Ini menghasilkan file yang berisi pernyataan `CREATE TABLE`, `INSERT`, dan `ALTER TABLE` yang cukup untuk membuat ulang `mydatabase` dari awal.

Kasus khusus kritis: Secara default, `mysqldump` memperoleh global read lock (`FLUSH TABLES WITH READ LOCK`) di awal dump. Pada server InnoDB dengan lalu lintas tinggi, gunakan `–single-transaction` sebagai gantinya untuk mengambil snapshot yang konsisten tanpa memblokir penulisan:

“`bash

mysqldump -u root -p –single-transaction mydatabase > mydatabase_backup.sql

“`

`–single-transaction` hanya bekerja dengan andal pada tabel InnoDB. Jika database Anda berisi tabel MyISAM, lock tidak dapat dihindari.

2. Ekspor Beberapa Database

“`bash

mysqldump -u [username] -p –databases db1 db2 > multiple_databases_backup.sql

“`

Flag `–databases` menginstruksikan `mysqldump` untuk menyertakan pernyataan `CREATE DATABASE` dan `USE` dalam output, membuat dump menjadi mandiri. Tanpa flag ini, pernyataan-pernyataan tersebut dihilangkan dan dump mengasumsikan database target sudah dipilih saat impor.

Contoh:

“`bash

mysqldump -u root -p –databases db1 db2 > multiple_databases_backup.sql

“`

3. Ekspor Semua Database

“`bash

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

“`

Ini mengekspor setiap skema yang dapat diakses oleh pengguna yang terhubung, termasuk database sistem `mysql`, `information_schema`, dan `performance_schema`. Hindari mengimpor database sistem di antara batas versi MySQL utama — skema tabel hak akses berubah secara signifikan antara MySQL 5.7 dan 8.0, dan mengimpor skema `mysql` lama ke dalam instance 8.0 yang baru akan merusak autentikasi.

Untuk mengecualikan skema sistem:

“`bash

mysqldump -u root -p –all-databases

–ignore-table=mysql.user

–ignore-table=mysql.db

> all_user_databases_backup.sql

“`

4. Ekspor Hanya Struktur Tabel (Tanpa Data)

“`bash

mysqldump -u root -p –no-data mydatabase > structure_only.sql

“`

Ini sangat berguna untuk kontrol versi skema, tinjauan kode, atau menyiapkan lingkungan staging yang kosong. Output hanya berisi `CREATE TABLE`, `CREATE VIEW`, `CREATE PROCEDURE`, dan DDL serupa — tanpa baris `INSERT`.

5. Ekspor Tabel Tertentu

“`bash

mysqldump -u root -p mydatabase table1 table2 > tables_backup.sql

“`

Perhatikan bahwa saat mengekspor tabel tertentu dengan cara ini, `mysqldump` tidak menyertakan pernyataan `CREATE DATABASE` atau `USE`. Anda harus memastikan database target sudah ada sebelum mengimpor.

6. Ekspor dengan Stored Procedure, Trigger, dan Event

Secara default, `mysqldump` menyertakan trigger tetapi menghilangkan stored procedure, fungsi, dan scheduled event. Untuk backup aplikasi yang lengkap:

“`bash

mysqldump -u root -p

–routines

–triggers

–events

–single-transaction

mydatabase > full_backup.sql

“`

Melupakan `–routines` adalah salah satu penyebab paling umum dari kegagalan pemulihan aplikasi — skema dan data ada tetapi logika bisnis hilang.

7. Ekspor Terkompresi

Alirkan output langsung melalui `gzip` untuk mengurangi ukuran file sebesar 60–80%:

“`bash

mysqldump -u root -p mydatabase | gzip > mydatabase_backup.sql.gz

“`

Untuk kompresi maksimum pada database besar (dengan biaya waktu CPU):

“`bash

mysqldump -u root -p mydatabase | gzip -9 > mydatabase_backup.sql.gz

“`

Mengimpor Database dengan Klien mysql

Klien command-line `mysql` menerima file SQL melalui pengalihan stdin dan mengeksekusi setiap pernyataan secara berurutan terhadap server target.

1. Impor ke Database yang Sudah Ada

“`bash

mysql -u [username] -p [database_name] < [filename].sql

“`

Contoh:

“`bash

mysql -u root -p mydatabase < mydatabase_backup.sql

“`

Penting: Jika file `.sql` diekspor dengan `–databases` atau `–all-databases`, file tersebut sudah berisi direktif `CREATE DATABASE` dan `USE`. Dalam hal itu, jangan tentukan nama database di command line — melakukannya akan menimbulkan konflik:

“`bash

mysql -u root -p < all_databases_backup.sql

“`

2. Impor ke Database Baru

Database target harus ada sebelum Anda dapat mengimpor ke dalamnya. MySQL tidak akan membuatnya secara otomatis dari dump tabel biasa.

Langkah 1 — Buat database:

“`bash

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

“`

Langkah 2 — Impor dump:

“`bash

mysql -u root -p newdatabase < mydatabase_backup.sql

“`

Selalu tentukan character set dan collation secara eksplisit saat membuat database. Mengandalkan default server adalah sumber umum ketidakcocokan encoding, terutama saat bermigrasi antara server dengan konfigurasi `character_set_server` yang berbeda.

3. Impor Dump Terkompresi

“`bash

gunzip < mydatabase_backup.sql.gz | mysql -u root -p mydatabase

“`

Atau menggunakan `zcat` (setara pada sebagian besar distribusi Linux):

“`bash

zcat mydatabase_backup.sql.gz | mysql -u root -p mydatabase

“`

4. Impor dengan Pemantauan Progres

Untuk dump besar, klien `mysql` standar tidak memberikan umpan balik. Gunakan `pv` (pipe viewer) untuk menampilkan progress bar secara real-time:

“`bash

pv mydatabase_backup.sql | mysql -u root -p mydatabase

“`

Instal `pv` dengan `apt install pv` atau `yum install pv`. Pada Dedicated Server yang menangani database produksi berukuran multi-gigabyte, visibilitas ini sangat penting secara operasional.

mysqldump vs. Metode Backup Alternatif

Untuk lingkungan yang menjalankan beberapa database produksi pada VPS dengan cPanel yang dikelola, `mysqldump` tetap menjadi opsi yang paling didukung secara universal karena kompatibilitas dan kesederhanaannya.

Konfigurasi Lanjutan dan Kasus Khusus

Menangani Character Set dengan Benar

Ketidakcocokan character set bertanggung jawab atas sebagian besar impor yang rusak. Pendekatan paling aman adalah bersikap eksplisit di setiap tahap:

“`bash

mysqldump -u root -p

–default-character-set=utf8mb4

mydatabase > mydatabase_backup.sql

“`

“`bash

mysql -u root -p

–default-character-set=utf8mb4

mydatabase < mydatabase_backup.sql

“`

Catatan: `utf8` di MySQL adalah subset 3-byte yang tidak dapat menyimpan karakter Unicode 4-byte (emoji, ideograf CJK tertentu). Selalu gunakan `utf8mb4` untuk database baru.

Mempercepat Impor Besar

Secara default, MySQL melakukan commit penuh setelah setiap pernyataan `INSERT` dalam dump. Untuk dataset besar, ini sangat lambat. Tambahkan hal berikut di awal sesi impor Anda:

“`bash

mysql -u root -p mydatabase <<EOF

SET foreign_key_checks = 0;

SET unique_checks = 0;

SET autocommit = 0;

SOURCE /path/to/mydatabase_backup.sql;

COMMIT;

SET foreign_key_checks = 1;

SET unique_checks = 1;

EOF

“`

Atau, ekspor dengan `–extended-insert` (diaktifkan secara default) dan `–disable-keys` untuk mengelompokkan insert dan menunda pembangunan ulang indeks hingga setelah pemuatan data.

Mengotomatiskan Backup dengan Cron

Entri backup otomatis tingkat produksi di `/etc/cron.d/mysql-backup`:

“`bash

0 2 * * * root mysqldump -u backup_user -p'StrongPass'

–single-transaction –routines –triggers –events

mydatabase | gzip > /backups/mydatabase_$(date +%F).sql.gz

“`

Gunakan pengguna MySQL khusus dengan hak akses minimum yang diperlukan daripada `root`. Buat dengan:

“`sql

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPass';

GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, EVENT ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

“`

Mengamankan Kredensial dengan .my.cnf

Meneruskan password di command line mengeksposnya dalam riwayat shell dan daftar proses. Simpan kredensial di `~/.my.cnf`:

“`ini

[client]

user=root

password=YourSecurePassword

“`

Atur izin segera:

“`bash

chmod 600 ~/.my.cnf

“`

Dengan ini, semua perintah `mysqldump` dan `mysql` mengambil kredensial secara otomatis tanpa flag `-u` dan `-p`.

Ekspor Database Jarak Jauh

Untuk melakukan dump database dari server MySQL jarak jauh:

“`bash

mysqldump -h remote.server.com -P 3306 -u remoteuser -p remotedatabase > remote_backup.sql

“`

Pastikan instance MySQL jarak jauh mengizinkan koneksi dari alamat IP Anda dan port 3306 terbuka di firewall. Untuk transfer terenkripsi, terowongi melalui SSH:

“`bash

ssh -L 3307:127.0.0.1:3306 user@remote.server.com -N &

mysqldump -h 127.0.0.1 -P 3307 -u remoteuser -p remotedatabase > remote_backup.sql

“`

Matriks Keputusan Praktis

Poin Teknis Utama

• Selalu gunakan `–single-transaction` untuk database InnoDB guna menghindari pemblokiran penulisan aplikasi selama ekspor
• Selalu tentukan `utf8mb4` secara eksplisit — jangan pernah mengandalkan asumsi character set default server
• Sertakan `–routines`, `–triggers`, dan `–events` dalam setiap backup aplikasi penuh atau Anda berisiko kehilangan logika bisnis
• Jangan pernah mengimpor tabel skema sistem (`mysql.*`) di antara batas versi MySQL utama
• Simpan kredensial di `~/.my.cnf` dengan `chmod 600` — jangan pernah meneruskan password sebagai argumen inline
• Untuk database yang melebihi 10 GB, evaluasi `mysqlpump` atau utilitas dump paralel MySQL Shell karena `mysqldump` akan menjadi bottleneck
• Verifikasi setiap backup dengan melakukan pemulihan uji ke lingkungan terisolasi sebelum mengandalkannya untuk pemulihan bencana
• Saat menghosting beberapa database klien, isolasi lingkungan menggunakan VPS Control Panel terpisah untuk mencegah akses lintas penyewa selama operasi pemulihan
• Padukan strategi backup database Anda dengan Sertifikat SSL yang valid pada lapisan aplikasi yang menghadap web yang terhubung ke MySQL untuk mencegah intersepsi kredensial saat transit

FAQ

Apa perbedaan antara mysqldump dan mysqlpump?

`mysqldump` bersifat single-threaded dan menghasilkan satu file SQL — andal dan kompatibel secara universal. `mysqlpump`, yang diperkenalkan di MySQL 5.7.8, mendukung ekspor paralel dari beberapa database dan tabel secara bersamaan, secara signifikan mengurangi waktu dump pada server multi-core. Namun, `mysqlpump` memiliki masalah yang diketahui dengan backup konsisten dari database mesin campuran dan kurang cocok untuk migrasi lintas versi.

Bisakah saya mengimpor dump MySQL 5.7 ke MySQL 8.0?

Ya, dengan beberapa catatan. Data pengguna dan skema aplikasi diimpor dengan bersih. Namun, jangan pernah mengimpor database sistem `mysql` secara langsung — plugin autentikasi berubah dari `mysql_native_password` menjadi `caching_sha2_password` di 8.0, dan mengimpor tabel hak akses lama akan merusak autentikasi. Buat ulang pengguna secara manual menggunakan pernyataan `CREATE USER` dan `GRANT`.

Mengapa impor saya gagal dengan "ERROR 1005: Can't create table" karena batasan foreign key?

Ini terjadi ketika tabel diimpor dalam urutan yang melanggar dependensi foreign key. Solusinya adalah menambahkan `SET foreign_key_checks = 0;` di awal sesi impor Anda dan menambahkan `SET foreign_key_checks = 1;` setelah selesai. Atau, ekspor dengan `–single-transaction` yang mempertahankan integritas referensial dalam file dump itu sendiri.

Bagaimana cara mengekspor hanya data tanpa pernyataan CREATE TABLE?

Gunakan flag `–no-create-info`: `mysqldump -u root -p –no-create-info mydatabase > data_only.sql`. Ini berguna ketika Anda perlu memuat ulang data ke dalam skema yang sudah ada tanpa mengubah strukturnya.

Apa cara paling aman untuk mentransfer dump MySQL antara dua server jarak jauh?

Alirkan dump langsung melalui SSH tanpa menulis file perantara ke disk: `mysqldump -u root -p sourcedb | ssh user@destination.server "mysql -u root -p targetdb"`. Ini lebih cepat dan lebih aman daripada menyalin file `.sql` teks biasa, terutama saat beroperasi di lingkungan Shared Web Hosting di mana kuota disk menjadi kendala.