Monday, November 16, 2026

.::: Trouble shoot the last packet successfully received from the server was 59,875 milliseconds or (conn=32) Socket error in MariaDB in Lucee 6 :::.

 


A. Prepare lab

1. create datasource (dbteguhth)
2. create script sleep_run.cfm
http://10.10.10.60:8888/sleep_run.cfm?
 


3. create script sleep_timer.cnf
http://10.10.10.60:8888/sleep_timer.cfm?
 

4. enable wait timeout 50 seconds 

SHOW GLOBAL VARIABLES WHERE Variable_name IN ('wait_timeout','interactive_timeout','net_read_timeout','net_write_timeout','connect_timeout','max_connections','max_allowed_packet');

SHOW GLOBAL STATUS WHERE Variable_name IN ('Aborted_clients','Aborted_connects','Connection_errors_max_connections','Threads_connected','Threads_running','Max_used_connections');
 

Wednesday, November 11, 2026

.::: Install MariaDB 11.4, 12, 13 & Maxscale 24 In Ubuntu 22.04.5 LTS :::.

 
correlation https://teguhth.blogspot.com/2026/11/install-mariadb-114-12-13-in-ubuntu.html
https://teguhth.blogspot.com/2026/11/install-mariadb-118-12-13-in-ubuntu.html

1. check OS 

grep '^PRETTY_NAME=' /etc/os-release | cut -d= -f2- | tr -d '"'

2. install curl mariadb & maxscale

curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash

curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-11.4" --mariadb-maxscale-version="24"


3. after install, check mariadb.source 

root@teguhth:~# cat /etc/apt/sources.list.d/mariadb.sources

# MariaDB Server
# To use a different major version of the server, or to pin to a specific minor version, change URI below.
X-Repolib-Name: MariaDB Server
Enabled: yes
Types: deb
#URIs: https://dlm.mariadb.com/repo/mariadb-server/11.4/repo/ubuntu
#URIs: https://dlm.mariadb.com/repo/mariadb-server/12.rolling/repo/ubuntu
URIs: https://dlm.mariadb.com/repo/mariadb-server/13.rolling/repo/ubuntu
Suites: jammy
Components: main main/debug
Architectures: amd64 arm64
Signed-By: /etc/apt/keyrings/mariadb-keyring.gpg
 

Tuesday, November 10, 2026

.::: Install MariaDB 11.4, 12, 13 In Ubuntu 24.04.5 LTS :::.

 

 
1. check OS 

grep '^PRETTY_NAME=' /etc/os-release | cut -d= -f2- | tr -d '"'

2. install curl mariadb & maxscale

curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash

curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-11.4" --mariadb-maxscale-version="24"

3. after install, check mariadb.source 

root@teguhth:~# cat /etc/apt/sources.list.d/mariadb.sources

# MariaDB Server
# To use a different major version of the server, or to pin to a specific minor version, change URI below.
X-Repolib-Name: MariaDB Server
Enabled: yes
Types: deb
#URIs: https://dlm.mariadb.com/repo/mariadb-server/11./repo/ubuntu
#URIs: https://dlm.mariadb.com/repo/mariadb-server/12.rolling/repo/ubuntu
URIs: https://dlm.mariadb.com/repo/mariadb-server/13.rolling/repo/ubuntu
Suites: noble
Components: main main/debug
Architectures: amd64 arm64
Signed-By: /etc/apt/keyrings/mariadb-keyring.gpg
 

Friday, November 6, 2026

.::: Install MariaDB 11.8, 12, 13 In Ubuntu 26.04.1 LTS :::.

 
1. install repository

curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-11.4"
 
curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-11.4" --mariadb-maxscale-version="24"

2. check mariadb source 

root@teguhth:/data# cat /etc/apt/sources.list.d/mariadb.sources

