Mysqldump

mysqldump adalah alat backup yang paling sering dipakai di dunia MySQL — dan alasan pertamanya sederhana: program ini hadir di setiap instalasi server, dari laptop latihan sampai cluster produksi. Seperti yang kita pahami di chapter logical backup, hasil kerjanya adalah file teks berisi statement SQL. Chapter ini mengajak kita mengendarainya dengan sungguh-sungguh: membuat dump pertama, membaca isi file dump baris demi baris sampai tahu persis apa yang dibelinya, lalu mengatur opsi-opsi penting yang membedakan dump sekadang salinan dari dump yang memang layak dipakai pulihkan server.

Menyiapkan Data Latihan #

Kita pakai belajar_backup yang kini dilengkapi dua table dan satu stored procedure — procedure kecil ini sengaja kita tanam untuk menguji sebuah gotcha penting di akhir chapter:

DROP DATABASE IF EXISTS belajar_backup;
CREATE DATABASE belajar_backup;
USE belajar_backup;

Output yang Dihasilkan:

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

Tiga statement pembuka menempatkan kita di database yang bersih. Berikutnya table pertama beserta data produk:

CREATE TABLE produk (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  nama  VARCHAR(100) NOT NULL,
  harga INT NOT NULL,
  stok  INT NOT NULL DEFAULT 0
);

INSERT INTO produk (id, nama, harga, stok) VALUES
  (1, 'Kopi Toraja', 52000, 15),
  (2, 'Teh Melati', 28000, 40),
  (3, 'Gula Aren', 32000, 8);

INSERT INTO produk (nama, harga, stok) VALUES
  ('Susu Kental', 38000, 12),
  ('Kopi Robusta', 48000, 20),
  ('Teh Hijau', 30000, 25);

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Table kedua berisi daftar pelanggan untuk meniru database yang punya lebih dari satu objek:

CREATE TABLE pelanggan (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100) NOT NULL,
  kota VARCHAR(50) NOT NULL
);

INSERT INTO pelanggan (nama, kota) VALUES
  ('Budi', 'Jakarta'),
  ('Siti', 'Bandung'),
  ('Rina', 'Surabaya');

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Terakhir, satu stored procedure kecil yang akan menjadi bintang uji gotcha di paruh akhir chapter:

CREATE PROCEDURE hitung_total_stok() SELECT SUM(stok) AS total_stok FROM produk;

CALL hitung_total_stok();

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

+------------+
| total_stok |
+------------+
|        120 |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Procedure hitung_total_stok ditulis satu baris tanpa blok BEGIN ... END, sehingga cukup dibuat tanpa perintah DELIMITER — trik yang berguna untuk procedure mungil seperti ini. Panggilan CALL mengembalikan 120, jumlah seluruh stok keenam produk, sekaligus membuktikan procedure hidup dan terdaftar di database.

Dump Pertama #

Bentuk paling mendasar dari mysqldump adalah: koneksi ke server, satu nama database sebagai argumen, dan keluaran yang diarahkan ke sebuah file dengan >:

-- @test-bash
mysqldump -h 127.0.0.1 -P 3306 -u root belajar_backup > /tmp/dump-belajar.sql

Perintah ini selesai dalam sekejap dan — perhatikan — tidak mencetak apa pun ke layar. Seluruh hasil dump mengalir ke stdout, dan stdout sudah kita alihkan ke /tmp/dump-belajar.sql; layar yang sunyi itulah tanda dump berjalan baik. Bila terjadi galat, mysqldump justru berbicara lewat stderr, jadi pesan error tetap terlihat meski stdout dialihkan. File target tidak harus berakhiran .sql, tetapi akhiran itu adalah konvensi yang diikuti semua alat dan manusia, jadi kita patuhi.

Membedah Isi File Dump #

File dump yang tampak menakutkan itu sebenarnya terstruktur rapi. Kita intip 30 baris pertamanya — sekalian meneruskannya ke expand supaya karakter tab pada header ditampilkan sebagai spasi biasa:

-- @test-bash
head -n 30 /tmp/dump-belajar.sql | expand

Output yang Dihasilkan:

-- MySQL dump 10.13  Distrib 8.4.11, for Linux (x86_64)
--
-- Host: HOST Database: DB
-- ------------------------------------------------------
-- Server version       8.4.11

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `pelanggan`
--

