.::: Install MariaDB GTID Mirroring Monitoring using php :::.

 

install php https://infotechys.com/install-php-8-3-on-rhel-9-centos-9/

1. create bash monitoring 

[root@teguhth03 web]# cat /data/web/server31.sh
#!/bin/bash

# Variabel koneksi
USER="admin"
PASS="xxxxx"
HOST="10.10.10.31"
PORT="3306"

# Test koneksi ke MySQL server
mysql --skip-ssl -u$USER -p$PASS -h$HOST -P$PORT -e "SELECT 1;" >/dev/null 2>&1

 if [ $? -ne 0 ]; then
    echo "{ \"connections\": \"\", \"role\": \"Down\", \"gtid\": \"\",\"hostname\": \"$hostnamex\", \"host\": \"$HOST\", \"port\": \"$PORT\",\"dbv\": \"$versionx\",\"osv\": \"$osx\" }"
    exit 0
fi
 

.::: Install Microsoft SQL Server 2022 in Centos 9 / Redhat 9 / Almalinux 9 :::.

 

1. disable selinux

2. enable repo 

curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/$(rpm -E %{rhel})/mssql-server-2022.repo

curl https://packages.microsoft.com/config/rhel/8/prod.repo | sudo tee /etc/yum.repos.d/mssql-release.repo

curl https://packages.microsoft.com/config/rhel/9/prod.repo | sudo tee /etc/yum.repos.d/mssql-release.repo

[root@teguhth data]# curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/$(rpm -E %{rhel})/mssql-server-2022.repo
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   444  100   444    0     0    199      0  0:00:02  0:00:02 --:--:--   199
[root@teguhth data]#
[root@teguhth data]#
[root@teguhth data]# cat /etc/yum.repos.d/mssql-server.repo
[packages-microsoft-com-mssql-server-2022]
name=Microsoft SQL Server 2022
baseurl=https://packages.microsoft.com/rhel/9/mssql-server-2022/
enabled=1
gpgcheck=1
repo_gpgcheck=1
# If installing the bare repo file consider installing the gpg key once and then updating
# this to point to the on-disk location. For example:
# gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-Microsoft
gpgkey=https://packages.microsoft.com/keys/microsoft.asc
sslverify=1
[root@teguhth data]#

.::: Install MariaDB 10.6 in Ubuntu 22 :::.

  

1. Tambahkan GPG Key mariadb

sudo apt install -y curl software-properties-common gnupg
curl -fsSL https://mariadb.org/mariadb_release_signing_key.asc | sudo gpg --dearmor -o /usr/share/keyrings/mariadb-keyring.gpg

2. Tambahkan Repository mariadb 10.6

deb [arch=amd64,arm64 signed-by=/usr/share/keyrings/mariadb-keyring.gpg] http://mirror.mariadb.org/repo/10.6/ubuntu noble main
 

.::: Script Backup & Restore MariaDB using Confirm before Action Execute :::.

 

A. Action 

1. test restore not found
 

 

.::: Query Check day, hour & early time, check time, different time in MSSQL, MariaDB MySQL, PostgreSQL EDB, Oracle DB to Get the Start of the Current Hour and the Previous Hour :::.

  

A. MSSQL

1. one day before

select @@servername as ServerName, GETDATE() as Currentdate, CAST(CONVERT(date, GETDATE() - 1) AS datetime) as datebefore , CAST(CONVERT(date, GETDATE()) AS datetime) as datecurrent 

INSERT INTO target_table (col1, col2, col3, tanggal)
SELECT col1, col2, col3, tanggal
FROM source_table
WHERE tanggal >= CAST(CONVERT(date, GETDATE() - 1) AS datetime) 
  AND tanggal <  CAST(CONVERT(date, GETDATE()) AS datetime);

.::: Simulation Query Select from Java to MariaDB using mariadb-java-client-3.5.3.jar (MariaDB Driver) using MariaDB SSL Certificate :::.

 