# MariaDB Server
# To use a different major version of the server, or to pin to a specific minor version, change URI below.
X-Repolib-Name: MariaDB Server
Enabled: yes
Types: deb
URIs: https://dlm.mariadb.com/repo/mariadb-server/11.8/repo/ubuntu
#URIs: https://dlm.mariadb.com/repo/mariadb-server/13.rolling/repo/ubuntu
#URIs: https://dlm.mariadb.com/repo/mariadb-server/12.rolling/repo/ubuntu
Suites: resolute
Components: main main/debug
Architectures: amd64 arm64
Signed-By: /etc/apt/keyrings/mariadb-keyring.gpg
 

Monday, October 26, 2026

.::: Install MariaDB 11.4, Maxscale 24, Keepalived 2 in Redhat Enterprise Linux 10.2 without regisration or using repo rpm This system is not registered with an entitlement server. You can use "rhc" or "subscription-manager" to register. :::.

 

1. download packet maria

## rpm package maria 

wget https://cdn-ubi.redhat.com/content/public/ubi/dist/ubi10/10/x86_64/appstream/os/Packages/p/perl-libs-5.40.2-515.el10_2.x86_64.rpm
wget https://cdn-ubi.redhat.com/content/public/ubi/dist/ubi10/10/x86_64/appstream/os/Packages/p/perl-version-0.99.32-4.el10.x86_64.rpm
wget https://cdn-ubi.redhat.com/content/public/ubi/dist/ubi10/10/x86_64/appstream/os/Packages/p/perl-DBI-1.643-26.el10_2.7.x86_64.rpm
wget https://cdn-ubi.redhat.com/content/public/ubi/dist/ubi10/10/x86_64/appstream/os/Packages/p/perl-File-Copy-2.41-515.el10_2.noarch.rpm
wget https://cdn-ubi.redhat.com/content/public/ubi/dist/ubi10/10/x86_64/appstream/os/Packages/p/perl-Sys-Hostname-1.25-515.el10_2.x86_64.rpm
wget https://cdn-ubi.redhat.com/content/public/ubi/dist/ubi10/10/x86_64/appstream/os/Packages/p/perl-Module-Load-0.36-511.el10.noarch.rpm
wget https://cdn-ubi.redhat.com/content/public/ubi/dist/ubi10/10/x86_64/appstream/os/Packages/p/perl-Math-BigInt-2.0030.03-3.el10.noarch.rpm
wget https://cdn-ubi.redhat.com/content/public/ubi/dist/ubi10/10/x86_64/appstream/os/Packages/p/perl-Math-Complex-1.62-515.el10_2.noarch.rpm
wget https://repo.almalinux.org/almalinux/10/AppStream/x86_64/os/Packages/m4-1.4.19-11.el10.x86_64.rpm
wget https://repo.almalinux.org/almalinux/10/BaseOS/x86_64/os/Packages/make-4.4.1-9.el10.x86_64.rpm
wget https://cdn-ubi.redhat.com/content/public/ubi/dist/ubi10/10/x86_64/appstream/os/Packages/b/boost-program-options-1.83.0-7.el10.x86_64.rpm
wget https://repo.almalinux.org/almalinux/10/AppStream/x86_64/os/Packages/mysql-selinux-1.0.14-1.el10_0.noarch.rpm
 

Wednesday, October 21, 2026

.::: Install jemalloc Memory Allocation MariaDB 12 in Almalinux 10 & Ubuntu :::.

 
 
1. check mariadb memory allocation

SELECT @@hostname, @@version_comment,@@version;
SELECT @@hostname AS hostname,@@version AS mariadb_version, @@version_malloc_library AS memory_allocator;
 


2. Install epel release 

yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-10.noarch.rpm -y

3. Install jemalloc 
 
# redhat
yum install jemalloc -y

# ubuntu
apt install -y libjemalloc2

Tuesday, October 20, 2026

.::: Enable Dynamic Data Masking (DDM) in MariaDB 12 using view, list view :::.

 
Oh, kalau MariaDB, beda dengan SQL Server. MariaDB tidak memiliki fitur Dynamic Data Masking (MASKED WITH) bawaan seperti SQL Server.

