Kursus MariaDB / Proyek Akhir: Basis Data Pesanan, Hak Akses, Transaksi, dan Pemulihan

Latihan akhir ini membangun basis data pesanan, menjalankan perubahan stok dan pesanan dalam satu transaksi melalui akun terbatas, lalu mencadangkan keadaan yang sudah dikomit. Pulihkan cadangan ke basis data lain dan periksa struktur, seluruh nilai baris, hak akses, serta total penjualan secara terpisah agar pesan keberhasilan impor tidak menjadi satu-satunya bukti.

Tentukan peran akun dan kriteria penyelesaian

Gunakan basis data latihan baru final48_lab, terpisah dari layanan pesanan atau pembayaran nyata. Anda akan membuat pelanggan, produk, pesanan, dan rincian barang, kemudian mengomit pesanan 105 sebelum pencadangan. Pisahkan akun administrator, akun belajar yang boleh mengelola struktur, dan akun aplikasi dengan operasi terbatas. Aplikasi nyata juga memerlukan validasi masukan, parameter kueri, autentikasi, dan penanganan permintaan berulang agar satu permintaan tidak menggandakan transaksi bisnis.

Pelajari batas pengesahan dan pembatalan melalui latihan transaksi InnoDB dengan COMMIT dan ROLLBACK. Gunakan latihan akun dan GRANT untuk menguji operasi yang memang harus ditolak.

Bagian Hasil yang harus diperiksa
Struktur Empat tabel InnoDB dengan kunci asing dan batasan nilai
Pesanan baru Stok produk 1 berubah dari 20 menjadi 18; nilai barang pesanan 105 adalah 7000
Hak akses Pembacaan, penambahan pesanan dan rincian, serta perubahan kolom stok yang diperlukan
Pemulihan Target kosong terpisah berisi 4, 4, 5, dan 7 baris
Rekonsiliasi Total barang berstatus paid adalah 46000; nilai baris dan batasan cocok

Buat basis data tersendiri dengan data awal lengkap

Pada koneksi administrator ke server latihan, periksa versi dengan SELECT VERSION();, daftar basis data dengan SHOW DATABASES, dan akun yang tersedia. Pastikan nama final48_lab, final48_restore, serta 'final48_app'@'127.0.0.1' belum dipakai. Jika sudah ada, pilih nama latihan lain dan sesuaikan semua blok; jangan menimpa atau menghapus objek yang ada.

CREATE DATABASE final48_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE final48_lab;
CREATE TABLE c48_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c48_order_customer FOREIGN KEY (customer_id)
  REFERENCES c48_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c48_item_order FOREIGN KEY (order_id)
  REFERENCES c48_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c48_item_product FOREIGN KEY (product_id)
  REFERENCES c48_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c48_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c48_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c48_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c48_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

Nama pelanggan, kota, dan barang dalam contoh tetap memakai teks Korea. Produk 1–4 adalah buku catatan, bolpoin, mug, dan lampu meja. Pelanggan 1 dan 3 memiliki nilai kota Seoul, pelanggan 2 Busan, dan pelanggan 4 Daejeon. Pertahankan teks UTF-8 tersebut agar perbandingan hasil tetap sesuai.

Jika diperlukan, jalankan pemberian hak berikut dari koneksi administrator untuk akun belajar course_user. Hak yang sebelumnya diberikan pada shopcourse tidak otomatis berlaku pada basis data baru ini.

GRANT ALL PRIVILEGES ON final48_lab.* TO 'course_user'@'127.0.0.1';

Data awal terdiri dari empat pelanggan, empat produk, empat pesanan, dan enam rincian. Periksa kunci asing serta batasan jumlah, harga, dan stok melalui SHOW CREATE TABLE. Harga katalog saat ini dan unit_price, yaitu harga yang disimpan pada rincian pembelian, mempunyai makna berbeda; nilai historis tidak boleh berubah hanya karena katalog diperbarui.

Hubungkan akun yang dibatasi pada tugas aplikasi

Sebagai administrator, ganti kata sandi demonstrasi dengan nilai unik milik Anda sebelum membuat akun berikut. Akun ini boleh membaca data latihan, menambahkan pesanan dan rincian, serta mengubah stok. Menghapus tabel, mengelola akun, mengganti harga, dan menghapus pesanan bukan bagian dari tugasnya. Periksa keluaran SHOW GRANTS secara pribadi karena dapat memuat informasi autentikasi.

CREATE USER 'final48_app'@'127.0.0.1' IDENTIFIED BY 'Replace_With_Unique_Lab_Password!';
GRANT SELECT ON final48_lab.* TO 'final48_app'@'127.0.0.1';
GRANT INSERT ON final48_lab.c48_orders TO 'final48_app'@'127.0.0.1';
GRANT INSERT ON final48_lab.c48_items TO 'final48_app'@'127.0.0.1';
GRANT UPDATE(stock) ON final48_lab.c48_products TO 'final48_app'@'127.0.0.1';
SHOW GRANTS FOR 'final48_app'@'127.0.0.1';