correlation https://teguhth.blogspot.com/2025/06/simulation-query-select-from-java-to.html
https://teguhth.blogspot.com/2025/06/simulation-connection-java-to-mariadb.html
    
1. Konversi client-cert.pem dan client-key.pem ke .p12

 

openssl pkcs12 -export -in /home/user/certs/client-cert.pem -inkey /home/user/certs/client-key.pem -out /home/user/certs/client-cert.p12 -name client -CAfile /home/user/certs/ca-cert.pem -caname root -passout pass:root

.::: Create User MariaDB MySQL using SSL / Certificate :::.

 

Mengaktifkan TLS/SSL pada MariaDB 10.6 dan jika sudah nanti akan di test dengan user 'adminssl' password 'adminssl' 

Berikut adalah panduan mengaktifkan TLS/SSL di MariaDB 10.6 dan menguji koneksi dengan user adminssl:

1. Buat Direktori & Sertifikat SSL

mkdir -p /etc/mysql/ssl

cd /etc/mysql/ssl

2. Create Certificate 
 

.::: Script Daily Backup MongoDB using Date or Compress :::.

  

1. Script backup full 

#[root@teguhth mongod]# cat backup_mongodb_full_script.sh
##-bash-4.2$ cat backup_mongodb_full_script.sh
echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address
 

.::: Sample shell script integration MariaDB to Redis :::.

 

1. run script using mariadb
sh compare_maria_redis.sh mariadb
 

2. run script using redis
sh compare_maria_redis.sh redis
 

.::: Install MongoDB 8 in Centos 9 (mongodb-org-server, mongodb-mongosh) :::.

  

1. download

wget https://repo.mongodb.org/yum/redhat/9/mongodb-org/8.0/x86_64/RPMS/mongodb-org-server-8.0.10-1.el9.x86_64.rpm

2. create repository

[root@teguhth ~]# cat /etc/yum.repos.d/mongodb-org.repo
[mongodb-org-6.0]
name=MongoDB Repository
#baseurl=https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/6.0/x86_64/
baseurl=https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/9.0/x86_64/
gpgcheck=0
enabled=1
gpgkey=https://www.mongodb.org/static/pgp/server-6.0.asc
[root@teguhth ~]#

.::: Start the SQL Server Import and Export Wizard From MariaDB to MSSQL using Write a query to specify the data to transfer & compare with Open Query :::.

 

correlation with https://teguhth.blogspot.com/2025/06/start-sql-server-import-and-export.html

A. Prepare

1. MariaDB check sample query 

select * from products where product_name='laptop' or product_name='tablet';

2. create view mariadb 

create view view_produk select * from products where product_name='laptop' or product_name='tablet';

3. test if using open query

select * from openquery([MARIALOCAL],'select * from erpdb.view_produk;')
select * from openquery([MARIALOCAL],'select * from erpdb.products where product_name=''laptop'' or product_name=''tablet'';')
  

.::: Start the SQL Server Import and Export Wizard From MSSQL to MariaDB :::.

 

1. Install ODBC Driver MariaDB MySQL (32 bits) for this case install mysql-connector-odbc-8.0.42-win32

mysql-connector-odbc-8.0.42-win32
mariadb-connector-odbc-3.2.6-win32

2. Setup DSN MariaDB Using MySQL Driver 

# using mysql driver 32 >> pakai yang ini 
Dsn=mysql321;Driver={MySQL ODBC 8.0 Unicode Driver};server=10.10.10.7;uid=admin;pwd=admin;database=erpdb;port=3306

# using maridb driver32
Dsn=maria;Driver={MariaDB ODBC 3.2 Driver};server=10.10.10.7;uid=admin;pwd=admin;database=erpdb;port=3306
 

.::: Shell Script to compare table Database MariaDB MySQL :::.

 

  correlation with https://teguhth.blogspot.com/2025/03/query-to-check-table-column-compare_28.html

A. Compare Multiple Table 
1. check table server1
 

 

.::: Shell Script to compare value table a Database MariaDB MySQL :::.

  

correlation with https://teguhth.blogspot.com/2025/03/query-to-check-table-column-compare_28.html