Untuk tabel customer Anda, cara yang paling umum adalah membuat VIEW yang menampilkan data sudah dimasking. Data asli tetap tersimpan di tabel.

1. Data asli

CREATE TABLE customer(
    KODE_CUSTOMER CHAR(6),
    NAMA_CUSTOMER VARCHAR(30),
    ALAMAT_CUSTOMER VARCHAR(30),
    KOTA_CUSTOMER VARCHAR(15),
    TELEPON_CUSTOMER VARCHAR(15),
    PRIMARY KEY(KODE_CUSTOMER)
);

SELECT * FROM customer;


2. Buat VIEW untuk data yang dimasking

CREATE VIEW v_customer_masked AS
SELECT
    KODE_CUSTOMER,
    NAMA_CUSTOMER,
    CONCAT(
        LEFT(ALAMAT_CUSTOMER, 3),
        '********'
    ) AS ALAMAT_CUSTOMER,
    KOTA_CUSTOMER,
    CONCAT(
        '########',
        RIGHT(TELEPON_CUSTOMER, 4)
    ) AS TELEPON_CUSTOMER,
    '********' AS TELEPON_CUSTOMER_ALL
FROM customer;
 

.::: Enable Dynamic Data Masking (DDM) SQL Server MSSQL 2025 include using view :::.

 

1. Tabel awal
 
CREATE TABLE customer(
    KODE_CUSTOMER CHAR(6),
    NAMA_CUSTOMER VARCHAR(30),
    ALAMAT_CUSTOMER VARCHAR(30),
    KOTA_CUSTOMER VARCHAR(15),
    TELEPON_CUSTOMER VARCHAR(15),
    PRIMARY KEY(KODE_CUSTOMER)
);

 


2. Tambahkan Dynamic Data Masking for telepon & alamat 

ALTER TABLE customer
ALTER COLUMN TELEPON_CUSTOMER
ADD MASKED WITH (FUNCTION = 'partial(0,"XXXXXXX",4)');

ALTER TABLE customer
ALTER COLUMN ALAMAT_CUSTOMER
ADD MASKED WITH (FUNCTION = 'partial(3,"XXXX",3)');

ALTER TABLE customer
ALTER COLUMN KOTA_CUSTOMER
ADD MASKED WITH (FUNCTION = 'default()');

  

Friday, October 16, 2026

.::: Check ETA (Estimated Time of Arrival) restore status include All Database / Reinisiasi MariaDB MarinaDB on Persent (%) for GTID Mirroring & Galera MariaDB MarinaDB - ETA All Table, reinisiasi using Utilisation CPU, Memory, eta_all_table_util.sh,eta_reinisiasi_util.sh :::.

 
correlation with https://teguhth.blogspot.com/2026/09/check-eta-estimated-time-of-arrival.html

1. Total database/folder (result 11 folder or db) <eta_reinisiasi>
 
ls -d /var/lib/mysql/*/ | wc -l
 
[root@teguhth10x ~]# ls -d /var/lib/mysql/*/ | wc -l
12
[root@teguhth10x ~]#


2. sample size a database mariadb (sample result 516132 kib or 528,519,168 bytes) <eta_all_table>
 

du -s /var/lib/mysql/marinadb/
du -sh /var/lib/mysql/marinadb/

#or exclude

du -s --exclude='pembelian.ibd' /var/lib/mysql/marinadb
du -sh --exclude='pembelian.ibd' /var/lib/mysql/marinadb
 
[root@teguhth10x data]# du -s /var/lib/mysql/marinadb/
516132  /var/lib/mysql/marinadb/
[root@teguhth10x data]# 
 

Tuesday, October 13, 2026

.::: Create Store Procedure Sync Manual Table, Add Column, Alter Column Compare Database (teguhth,aisyah) in MariaDB MarinaDB MySQL Version 2 :::.

 