Buka koneksi baru dari terminal sistem operasi menggunakan akun aplikasi. Masukkan kata sandi saat diminta dan sesuaikan port dengan server latihan:

mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=final48_app --password --default-character-set=utf8mb4 final48_lab

Di dalam klien, periksa SELECT CURRENT_USER(); dan kueri produk. Percobaan ALTER TABLE tanpa hak yang sesuai harus ditolak. Pada aplikasi sebenarnya, batasi tabel, kolom, dan tugas sesuai kebutuhan; pemberian SELECT pada seluruh basis data latihan adalah pilihan eksplisit dalam contoh ini. Lihat cakupan basis data, tabel, dan kolom pada referensi resmi GRANT.

Periksa dan komit stok, pesanan, serta rincian bersama-sama

Jalankan langkah berikut dalam satu koneksi aplikasi yang sama. SELECT FOR UPDATE mengunci baris produk selama transaksi, lalu UPDATE bersyarat mencoba mengurangi stok sebanyak dua unit.

USE final48_lab;
START TRANSACTION;
SELECT product_id,price,stock FROM c48_products WHERE product_id=1 FOR UPDATE;
UPDATE c48_products SET stock=stock-2 WHERE product_id=1 AND stock>=2;
SELECT ROW_COUNT() AS reserved_rows;

Lanjutkan ke blok berikut hanya jika reserved_rows bernilai 1. Jika nol baris berubah atau muncul kesalahan, jalankan ROLLBACK; pada koneksi tersebut dan jangan tambahkan pesanan maupun rinciannya. Periksa ROW_COUNT segera setelah perubahan, seperti pada contoh, agar pernyataan lain tidak menggantikan hasil yang ingin diperiksa.

INSERT INTO c48_orders(order_id,customer_id,ordered_at,status,shipping_fee)
VALUES(105,4,'2026-03-01 10:00:00','paid',1000);
INSERT INTO c48_items(order_id,product_id,quantity,unit_price)
VALUES(105,1,2,3500);
SELECT order_id,SUM(quantity*unit_price) AS goods_total
FROM c48_items WHERE order_id=105 GROUP BY order_id;

Dengan data awal yang tidak diubah, reservasi stok harus mengubah satu baris dan nilai barang pesanan 105 harus 7000.00. Jika salah satu pernyataan gagal, berhenti dan batalkan seluruh percobaan sebelum melanjutkan. Ini adalah latihan yang diawasi dengan masukan yang diketahui; program perlu menjalankan percabangan tersebut sendiri dan mengikat nilai melalui parameter pengandar basis data.

Setelah semua pemeriksaan berhasil, komit perubahan dan baca hasilnya:

COMMIT;
SELECT stock FROM c48_products WHERE product_id=1;
SELECT COUNT(*) FROM c48_orders;
SELECT COUNT(*) FROM c48_items;

Stok produk 1 sekarang 18, jumlah pesanan lima, dan rincian tujuh. Mengulang pesanan dengan ID 105 menyebabkan kesalahan kunci duplikat. Tangani permintaan berulang secara eksplisit, bukan menyembunyikannya dengan IGNORE atau REPLACE. Jika percobaan gagal sudah sempat mengurangi stok, ROLLBACK harus membatalkan pengurangan itu sebelum mencoba ulang. ROLLBACK yang dijalankan sesudah transaksi sebelumnya berhasil dikomit tidak membatalkan pesanan yang telah disahkan; lihat aturan START TRANSACTION, COMMIT, dan ROLLBACK.

Cadangkan keadaan yang sudah dikomit dan siapkan target terpisah

Gunakan akun belajar course_user yang mempunyai hak pencadangan, bukan akun aplikasi terbatas. Pada Windows PowerShell, pilih berkas baru, periksa kode keluar, lalu tinjau dump untuk perintah yang mengalihkan basis data. Jalankan blok ini pada terminal sistem operasi:

New-Item -ItemType Directory -Path 'C:/mariadb-lab' -Force | Out-Null
$courseBackupPath='C:/mariadb-lab/final48-checked.sql'
if (Test-Path -LiteralPath $courseBackupPath) { throw 'Choose a new backup filename.' }
mariadb-dump --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 --single-transaction --quick --skip-lock-tables --result-file=$courseBackupPath final48_lab
if ($LASTEXITCODE -ne 0) { throw 'Backup failed; do not restore this file.' }
Get-Item -LiteralPath $courseBackupPath | Select-Object Name,Length
Get-FileHash -LiteralPath $courseBackupPath -Algorithm SHA256

Melalui koneksi administrator, buat basis data pemulihan yang sudah dipastikan belum dipakai dan berikan hak latihan yang diperlukan:

CREATE DATABASE final48_restore CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON final48_restore.* TO 'course_user'@'127.0.0.1';

