Скачать или смотреть Tugas 3 Basis Data - commit rollback dan replika database mysql

TUGAS 3 PRAKTIKUM BASIS DATA | TRANSAKSI MYSQL | TRIGGER | REPLIKASI MASTER SLAVE

Assalamualaikum wr wb.
Nama saya Tofik Nuryanto. Ini adalah video penjelasan dan demonstrasi Tugas 3 Mata Kuliah Basis Data.
Video ini membahas dua materi utama:
1. Praktikum Transaksi pada MySQL
2. Praktikum Basis Data Terdistribusi (Master Slave Replication)

==================================================
PRAKTIKUM TRANSAKSI MYSQL
==================================================

Konsep transaksi dalam MySQL mencakup:
BEGIN
START TRANSACTION
COMMIT
ROLLBACK
END

Contoh procedure transaksi:
DELIMITER $$
CREATE PROCEDURE transaksiBaru()
BEGIN
START TRANSACTION;
INSERT INTO pasien (id_nama_pasien, alamat_pasien, jenis_kelamin)
VALUES ('Bukan Tito', 'Kl. Pakis Haji no 23', '1');
SELECT * FROM pasien;
END $$
DELIMITER ;

Penjelasan singkat:
COMMIT = transaksi disimpan
ROLLBACK = membatalkan transaksi jika terjadi error

Jenis trigger pada MySQL:
BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE
AFTER DELETE

Contoh tabel obat:
CREATE TABLE obat(
id_obat int primary key auto_increment,
kode_obat varchar(5),
nama_obat varchar(30),
harga int,
stok int
);

Contoh tabel transaksi obat:
CREATE TABLE transaksi_obat(
id_transaksi int primary key auto_increment,
id_pasien int,
id_obat int,
jumlah int,
total_harga int,
foreign key (id_pasien) references pasien(id_pasien),
foreign key (id_obat) references obat(id_obat)
);

Trigger pengurangan stok otomatis:
CREATE TRIGGER triggerStokObat
AFTER INSERT ON transaksi_obat
FOR EACH ROW
BEGIN
UPDATE obat SET stok = stok - NEW.jumlah
WHERE id_obat = NEW.id_obat;
END;

Contoh insert data:
INSERT INTO obat (kode_obat, nama_obat, harga, stok) VALUES
("A001","Amikacin",20000,10),
("A002","Amoxilin",15000,20),
("A003","Betadine",7000,15);

Test trigger:
INSERT INTO transaksi_obat (id_pasien, id_obat, jumlah, total_harga)
VALUES (4,2,5,0);

==================================================
PRAKTIKUM BASIS DATA TERDISTRIBUSI
REPLIKASI MASTER SLAVE MYSQL
==================================================

Konsep:
Master = database utama
Slave = replika otomatis

Konfigurasi master (Windows):
Tambahkan pada my.ini:
server-id=1
log-bin=mysql-bin
binlog_do_db=nama_database

Buat user replikasi:
CREATE USER 'replica'@'%' IDENTIFIED BY 'PASSWORD_kuat@123';
GRANT REPLICATION SLAVE ON . TO 'replica'@'%';
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Konfigurasi slave (Ubuntu):
Edit my.cnf:
server-id=2
replicate_do_db=nama_database

Restart:
sudo systemctl restart mysql

Set koneksi ke master:
STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='192.168.18.2',
MASTER_USER='replica',
MASTER_PASSWORD='PASSWORD_kuat@123',
MASTER_LOG_FILE='DESKTOP-EBTOI7T-bin.000480',
MASTER_LOG_POS=24370;
START SLAVE;
SHOW SLAVE STATUS;

Cek status:
Slave IO Running = Yes
Slave SQL Running = Yes

==================================================
SHARE FOLDER HYPER-V (CIFS)
==================================================
Saya menggunakan HyperV dan sharing folder menggunakan cifs.

sudo mount -t cifs //IP_WINDOWS/NamaFolderShare ~/TargetFolder -o username=USERNAME_WINDOWS,uid=$(id -u)

==================================================
praktikum basis data
database mysql
transaksi mysql
trigger mysql
master slave mysql
replication mysql
kuliah basis data
tutorial mysql indonesia
cara membuat trigger mysql
cara konfigurasi master slave mysql