correlation https://teguhth.blogspot.com/2026/04/create-store-procedure-sync-manual_10.html <diperbarui disini>
correlation https://teguhth.blogspot.com/2026/04/shell-script-sync-manual-table-add.html shell script to check
correlation https://teguhth.blogspot.com/2025/03/query-to-check-table-column-compare_28.html basic compare 

1. check 


SELECT @@hostname,@@version;

SELECT
    TABLE_SCHEMA,
    TABLE_TYPE,
    COUNT(*) AS total_objek
FROM information_schema.tables
WHERE TABLE_SCHEMA IN ('teguhth', 'aisyahdb')
GROUP BY TABLE_SCHEMA, TABLE_TYPE;

 

2. Hitung jumlah kolom yang perlu dibandingkan

SELECT
    TABLE_SCHEMA,
    COUNT(*) AS total_kolom
FROM information_schema.columns
WHERE TABLE_SCHEMA IN (
    'teguhth',
    'aisyahdb'
)
GROUP BY TABLE_SCHEMA;
 

Monday, October 5, 2026

.::: Store Procedure: Enlarge Table & Database PostgreSQL to 1,000,000 Rows :::.

 


1. table barang 

ALTER TABLE barang
ALTER COLUMN kode_barang TYPE VARCHAR(11);

-- 2. Pastikan KODE_BARANG NOT NULL
ALTER TABLE barang
ALTER COLUMN kode_barang SET NOT NULL;


DROP PROCEDURE IF EXISTS generate_barang(INT);

CREATE OR REPLACE PROCEDURE generate_barang(IN jumlah INT)
LANGUAGE plpgsql
AS $$
DECLARE
    mulai BIGINT;
BEGIN

    SELECT COALESCE(
        MAX(
            NULLIF(
                regexp_replace(kode_barang, '^ELK-', ''),
                ''
            )::BIGINT
        ),
        0
    )
    INTO mulai
    FROM barang
    WHERE kode_barang ~ '^ELK-[0-9]+$';

    RAISE NOTICE 'Nomor ELK terakhir = %', mulai;
    RAISE NOTICE 'Generate % data mulai dari ELK-%',
                 jumlah,
                 mulai + 1;
 

Friday, October 2, 2026

.::: Backup MariaDB mysqldump mariadb-dump using --ignore-table-data, --ignore-table Some Table :::.

  

1. sample create table dummy sample typ*


CREATE TABLE typa (text VARCHAR(100));
CREATE TABLE typb (text VARCHAR(100));
CREATE TABLE typc (text VARCHAR(100));
CREATE TABLE typd (text VARCHAR(100));
CREATE TABLE type (text VARCHAR(100));

INSERT INTO typa (text) VALUES ('typa sample typa');
INSERT INTO typb (text) VALUES ('typb sample typb');
INSERT INTO typc (text) VALUES ('typc sample typc');
INSERT INTO typd (text) VALUES ('typd sample typd');
INSERT INTO type (text) VALUES ('type sample type');

 

Monday, September 28, 2026

Wednesday, September 23, 2026

.::: Shell Script to Compare MariaDB Restore_log, backup_log with dump .sql include resume log :::.

  

A. Activity test compare 

1. prepare file 

[root@teguhth10x mirortest]# pwd
/data/mirror/mirortest
[root@teguhth10x mirortest]# ls -lh
total 296M
-rw-r--r-- 1 root root 1023 Sep 22 14:53 cek_compare_backup_error_basic.sh
-rw-r--r-- 1 root root 1.4K Sep 22 14:50 cek_compare_backup_error_detail.sh
-rw-r--r-- 1 root root 2.0K Sep 22 14:50 cek_compare_backup_error_detail_compres.sh
-rw-r--r-- 1 root root 2.5K Sep 22 14:50 cek_compare_backup_error_detail_compres_enc.sh
-rw-r--r-- 1 root root  973 Sep 22 15:58 restore_error.log
-rw-r--r-- 1 root root 139M Sep 22 14:53 teguhth_ori.sql
-rw-r--r-- 1 root root  19M Sep 22 14:51 teguhth_ori.sql.gz
-rw-r--r-- 1 root root 139M Sep 22 14:52 teguhthenc.sql.enc
[root@teguhth10x mirortest]#
 

