.::: 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-%';
 

.::: 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'
 

.::: 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
 

.::: 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%"
)
 

.::: 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';
 

.::: 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';
 

.::: 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 
 

.::: 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;
 

.::: 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
 

.::: 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]#
 

.::: 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
 

.::: 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
 

.::: 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;
 

.::: 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
 

.::: Testing & Check Primary key & autoincremental status in MariaDB :::.

 

https://teguhth.blogspot.com/2024/10/sample-database-erp-enterprise-resource.html

1. sample table customers 

show create table customers\G
 

2. check table customers 

select * from customers;
 

.::: Check xml_deadlock_report offline & online :::.

 

correlation deadlock 

https://teguhth.blogspot.com/2026/03/simalation-locking-blocking-deadlock-in.html
https://teguhth.blogspot.com/2021/09/sql-server-how-to-simulate-and-catch.html

1. check targent data  event_file

SELECT create_time,target_name,name,session_source,
    CAST(xt.target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets xt
JOIN sys.dm_xe_sessions xs
ON xs.address = xt.event_session_address
WHERE xs.name='system_health'
AND xt.target_name='event_file';

output 

<EventFileTarget truncated="0">
  <Buffers logged="4" dropped="0" />
  <File name="C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\system_health_0_134303098936130000.xel" />
</EventFileTarget>
 

 

.::: Test MariaDB MaxScale Routing Hint Routing to Master or Slave Using Lucee & PHP :::.

 

 
correlation https://teguhth.blogspot.com/2025/01/test-insert-select-data-using-maxscale.html

1. info server testing 
 

2. diagram
 

 

.::: Upgrading from MariaDB 11.8 to MariaDB 12.3 :::.

 

https://teguhth.blogspot.com/2023/06/upgrading-from-mariadb-104-to-mariadb.html

1. check Repo for prepare maria 12x
[root@teguhth-all /]# cat /etc/yum.repos.d/mariadb.repo

[mariadb-main]
name = MariaDB Server
#baseurl = https://dlm.mariadb.com/repo/mariadb-server/11/yum/rhel/9/x86_64
baseurl = https://dlm.mariadb.com/repo/mariadb-server/12.rolling/yum/rhel/9/x86_64
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Server-GPG-KEY
gpgcheck = 1
enabled = 1
module_hotfixes = 1

[mariadb-maxscale]
# To use the latest stable release of MaxScale, use "latest" as the version
# To use the latest beta (or stable if no current beta) release of MaxScale, use "beta" as the version
name = MariaDB MaxScale
baseurl = https://dlm.mariadb.com/repo/maxscale/latest/yum/rhel/9/x86_64
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-MaxScale-GPG-KEY
gpgcheck = 1
enabled = 1

[root@teguhth-all /]#