DROP TABLE IF EXISTS `pelanggan`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `pelanggan` (
  `id` int NOT NULL AUTO_INCREMENT,
  `nama` varchar(100) NOT NULL,
  `kota` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Mari kita bedah bagian-bagian inti file dump tersebut:

1. Baris 1-5 (header komentar) — mencatat versi mysqldump (10.13), distribusi dan platform server, versi server, serta database yang di-dump. Baris ini murni dokumentasi; server mengabaikannya.
2. Baris 7 ke bawah (blok SET pengaman) — belasan statement SET yang dibungkus version-conditional comment seperti /*!40101 ... */. Tanda seru di dalam komentar membuat statement dieksekusi hanya bila versi MySQL minimal 4.1.1 — server lama melewatkannya sebagai komentar biasa. Statement-statement ini menyimpan pengaturan sesi lama (@OLD_...), lalu mengetatkan sesi restore: mematikan pemeriksaan foreign key dan unique checks supaya memuat ulang data lebih cepat dan tidak tersandung urutan table.
3. DROP TABLE IF EXISTS — membuat file dump aman dijalankan ulang di server yang sudah berisi table bernama sama; table lama dijatuhkan lebih dulu.
4. CREATE TABLE — struktur table ditulis utuh, termasuk tipe data, DEFAULT, primary key, nilai AUTO_INCREMENT terakhir, sampai charset dan collation. Bagian inilah yang membuat dump SQL portabel lintas platform.
5. Bagian data di paruh kedua file — pasangan LOCK TABLES ... WRITE / UNLOCK TABLES mengapit INSERT besar berisi seluruh baris table dalam satu statement multi-values.

Kita buktikan dua hal terakhir itu langsung dari file yang sama:

-- @test-bash
grep -E "INSERT INTO|AUTO_INCREMENT=" /tmp/dump-belajar.sql

Output yang Dihasilkan:

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `pelanggan` VALUES (1,'Budi','Jakarta'),(2,'Siti','Bandung'),(3,'Rina','Surabaya');
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `produk` VALUES (1,'Kopi Toraja',52000,15),(2,'Teh Melati',28000,40),(3,'Gula Aren',32000,8),(4,'Susu Kental',38000,12),(5,'Kopi Robusta',48000,20),(6,'Teh Hijau',30000,25);

Empat baris hasil grep membuktikan bentuk data di dalam dump. Kedua table mencatat nilai AUTO_INCREMENT terakhirnya (4 untuk pelanggan, 7 untuk produk) sehingga counter dilanjutkan dari tempat yang sama saat restore, lalu masing-masing table memiliki satu statement INSERT multi-values — semua baris pelanggan dalam satu baris perintah, semua baris produk dalam satu baris perintah. Perhatikan juga urutannya: mysqldump memproses table secara alfabetis, itulah sebabnya pelanggan muncul lebih dulu daripada produk meskipun kita membuatnya belakangan.

Opsi-Opsi Penting #

Kita uji empat opsi yang paling mengubah isi file. Pertama, --databases — perhatikan bahwa dump biasa tidak berisi CREATE DATABASE sama sekali, sedangkan opsi ini menambahkan dua baris ajaib:

-- @test-bash
mysqldump -h 127.0.0.1 -P 3306 -u root --databases belajar_backup > /tmp/dump-db-belajar.sql
grep -E "CREATE DATABASE|^USE" /tmp/dump-db-belajar.sql

Output yang Dihasilkan:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `belajar_backup` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `belajar_backup`;

Kedua, --no-data — skema murni tanpa satu baris data pun; grep -c yang mengembalikan 0 membuktikan tidak ada statement INSERT di dalamnya:

-- @test-bash
mysqldump -h 127.0.0.1 -P 3306 -u root --no-data belajar_backup > /tmp/skema-belajar.sql
grep -c "INSERT INTO" /tmp/skema-belajar.sql

Output yang Dihasilkan:

0

Ketiga, dump selektif per table — sebutkan nama table setelah nama database, dan hanya table itu yang di-dump. wc -l membandingkan panjang dump seluruh database versus dump table tunggal:

-- @test-bash
mysqldump -h 127.0.0.1 -P 3306 -u root belajar_backup produk > /tmp/dump-tabel-belajar.sql
wc -l /tmp/dump-belajar.sql /tmp/dump-tabel-belajar.sql

Output yang Dihasilkan:

  78 /tmp/dump-belajar.sql
  53 /tmp/dump-tabel-belajar.sql
 131 total

Keempat, --compact merapatkan hasil dengan membuang baris komentar dan blok SET — dari 78 baris menjadi jauh lebih ramping:

-- @test-bash
mysqldump -h 127.0.0.1 -P 3306 -u root --compact belajar_backup | wc -l

Output yang Dihasilkan:

21

Gotcha: Stored Procedure Tidak Ikut Secara Default #

Inilah jebakan yang paling sering menyakitkan. Dump standar menyimpan struktur table, data, dan trigger — tetapi stored procedure dan function sama sekali tidak ikut kecuali kita memakai --routines. Kita buktikan dengan procedure yang kita tanam di awal chapter:

-- @test-bash
grep -c "PROCEDURE" /tmp/dump-belajar.sql

Output yang Dihasilkan:

0

Hitungan 0 itu menegaskan: hitung_total_stok tidak ada di file dump standar. Restore dari file ini menghasilkan database yang tampak sehat — table penuh, data utuh — lalu aplikasi mulai gagal saat memanggil procedure yang tidak pernah ikut dipindahkan. Sekarang kita ulangi dump dengan --routines:

-- @test-bash
mysqldump -h 127.0.0.1 -P 3306 -u root --routines belajar_backup > /tmp/dump-rutin-belajar.sql
grep "CREATE DEFINER" /tmp/dump-rutin-belajar.sql

Output yang Dihasilkan:

CREATE DEFINER=`root`@`localhost` PROCEDURE `hitung_total_stok`()

Baris CREATE DEFINER kini hadir — procedure ikut tertulis utuh, termasuk siapa pemiliknya (DEFINER=root@localhost``), sebuah detail yang akan kita bahas lagi saat troubleshooting restore.

–single-transaction: Snapshot Tanpa Kunci #

Opsi ini bekerja lewat mekanisme yang sudah kita kenal di section transaksi. mysqldump membuka transaksi dengan isolation level REPEATABLE READ — default InnoDB — lalu membaca seluruh table dari snapshot konsisten tersebut. Selama dump berjalan, transaksi lain tetap bebas menulis; pembaca kita terus melihat dunia sebagaimana adanya pada detik snapshot diambil, seolah-olah data dibekukan sesaat. Hasilnya adalah dump yang konsisten tanpa mengunci table sama sekali — tidak ada aplikasi yang menunggu, tidak ada antrean yang menumpuk.

Dua catatan penting membatasi kekuatannya. Pertama, konsistensi tanpa kunci ini hanya berlaku untuk table InnoDB; table MyISAM tidak punya versioning, sehingga --single-transaction untuk MyISAM tetap menghasilkan potongan data yang bisa berubah di tengah pembacaan. Kedua, DDL besar (misalnya ALTER TABLE) yang berjalan bersamaan bisa memutus snapshot, sehingga di server yang sangat sibuk, dump tetap perlu dijadwalkan di jam sepi. Untuk database latihan kita yang seluruhnya InnoDB dan hampir tak pernah sibuk, opsi ini murah dan aman untuk selalu menyala.

Kombinasi Lengkap untuk Backup Harian #

Menyambung seluruh opsi di atas, inilah bentuk yang layak menjadi perintah backup harian database kecil-menengah:

-- @test-bash
mysqldump -h 127.0.0.1 -P 3306 -u root \
  --single-transaction \
  --routines --triggers --events \
  --databases belajar_backup \
  > /tmp/dump-lengkap-belajar.sql

Lima keputusan terkandung dalam satu baris lanjutan: snapshot konsisten tanpa mengganggu (--single-transaction), seluruh objek database ikut (--routines --triggers --events), database tercipta sendiri saat restore (--databases), output tertib di satu file, dan tanda backslash \ hanya merapikan perintah panjang menjadi baris-baris yang mudah dibaca. Perintah ini berjalan senyap seperti dump pertama kita — tidak ada kabar berarti kabar baik. Di chapter backup scheduling, baris inilah yang akan kita bungkus skrip, beri jadwal, dan pasang pemantauan.

Referensi #

• mysqldump — A Database Backup Program — MySQL 8.4 Reference Manual
• mysqldump SQL Format — MySQL 8.4 Reference Manual
• Copying a Database from One Server to Another — MySQL 8.4 Reference Manual

Kita kini bisa mencadangkan database dengan percaya diri dan tahu persis isi kantongnya. Namun menghasilkan file hanyalah setengah dari ilmu backup — setengah satunya lagi adalah mengembalikannya. Chapter berikutnya sengaja “membakar” database latihan kita dengan DROP DATABASE, lalu membangkitkannya kembali dari dump sambil mengamati jebakan-jebakan klasik restore: database yang belum ada, checksum yang harus cocok, dan error DEFINER.