Tuesday, September 22, 2026

.::: Script Check ETA (Estimated Time of Arrival) Backup Database MariaDB on % Percent in .sql or .sql.gz :::.

  

1. check size database (kb)

du -s /var/lib/mysql/teguhth

du -s --exclude='pembelian.ibd' /var/lib/mysql/teguhth

[root@teguhth10x testbackup]# du -sh /var/lib/mysql/teguhth
505M    /var/lib/mysql/teguhth
[root@teguhth10x testbackup]# du -s /var/lib/mysql/teguhth
516132  /var/lib/mysql/teguhth

2. backup in sql or comprese 

mariadb-dump -uroot -pxxx teguhth > teguhth_all.sql
mariadb-dump -uroot -pxxx teguhth | gzip -c > teguhth_all.sql.gz

mariadb-dump -uroot -pxxx marinadb > marinadb_all.sql
mariadb-dump -uroot -pxxx marinadb | gzip -c > marinadb_all.sql.gz

 

Monday, September 14, 2026

.::: Check ETA (Estimated Time of Arrival) restore status include All Database / Reinisiasi MariaDB MarinaDB on Persent (%) for GTID Mirroring & Galera MariaDB MarinaDB - ETA All Final, ETA All Table, Eta Table :::.

 
1. Total database/folder (result 11 folder or db) <eta_reinisiasi>
 
ls -d /var/lib/mysql/*/ | wc -l
 
[root@teguhth10x ~]# ls -d /var/lib/mysql/*/ | wc -l
12
[root@teguhth10x ~]#


2. sample size a database mariadb (sample result 516132 kib or 528,519,168 bytes) <eta_all_table>
 
du -s /var/lib/mysql/marinadb/
du -sh /var/lib/mysql/marinadb/
#or 
du -s --exclude='pembelian.ibd' /var/lib/mysql/marinadb
du -sh --exclude='pembelian.ibd' /var/lib/mysql/marinadb
 
[root@teguhth10x data]# du -s /var/lib/mysql/marinadb/
516132  /var/lib/mysql/marinadb/
[root@teguhth10x data]# 
 
[root@teguhth10x ~]# du -s --exclude='pembelian.ibd' /var/lib/mysql/marinadb
389152  /var/lib/mysql/marinadb
[root@teguhth10x ~]#
[root@teguhth10x ~]# du -sh --exclude='pembelian.ibd' /var/lib/mysql/marinadb
381M    /var/lib/mysql/marinadb
[root@teguhth10x ~]#
 
2 A. jumlah table via file <eta_all_table>
 
find /var/lib/mysql/teguhth -maxdepth 1 -type f -name "*.ibd" | wc -l
 
[root@teguhth10x ~]# find /var/lib/mysql/teguhth -maxdepth 1 -type f -name "*.ibd" | wc -l
5
[root@teguhth10x ~]# find /var/lib/mysql/marinadb -maxdepth 1 -type f -name "*.ibd" | wc -l
5
[root@teguhth10x ~]# 


Friday, September 11, 2026

.::: Store Procedure: Enlarge Table & Database MariaDB to 1,000,000 Rows :::.

 
 
correlation with https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query-mysql.html

1. table barang 

ALTER TABLE barang MODIFY KODE_BARANG CHAR(10) NOT NULL;

DELIMITER //

CREATE PROCEDURE generate_barang(IN jumlah INT)
BEGIN
    DECLARE i INT;
    DECLARE mulai INT;

    SELECT COALESCE(
        MAX(CAST(SUBSTRING(KODE_BARANG, 5) AS UNSIGNED)),
        0
    )
    INTO mulai
    FROM barang
    WHERE KODE_BARANG LIKE 'ELK-%';
 

Tuesday, September 8, 2026