1. check value table barang server1
 

 

.::: Trial Backup & Restore MariaDB MySQL with crontab -l / crontab -e & /etc/crontab :::.

 

A. Prepare Script backup & Restore

1. create backup script

[root@teguhth cron]# pwd
/data/cron
[root@teguhth cron]# cat backup_test.sh
echo "backup full mulai" > log_backup.txt
date >> log_backup.txt

mysqldump -uroot -pxxx -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --routines=true --triggers=true --no-data=false teguhth | gzip -c > teguhth_ori.sql.gz ;

echo "backup full selesai " >> log_backup.txt
date  >> log_backup.txt
[root@teguhth cron]#

.::: Simulation Query Select from Java to MariaDB using mariadb-java-client-3.5.3.jar (MariaDB Driver) :::.

  

https://teguhth.blogspot.com/2025/06/simulation-connection-java-to-mariadb.html

1. Script java 

[root@teguhth maria]# cat queryjoin.java
import java.sql.*;

public class queryjoin {
    public static void main(String[] args) {
        String url = "jdbc:mariadb://10.10.10.90:3306/teguhth"; // Ganti IP jika perlu
        String user = "admin";
        String pass = "admin";

        try {
            Connection conn = DriverManager.getConnection(url, user, pass);

            // Jalankan query
//            String query = "select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;";

//String query = "SELECT b.NAMA_BARANG, s.NAMA_SUPLIER, p.TANGGAL_PASOK, p.JUMLAH_PASOK, (SELECT SLEEP(20)) as DELAY FROM barang b, suplier s, pasok p WHERE b.KODE_BARANG = p.KODE_BARANG AND s.KODE_SUPLIER = p.KODE_SUPLIER;";

//String query ="select * from pembelian;";
 

.::: Simulation connection java to MariaDB for Success & Fail using mysql-connector-j-8.0.33.jar (MySQL Driver) (com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure. The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.) :::..

 

1. Download (Platform independent) MySQL Driver

https://repo1.maven.org/maven2/com/mysql/mysql-connector-j/8.0.33/mysql-connector-j-8.0.33.jar

java -version
dnf install java-17-openjdk-devel -y    # CentOS 9

2. check login mariadb

[root@teguhth ~]# mysql -h 10.10.10.90 -uadmin -p teguhth
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 11.4.4-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

.::: Simulation connection java to MariaDB for Success & Fail using mariadb-java-client-3.5.3.jar (MariaDB Driver) (java.sql.SQLNonTransientConnectionException: Socket fail to connect to 10.10.10.9. No route to host) :::.

 

1. Download (Platform independent)

https://mariadb.com/downloads/connectors/connectors-data-access/java8-connector
 

java -version
dnf install java-17-openjdk-devel -y    # CentOS 9

2. check login mariadb

[root@teguhth ~]# mysql -h 10.10.10.90 -uadmin -p teguhth
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 11.4.4-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

.::: Move TempDB to other folder (move SQL Server MDF and LDF Files location):::.

 

 
correlation https://teguhth.blogspot.com/2023/06/move-sql-server-mdf-and-ldf-files.html

1. check 

sp_helpdb tempdb

USE tempdb;
GO
SELECT 
    name AS [Logical Name],
    physical_name AS [File Path],
    type_desc AS [File Type],
    size * 8 / 1024 AS [Size (MB)],
    max_size,
    growth,
    is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');

.::: Quary query to simulation High CPU & High Memory Usage In EDB PostgreSQL :::.

 

 
A. Simulation High CPU

1. Contoh query untuk Simulasi CPU Tinggi
 
Untuk mensimulasikan CPU utilization tinggi di PostgreSQL menggunakan query, kamu bisa menjalankan perintah yang bersifat berat secara berulang, misalnya:

PERINGATAN!
Skrip di bawah akan membuat CPU usage PostgreSQL tinggi, gunakan di lingkungan development/test, bukan production.


CREATE OR REPLACE PROCEDURE cpu_hog()
LANGUAGE plpgsql
AS $$
DECLARE
    start_time timestamp := clock_timestamp();
    elapsed interval;
    i integer := 0;
    j double precision := 0;
BEGIN
    LOOP
        j := sqrt(i * random());
        i := i + 1;

        elapsed := clock_timestamp() - start_time;
        EXIT WHEN elapsed >= interval '20 seconds';
    END LOOP;
END;
$$;
 

.::: Shell Script Kasir cashier with & Without MariaDB :::.

 

A. Simple Cashier without Database

1. Struktur File listbarang.txt

2. Script: kasir.sh
 
[root@teguhth program]# cat kasir.sh

#!/bin/bash

file_barang="listbarang.txt"

# Fungsi untuk menampilkan daftar barang
tampilkan_barang() {
    echo -e "\nDaftar Barang:"
    echo "---------------------------"
    while IFS="|" read -r id nama harga
    do
        echo "$id. $nama - Rp$harga"
    done < "$file_barang"
    echo "---------------------------"
}

.::: Quary to simulation High CPU & High Memory Usage In MySQL MariaDB :::.

 

A. Simulation High CPU

1. Contoh untuk Simulasi CPU Tinggi
 
Untuk mensimulasikan CPU utilization tinggi di SQL Server menggunakan T-SQL, kamu bisa menjalankan perintah yang bersifat berat secara berulang, misalnya:

PERINGATAN!
Skrip di bawah akan membuat CPU usage SQL Server tinggi, gunakan di lingkungan development/test, bukan production.

USE teguhth;
DELIMITER $$

DROP PROCEDURE IF EXISTS CPU_HOG$$

CREATE PROCEDURE CPU_HOG()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE j DOUBLE DEFAULT 0;

    WHILE i < 10000000 DO
        SET j = SQRT(i * RAND());
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

.::: Quary T-SQL to simulation High CPU & High Memory Usage In SQL Server :::.

 

A. Simulation High CPU

1. Contoh T-SQL untuk Simulasi CPU Tinggi
 
Untuk mensimulasikan CPU utilization tinggi di SQL Server menggunakan T-SQL, kamu bisa menjalankan perintah yang bersifat berat secara berulang, misalnya:

PERINGATAN!
Skrip di bawah akan membuat CPU usage SQL Server tinggi, gunakan di lingkungan development/test, bukan production.

USE tempdb;
GO

IF OBJECT_ID('dbo.CPU_HOG') IS NOT NULL
    DROP PROCEDURE dbo.CPU_HOG;
GO

CREATE PROCEDURE dbo.CPU_HOG
AS
BEGIN
    DECLARE @i INT = 0;
    DECLARE @j FLOAT = 0;

    WHILE (@i < 10000000)
    BEGIN
        SET @j = SQRT(@i * RAND()); -- operasi matematika berat
        SET @i = @i + 1;
    END
END;
GO
 

.::: Insert data table to another table with different Database in Oracle Database :::.

 

A. Without Date

1. Check

select * from pembelian;
 

2. table after create
CREATE TABLE pembelian_clone (
    KODE_PEMBELIAN     CHAR(10) NOT NULL,
    KODE_BARANG        CHAR(6) NOT NULL,
    KODE_CUSTOMER      CHAR(6) NOT NULL,
    TANGGAL_PEMBELIAN  DATE DEFAULT NULL,
    JUMLAH_PEMBELIAN   DECIMAL(4,0) DEFAULT NULL
);
 

.::: Insert data table to another table with different Database in PostgreSQL EDB :::.

 

A. Without Date

1. Check

select * from pembelian;
 

2. table after create
CREATE TABLE pembelian_clone (
    KODE_PEMBELIAN     CHAR(10) NOT NULL,
    KODE_BARANG        CHAR(6) NOT NULL,
    KODE_CUSTOMER      CHAR(6) NOT NULL,
    TANGGAL_PEMBELIAN  DATE DEFAULT NULL,
    JUMLAH_PEMBELIAN   DECIMAL(4,0) DEFAULT NULL
);
 

.::: Insert data table to another table with different Database in MariaDB :::.

 

A. Without Date

1. Check

select * from teguhth.pembelian;
 
2. table after create
CREATE TABLE pembelian_clone (
    KODE_PEMBELIAN CHAR(10) NOT NULL,
    KODE_BARANG CHAR(6) NOT NULL,
    KODE_CUSTOMER CHAR(6) NOT NULL,
    TANGGAL_PEMBELIAN DATE DEFAULT NULL,
    JUMLAH_PEMBELIAN DECIMAL(4,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 

.::: Setting up and using the mutt email client in Linux for alert NMS or Other :::.

 

Berikut contoh penggunaan mutt untuk mengirim email dari command line di CentOS 9:

A. Setup

1. update repository

sudo dnf update -y

2. setup mutt

sudo dnf install mutt -y

3. check

mutt -v
 

.::: how to use DBMS_CRYPTO.ENCRYPT dan DBMS_CRYPTO.DECRYPT in Oracle as example table pembelian :::.

 

Oke! Saya akan kasih kamu contoh penggunaan DBMS_CRYPTO.ENCRYPT dan DBMS_CRYPTO.DECRYPT pada tabel pembelian. Di sini, kita akan menyimpan kolom credit_card_number dalam bentuk terenkripsi, dan mendekripsinya saat dibaca.

Studi Kasus: Tabel pembelian_card

1. grant access

GRANT EXECUTE ON DBMS_CRYPTO TO admin;
commit;

2. Buat tabel:

CREATE TABLE pembelian_card (
    id               NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nama_pembeli     VARCHAR2(100),
    credit_card_enc  RAW(2000) -- data terenkripsi
);

.::: Backup restore Export import expdp impdp from schema to another schema in oracle using crontab :::.

 

correlation https://teguhth.blogspot.com/2024/12/backup-restore-export-import-expdb.html


1. backup manual

[oracle@teguhth ~]$ expdp aisyah/hanin@localhost:1521/tgh DIRECTORY=DATA_PUMP_DIR DUMPFILE=aisyah_schema.dmp LOGFILE=aisyah_schema.log SCHEMAS=aisyah

Export: Release 19.0.0.0.0 - Production on Tue Apr 22 14:10:38 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "AISYAH"."SYS_EXPORT_SCHEMA_01":  aisyah/********@localhost:1521/tgh DIRECTORY=DATA_PUMP_DIR DUMPFILE=aisyah_schema.dmp LOGFILE=aisyah_schema.log SCHEMAS=aisyah
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "AISYAH"."BARANG"                           6.984 KB       6 rows
. . exported "AISYAH"."CUSTOMER"                         7.554 KB       6 rows
. . exported "AISYAH"."EMPLOYEES"                        6.359 KB       2 rows
. . exported "AISYAH"."EMPLOYEES_CHANGE_LOG"             8.312 KB       5 rows
. . exported "AISYAH"."PASOK"                            7.695 KB      12 rows
. . exported "AISYAH"."PEMBELIAN"                        7.835 KB      15 rows
. . exported "AISYAH"."SUPLIER"                          7.578 KB       6 rows
Master table "AISYAH"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for AISYAH.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/backup/aisyah_schema.dmp
Job "AISYAH"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Apr 22 14:11:39 2025 elapsed 0 00:00:58

[oracle@teguhth ~]$
 

.::: Enable Change Data Capture (CDC) Oracle Database using TRIGGER with session_user & client_ip :::.

 

Berikut adalah langkah-langkah untuk mengaktifkan Change Data Capture (CDC) di Oracle menggunakan Trigger, termasuk contoh tabel, isi tabel, dan implementasi trigger untuk melacak perubahan data:

1. Membuat Tabel Sumber
Tabel ini adalah tabel utama tempat data disimpan, dan perubahan pada tabel ini akan dilacak.

CREATE TABLE EMPLOYEES (
    EMP_ID NUMBER PRIMARY KEY,
    EMP_NAME VARCHAR2(50),
    DEPARTMENT_ID NUMBER,
    SALARY NUMBER
);
 

.::: How to enable CDC (Change Data Capture) in SQL Server Using Trigger with client_ip :::.

 

Berikut adalah implementasi Change Data Capture (CDC) menggunakan trigger di SQL Server, termasuk contoh tabel, data awal, serta operasi INSERT, UPDATE, dan DELETE.

correlation  with http://teguhth.blogspot.com/2022/11/how-to-enable-cdc-change-data-capture.html
https://teguhth.blogspot.com/2024/12/how-to-enable-cdc-change-data-capture.html
 
1. Membuat Tabel Utama
Misalkan tabel utama bernama Products:

CREATE TABLE Products (
    ProductID INT IDENTITY PRIMARY KEY,
    ProductName NVARCHAR(100),
    Price DECIMAL(10, 2),
    Stock INT
);

.::: Query Check user session, current, Client IP in MariaDB MySQL, PostgreSQL EDB, MSSQL SQL Server, Oracle :::.

 

1. MariaDB MySQL

select USER();
 

2. PostgreSQL

SELECT session_user, CURRENT_USER || '@' || host(inet_client_addr()) AS user_with_ip;  
SELECT CURRENT_USER || '@' || host(inet_client_addr()) AS user_with_ip;
SELECT COALESCE(host(inet_client_addr()), 'localhost') AS ip_only;
SELECT session_user,CURRENT_USER || '@' || inet_client_addr() AS user_with_ip;

.::: How to enable CDC (Change Data Capture) in PostgreSQL, EDB with user,ip client :::.

 

correlation https://teguhth.blogspot.com/2024/04/how-to-enable-cdc-change-data-capture.html

1. Enable WAL (Write-Ahead Logging): PostgreSQL uses Write-Ahead Logging for durability. Ensure that your PostgreSQL server is configured to use WAL.

2. Create Audit Table: Create an audit table where you will log the changes.

CREATE TABLE audit_table (
    id SERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    action_type TEXT NOT NULL,
    old_data JSONB,
    new_data JSONB,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    performed_by VARCHAR(100),
    ip_client TEXT NOT NULL
);

.::: How to enable CDC (Change Data Capture) in MariaDB, MySQL, MarinaDB in Table with user() information :::.

 

correlation https://teguhth.blogspot.com/2024/03/how-to-enable-cdc-change-data-capture.html


Enable Binary Logging
1. check table for sample

SELECT *,@@hostname,@@version FROM teguhth.pembelian
 
2. Create table for cdc

# create table

CREATE TABLE audit_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(100),
    action_type ENUM('INSERT', 'UPDATE', 'DELETE'),
    old_data JSON,
    new_data JSON,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    performed_by VARCHAR(100)
);

ALTER TABLE audit_table
ADD COLUMN performed_by VARCHAR(100);

SELECT *,@@hostname,@@version FROM audit_table;

.::: Insert data table to another table with different Database in SQL Server :::.

 

  A. Without Date

1. Check

select * from teguhth..pembelian
 

2. create table & insert firstime

-- pertama kali insert table
select * into dwh..pembelian_clone from teguhth..pembelian
 

.::: Shrink Disk VMWare for Linux & Windows Virtual Machine :::.

 A. Shrink Disk Linux Virtual Machine

1. check before shrink
 

2. Shrink via Terminal

dd if=/dev/zero of=zerofile bs=1M
 

.::: Setup Polybase in SQL Server for External Table MSSQL :::.

 

Fungsi PolyBase di SQL Server untuk Sumber Eksternal
PolyBase di SQL Server memungkinkan Anda untuk mengakses data dari berbagai sumber eksternal, termasuk SQL Server lain dan MSSQL. Dengan PolyBase, Anda dapat menjalankan query T-SQL di SQL Server yang menggabungkan data dari sumber eksternal tersebut tanpa memindahkan data.

1. setup ODBC MSSQL

2. Buat Credential untuk Login

CREATE DATABASE SCOPED CREDENTIAL RemoteCred
WITH IDENTITY = 'admin', SECRET = 'admin';