Dari terminal, hubungkan klien ke target dengan penghentian pemrosesan berkas SOURCE saat terjadi kesalahan:

mariadb --abort-source-on-error --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 final48_restore

Di dalam klien, periksa nama basis data dan daftar tabel yang kosong sebelum mengimpor berkas tepercaya. Jika target sudah berisi tabel, berhenti.

SELECT DATABASE();
SHOW TABLES;
SOURCE C:/mariadb-lab/final48-checked.sql;

Ketentuan mariadb-dump tetap berlaku untuk snapshot InnoDB yang konsisten dan perubahan struktur secara bersamaan. Panduan pemulihan dan verifikasi menjelaskan kompatibilitas klien serta kegagalan parsial. Dump basis data ini tidak memulihkan seluruh akun atau konfigurasi server. Jangan otomatis mengarahkan akun aplikasi ke hasil pemulihan latihan.

Alternatif pencadangan pada terminal Ubuntu: pilih blok ini atau versi PowerShell. Subshell yang diapit tanda kurung membatasi pengaturan izin berkas dan perlindungan penimpaan pada operasi ini saja.

mkdir -p "$HOME/mariadb-lab"
(
  umask 077
  set -C
  backup_file="$HOME/mariadb-lab/final48-checked.sql"
  if mariadb-dump --protocol=tcp --host=127.0.0.1 --port=3306 \
      --user=course_user --password --default-character-set=utf8mb4 \
      --single-transaction --quick --skip-lock-tables final48_lab > "$backup_file"; then
    test -s "$backup_file" || exit 1
    sha256sum "$backup_file"
  else
    printf '%s\n' 'Backup failed; preserve the partial file for diagnosis.' >&2
    exit 1
  fi
)

Berkas tujuan yang sudah ada menyebabkan kegagalan tanpa menimpa isinya. Jangan memulihkan berkas parsial setelah perintah gagal; periksa penyebabnya dan gunakan nama berkas baru saat mencoba ulang. Ukuran dan hash SHA-256 membantu mengidentifikasi berkas, tetapi tidak membuktikan bahwa berkas dapat dipulihkan dengan benar.

Untuk pemulihan melalui Ubuntu, buka target yang sama dengan perintah berikut. Ganti jalur SOURCE dengan jalur absolut berkas milik Anda; learner hanyalah contoh nama pengguna.

mariadb --abort-source-on-error --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 final48_restore
SELECT DATABASE();
SHOW TABLES;
SOURCE /home/learner/mariadb-lab/final48-checked.sql;

Jalankan hanya satu alternatif pemulihan untuk satu target. Opsi penghentian SOURCE mencegah pernyataan berikutnya dalam berkas dijalankan, tetapi tidak membatalkan objek atau perubahan yang sudah dibuat sebelumnya. Setelah gagal, perbaiki penyebabnya dan ulangi pada target baru yang kosong.

Jika terjadi kesalahan Transport Layer Security (TLS), yaitu enkripsi koneksi, periksa versi, nama sertifikat, otoritas sertifikat tepercaya, dan sertifikat klien bila diwajibkan menurut persyaratan resmi enkripsi koneksi. Terapkan pengaturan yang tepat pada alat pencadangan dan pemulihan tanpa menjadikan penonaktifan verifikasi sebagai solusi rutin. Referensi mariadb-dump menjelaskan opsi dan prasyaratnya.

Bandingkan sumber, hasil pemulihan, dan operasi yang ditolak

SELECT (SELECT COUNT(*) FROM final48_restore.c48_customers) AS customers,
 (SELECT COUNT(*) FROM final48_restore.c48_products) AS products,
 (SELECT COUNT(*) FROM final48_restore.c48_orders) AS orders_count,
 (SELECT COUNT(*) FROM final48_restore.c48_items) AS detail_rows;
SELECT SUM(i.quantity*i.unit_price) AS paid_goods
FROM final48_restore.c48_orders o JOIN final48_restore.c48_items i ON i.order_id=o.order_id
WHERE o.status='paid';
SELECT stock FROM final48_restore.c48_products WHERE product_id=1;
SHOW CREATE TABLE final48_restore.c48_items;

Jumlah baris yang diharapkan adalah 4, 4, 5, dan 7, total barang berstatus paid adalah 46000.00, serta stok produk 1 adalah 18. Bandingkan seluruh nilai kolom berdasarkan kunci, batasan, dan hasil JOIN penting. Jumlah baris sama atau impor berhasil saja belum cukup. Uji secara terpisah bahwa kunci asing yang tidak valid, kunci duplikat, dan perubahan tanpa hak benar-benar ditolak.

Simpan versi, hash dump, kueri dan hasil pemeriksaan, serta cakupan hak akses. Tutup sesi latihan dan periksa kepemilikan serta kebutuhan penyimpanan sebelum menghapus akun atau basis data. Latihan ini tidak otomatis menghapus sumber, cadangan, maupun basis data verifikasi.