.::: Shell Script to Grouping Year, Month, Day, Time, Date for Archive, Cleansing in MarinaDB MariaDB some database, table :::.


correlation https://teguhth.blogspot.com/2025/11/grouping-year-month-day-time-date-for.html

1. run check all hardcode
 




2. run check manual 
 


3. script check hardcode


[root@teguhth-all countx]# cat table_count.sh
hostx=$(hostname -s)
ipx='10.10.10.90'
portx='3306'
userdb='admin'
pwddb='xxx'
dbname='teguhth'
tblname='datefilter'
cldatex='datex'
 

Monday, September 7, 2026

.::: Script Scheduling crontab SCP Linux to other server like Backup Daily (run from source server ) :::.

 



1. enable ssh-key 

hostname -I | awk '{print $1}'
ssh-keygen -t rsa
ssh-copy-id -p 22 root@10.10.10.9

 


2. manual command

scp /data/sample/* root@10.10.10.9:/dummy/
scp -P22 /data/sample/*  root@10.10.10.9:/dummy/

 

.::: Bat Script sftp windows to Server Linux with Scheduler & password using hiddep password or private key .ppk (run from destination server) using putty psftp.exe :::.

 

correlation https://teguhth.blogspot.com/2026/09/bat-script-sftp-windows-to-server-linux.html

1. create sshkey without password

C:\Windows\System32>type %USERPROFILE%\.ssh\id_rsa.pub | ssh ftpaccess@10.10.10.90 "mkdir -p ~/.ssh && chmod 700 ~/.ssh && cat >> ~/.ssh/authorized_keys && chmod 600 ~/.ssh/authorized_keys"

C:\Windows\System32>
C:\Windows\System32>sftp ftpaccess@10.10.10.90
Connected to 10.10.10.90.
sftp> exit
 

Friday, September 4, 2026

.::: Bat Script sftp windows to Server Linux with Scheduler & password :::.

  

 
1. Just sftp
 

 @echo off
setlocal

set SFTP_SERVER=10.10.10.90
set SFTP_USER=ftpaccess
set SFTP_PASSWORD=xxx

set REMOTE_DIR=/media/backup
set LOCAL_DIR=D:\temp

set SFTP_SCRIPT=D:\other\windows\sftpbat\sftp_download.txt

echo ========================================
echo SFTP DOWNLOAD ALL FILES
echo ========================================
echo Remote : %REMOTE_DIR%
echo Server : %SFTP_SERVER%
echo Local  : %LOCAL_DIR%
echo ========================================

if not exist "%LOCAL_DIR%" (
    mkdir "%LOCAL_DIR%"
)
 

Tuesday, September 1, 2026

.::: Simulation Blocking,lock, Locking in PostgreSQL EDB with Advanced process list Pg-Activity ,pg Lock like 'Waiting for table metadata lock' MariaDB MySQL :::.

 

correlation metadata mariadb https://teguhth.blogspot.com/2026/07/how-to-simulation-locking-with-waiting.html
correlation https://teguhth.blogspot.com/2023/10/how-to-sample-simulation-blocking-in.html

1. terminal 1

BEGIN;

SELECT
    pg_backend_pid() AS session_id,
    current_user AS user;

UPDATE pembelian
SET jumlah_pembelian = 10
WHERE kode_pembelian = 'BEL-E001';

-- JANGAN COMMIT
-- biarkan transaksi terbuka

BEGIN;
SELECT pg_backend_pid() AS session_id,current_user AS user;
UPDATE pembelian SET jumlah_pembelian = 10 WHERE kode_pembelian = 'BEL-E001';

 

Monday, August 31, 2026

.::: PostgreSQL 18 Access via Pgpool-II – SCRAM Authentication with AES-Encrypted fix psql: error: connection to server at "10.10.10.0", port 9999 failed: ERROR: failed to authenticate with backend using SCRAM :::.

  

1. login as root 

echo 'rahasia' > /root/.pgpoolkey
chmod 600 /root/.pgpoolkey
 
cp /root/.pgpoolkey /var/lib/pgsql/.pgpoolkey
chown postgres:postgres /var/lib/pgsql/.pgpoolkey
chmod 600 /var/lib/pgsql/.pgpoolkey 

 

2. create sample user n password

create user adminaes ;
ALTER ROLE adminaes SUPERUSER CREATEDB CREATEROLE REPLICATION;
ALTER ROLE adminaes PASSWORD 'adminaes';
ALTER USER adminaes WITH PASSWORD 'adminaes';
 

Friday, August 28, 2026

.::: Check ETA (Estimated Time of Arrival) restore status All Database / Reinisiasi MariaDB MarinaDB on Persent (%) for GTID Mirroring & Galera :::.

 
 
correlation with https://teguhth.blogspot.com/2026/07/check-eta-estimated-time-of-arrival.html

1. check dbsize all maria (only folder maria)

du -sch -- "/var/lib/mysql"/*/ | tail -1 | awk '{print $1}'
du -sc -- "/var/lib/mysql"/*/ | tail -1 | awk '{print $1}'

[root@teguhth-all ~]# du -sch -- "/var/lib/mysql"/*/ | tail -1 | awk '{print $1}'
3.7G
[root@teguhth-all ~]# du -sc -- "/var/lib/mysql"/*/ | tail -1 | awk '{print $1}'
3878576
[root@teguhth-all ~]#

 


2. recommendation drop all database no system

https://teguhth.blogspot.com/2025/10/script-drop-all-database-non-system-in.html 
 

Thursday, August 27, 2026

.::: Testing Routing Balancing PostgreSQL EDB using PGPool-II Write & Read :::.

 
A. queri normal select normal

SELECT
    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'teguhth01'
        WHEN '10.10.10.32'::inet THEN 'teguhth02'
        WHEN '10.10.10.33'::inet THEN 'teguhth03'
        ELSE 'UNKNOWN'
    END AS hostname,
    inet_server_addr() AS ip,
    inet_server_port() AS port,
    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'Master'
        WHEN '10.10.10.32'::inet THEN 'Slave'
        WHEN '10.10.10.33'::inet THEN 'Slave'
        ELSE 'UNKNOWN'
    END AS role,
    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'Write'
        WHEN '10.10.10.32'::inet THEN 'Read'
        WHEN '10.10.10.33'::inet THEN 'Read'
        ELSE 'UNKNOWN'
    END AS access_mode,
    inet_client_addr() AS ipclient,
    inet_client_port() AS ipclientport,
    current_database() AS database,
    current_schema() AS dbschema,
    current_user AS username,
    CURRENT_TIMESTAMP AS current_time;
 

Friday, August 21, 2026

.::: Change http to https to Activate Clipboard Apache Guacamole in Almalinux 10 / Redhat 10 for remote ssh/Rdp via Web Browser :::.

  

continue for https://teguhth.blogspot.com/2026/08/install-apache-guacamole-in-almalinux.html

1. install nginx & Buat sertifikat SSL

dnf install nginx openssl -y

mkdir -p /etc/nginx/ssl

openssl req -x509 -nodes -days 3650 -newkey rsa:2048 \
-keyout /etc/nginx/ssl/guacamole.key \
-out /etc/nginx/ssl/guacamole.crt \
-subj "/C=ID/ST=Jawa Barat/L=Bogor/O=IT/OU=IT/CN=10.10.10.22"

ls -l /etc/nginx/ssl/
 

.::: Install Apache Guacamole in Almalinux 10 / Redhat 10 for remote ssh/Rdp via Web Browser :::.

 

1. Pastikan AlmaLinux 10

Saya sarankan minimal AlmaLinux 10.2 karena 10.2 sudah dirilis 26 Mei 2026.

cat /etc/redhat-release 

grep '^PRETTY_NAME=' /etc/os-release | cut -d= -f2- | tr -d '"'

2. Aktifkan EPEL

dnf install -y epel-release
dnf makecache
 

Wednesday, August 19, 2026

.::: Test Pgpool-II Monitor PostgreSQL Routing to Master or Slave replication streaming Using Lucee & PHP PGPASSWORD :::.

 

1. PostgreSQL Connection

chmod 755 /opt/lucee/bin/pgpool_connection.sh
chmod 755 /opt/lucee/bin/pgpool_nodes.sh
chmod 755 /opt/lucee/bin/pgpool_replication.sh


[root@teguhth-all ROOT]# cat /opt/lucee/bin/pgpool_connection.sh
#!/bin/bash

export PGPASSWORD='admin'

/usr/bin/psql -h 10.10.10.25 -p 9999 -U userpgpool -d teguhth -c "SELECT inet_client_addr(), inet_client_port(), inet_server_addr(), inet_server_port(),'PostgreSQL ' || current_setting('server_version') as edbversion;"
[root@teguhth-all ROOT]#
 

Tuesday, August 18, 2026

.::: Install & Configure PgBouncer in Centos 9 Almalinux 9 for PostgreSQL 18 :::.

 

1. Install PgBouncer

dnf install -y pgbouncer

rpm -qa | grep pgbouncer

[root@teguhth-all ~]# rpm -qa | grep pgbouncer
pgbouncer-1.25.2-43PGDG.rhel9.8.x86_64
[root@teguhth-all ~]# pgbouncer --version
PgBouncer 1.25.2
libevent 2.1.12-stable
adns: c-ares 1.19.1
tls: OpenSSL 3.5.5 27 Jan 2026
systemd: yes
[root@teguhth-all ~]#


2. Backup konfigurasi

cp /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/back_pgbouncer.ini
 

Friday, August 14, 2026

.::: Install Pgpool-II for load balancer PostgreSQL EDB like Maxscale for MariaDB :::.

 

1. lab

ip master 10.10.10.31
port 5432
user pool access = userpgpool 
passwd pool access = admin
status Master

ip slave 10.10.10.33
port 5432
user pool access = userpgpool 
passwd pool access = admin
status Slave 1

ip slave 10.10.10.33
port 5432
user pool access = userpgpool 
passwd pool access = admin
status Slave 2

 

Wednesday, August 12, 2026

.::: Store Procedure backup database & database dmart or other in MSSQL SQL Server 2025 :::.

  

1. check store procedure

SELECT 
    db_name() as dbname,name AS ProcedureName,
    SCHEMA_NAME(schema_id) AS SchemaName,
    create_date,
    modify_date
FROM 
    sys.procedures
ORDER BY 
    name;
    
-- or 

SELECT 
    db_name() as dbname,name AS ProcedureName,
    SCHEMA_NAME(schema_id) AS SchemaName,
    create_date,
    modify_date
FROM 
    sys.procedures where name='sp_backupdev' 
ORDER BY 
    name;
 

Tuesday, August 11, 2026

.::: Convert pgaudit & pgauditlogtofile log insert into table pgauditfile in dbatools PostgreSQL 18 EDB :::.

 
correlation https://teguhth.blogspot.com/2024/02/enable-pgaudit-pgauditlogtofile-in.html

https://teguhth.blogspot.com/2024/04/convert-pgaudit-pgauditlogtofile-log.html

1. config postgresql.conf & restart systemctl restart postgresql-18 


listen_addresses='*'
shared_preload_libraries = 'pgaudit,pgauditlogtofile'

pgaudit.log_exclude = 'pg_catalog,information_schema'
pgaudit.log_directory = '/var/lib/pgsql/18/data/log'
pgaudit.log_filename = 'pgaudit-teguhth.log'

pgaudit.log = 'write,ddl,role'
#pgaudit.log = 'all'
pgaudit.log_catalog = off
pgaudit.log_relation = on
pgaudit.log_statement = on
pgaudit.log_parameter = off
pgaudit.log_rows = off
pgaudit.log_execution_time = off
pgaudit.log_execution_memory = off
# pgaudit.log_format = csv
 

Popular Posts