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

2. check restore_error.log

[root@teguhth10x mirortest]# cat restore_error.log
--------------
CREATE TABLE `barang_maria_federated_srcdef` (
  `KODE_BARANG` char(6) NOT NULL,
  `NAMA_BARANG` varchar(25) DEFAULT NULL,
  `SATUAN_BARANG` varchar(20) DEFAULT NULL,
  `STOK_BARANG` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`KODE_BARANG`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='dblink_maria/barang'
--------------

ERROR 1434 (HY000) at line 887445: Can't create federated table. Foreign data src error:  database: 'teguhth'  username: 'admin'  hostname: '10.10.10.8'
--------------
CREATE TABLE `table_edb_barang` (
  `KODE_BARANG` char(6) NOT NULL,
  `NAMA_BARANG` varchar(25) DEFAULT NULL,
  `SATUAN_BARANG` varchar(20) DEFAULT NULL,
  `STOK_BARANG` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`KODE_BARANG`)
) ENGINE=CONNECT DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='DSN=edbdb' `TABLE_TYPE`=ODBC `TABNAME`='barang'
--------------

ERROR 1911 (HY000) at line 887879: Unknown option 'TABLE_TYPE'

[root@teguhth10x mirortest]#
 

3. check basic 

sh cek_compare_backup_error_basic.sh

[root@teguhth10x mirortest]# sh cek_compare_backup_error_basic.sh
ERROR LINE : 887445
DATABASE   : dblink
TABLE      : barang_maria_federated_srcdef
ERROR      : Can't create federated table. Foreign data src error:  database: 'teguhth'  username: 'admin'  hostname: '10.10.10.8'

ERROR LINE : 887879
DATABASE   : edb
TABLE      : table_edb_barang
ERROR      : Unknown option 'TABLE_TYPE'

[root@teguhth10x mirortest]#
 

4. check detail 

sh cek_compare_backup_error_detail.sh 

[root@teguhth10x mirortest]# sh cek_compare_backup_error_detail.sh
============================================================
ERROR LINE : 887445
DATABASE   : dblink
TABLE      : barang_maria_federated_srcdef
ERROR      : Can't create federated table. Foreign data src error:  database: 'teguhth'  username: 'admin'  hostname: '10.10.10.8'

CREATE TABLE `barang_maria_federated_srcdef` (
  `KODE_BARANG` char(6) NOT NULL,
  `NAMA_BARANG` varchar(25) DEFAULT NULL,
  `SATUAN_BARANG` varchar(20) DEFAULT NULL,
  `STOK_BARANG` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`KODE_BARANG`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='dblink_maria/barang'
--------------

ERROR 1434 (HY000) at line 887445: Can't create federated table. Foreign data src error:  database: 'teguhth'  username: 'admin'  hostname: '10.10.10.8'

============================================================
ERROR LINE : 887879
DATABASE   : edb
TABLE      : table_edb_barang
ERROR      : Unknown option 'TABLE_TYPE'

CREATE TABLE `table_edb_barang` (
  `KODE_BARANG` char(6) NOT NULL,
  `NAMA_BARANG` varchar(25) DEFAULT NULL,
  `SATUAN_BARANG` varchar(20) DEFAULT NULL,
  `STOK_BARANG` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`KODE_BARANG`)
) ENGINE=CONNECT DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='DSN=edbdb' `TABLE_TYPE`=ODBC `TABNAME`='barang'
--------------

ERROR 1911 (HY000) at line 887879: Unknown option 'TABLE_TYPE'

[root@teguhth10x mirortest]#
 

5. cek detail from .gz 

sh cek_compare_backup_error_detail_compres.sh

[root@teguhth10x mirortest]# sh cek_compare_backup_error_detail_compres.sh
============================================================
ERROR LINE : 887445
DATABASE   : dblink
TABLE      : barang_maria_federated_srcdef
ERROR      : Can't create federated table. Foreign data src error:  database: 'teguhth'  username: 'admin'  hostname: '10.10.10.8'

CREATE TABLE `barang_maria_federated_srcdef` (
  `KODE_BARANG` char(6) NOT NULL,
  `NAMA_BARANG` varchar(25) DEFAULT NULL,
  `SATUAN_BARANG` varchar(20) DEFAULT NULL,
  `STOK_BARANG` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`KODE_BARANG`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='dblink_maria/barang'
--------------

ERROR 1434 (HY000) at line 887445: Can't create federated table. Foreign data src error:  database: 'teguhth'  username: 'admin'  hostname: '10.10.10.8'

============================================================
ERROR LINE : 887879
DATABASE   : edb
TABLE      : table_edb_barang
ERROR      : Unknown option 'TABLE_TYPE'

CREATE TABLE `table_edb_barang` (
  `KODE_BARANG` char(6) NOT NULL,
  `NAMA_BARANG` varchar(25) DEFAULT NULL,
  `SATUAN_BARANG` varchar(20) DEFAULT NULL,
  `STOK_BARANG` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`KODE_BARANG`)
) ENGINE=CONNECT DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='DSN=edbdb' `TABLE_TYPE`=ODBC `TABNAME`='barang'
--------------

ERROR 1911 (HY000) at line 887879: Unknown option 'TABLE_TYPE'

[root@teguhth10x mirortest]#
 

6. cek detail from .gz n encrypted 

[root@teguhth10x mirortest]# sh cek_compare_backup_error_detail_compres_enc.sh
*** WARNING : deprecated key derivation used.
Using -iter or -pbkdf2 would be better.
============================================================
ERROR LINE : 887445
DATABASE   : dblink
TABLE      : barang_maria_federated_srcdef
ERROR      : Can't create federated table. Foreign data src error:  database: 'teguhth'  username: 'admin'  hostname: '10.10.10.8'

CREATE TABLE `barang_maria_federated_srcdef` (
  `KODE_BARANG` char(6) NOT NULL,
  `NAMA_BARANG` varchar(25) DEFAULT NULL,
  `SATUAN_BARANG` varchar(20) DEFAULT NULL,
  `STOK_BARANG` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`KODE_BARANG`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='dblink_maria/barang'
--------------

ERROR 1434 (HY000) at line 887445: Can't create federated table. Foreign data src error:  database: 'teguhth'  username: 'admin'  hostname: '10.10.10.8'

============================================================
ERROR LINE : 887879
DATABASE   : edb
TABLE      : table_edb_barang
ERROR      : Unknown option 'TABLE_TYPE'

CREATE TABLE `table_edb_barang` (
  `KODE_BARANG` char(6) NOT NULL,
  `NAMA_BARANG` varchar(25) DEFAULT NULL,
  `SATUAN_BARANG` varchar(20) DEFAULT NULL,
  `STOK_BARANG` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`KODE_BARANG`)
) ENGINE=CONNECT DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='DSN=edbdb' `TABLE_TYPE`=ODBC `TABNAME`='barang'
--------------

ERROR 1911 (HY000) at line 887879: Unknown option 'TABLE_TYPE'

[root@teguhth10x mirortest]#
 

B. script error log mariadb using simple or resume

1. run script 

sh read_log_maria_resume.sh restore_error_sample.log

[root@teguhth10x onlylog]# pwd
/data/mirror/onlylog
[root@teguhth10x onlylog]# sh read_log_maria_resume.sh restore_error_sample.log
# Error 1

ERROR LINE : 1434 (HY000) at line 887445: Can't create federated table. Foreign data src error:  database: 'teguhth'  username: 'admin'  hostname: '10.10.10.8'
ERROR      : '10.10.10.8'
--------------
CREATE TABLE `barang_maria_federated_srcdef` (
  `KODE_BARANG` char(6) NOT NULL,
******cut******

# Error 2

ERROR LINE : 1911 (HY000) at line 887879: Unknown option 'TABLE_TYPE'
ERROR      : Unknown option 'TABLE_TYPE'
--------------
CREATE TABLE `table_edb_barang` (
  `KODE_BARANG` char(6) NOT NULL,
******cut******
[root@teguhth10x onlylog]#
 

2. sample script read_log_maria_resume.sh 

[root@teguhth10x onlylog]# pwd
/data/mirror/onlylog
[root@teguhth10x onlylog]# cat read_log_maria_resume.sh
#!/bin/bash

LOG="$1"

if [ -z "$LOG" ]; then
    echo "Usage: sh $0 restore_error.log"
    exit 1
fi

awk '
/^--------------$/ {
    if (in_query) {
        in_query=0
    } else {
        in_query=1
        query=""
    }
    next
}

# Simpan semua isi query
in_query {
    query=query $0 "\n"
    next
}

# ERROR
/^ERROR [0-9]+ \(HY000\) at line [0-9]+:/ {

    error_no++

    # Ambil error code
    match($0, /^ERROR [0-9]+/)
    error_code=substr($0, 7)

    # Ambil pesan error
    msg=$0
    sub(/^.*: /, "", msg)

    if (error_no > 1)
        print ""

    print "# Error " error_no
    print ""
    print "ERROR LINE : " error_code
    print "ERROR      : " msg
    print "--------------"

    # Tampilkan hanya 2 baris pertama query
    n=split(query, lines, "\n")

    if (n >= 1)
        print lines[1]

    if (n >= 2)
        print lines[2]
   print "******cut******"
    query=""
}
' "$LOG"
[root@teguhth10x onlylog]#

C. Script checkiing

1. script cek_compare_backup_error_basic.sh

sh cek_compare_backup_error_basic.sh

[root@teguhth10x mirortest]# pwd
/data/mirror/mirortest
[root@teguhth10x mirortest]# cat cek_compare_backup_error_basic.sh
#!/bin/bash

dump="teguhth_ori.sql"
err="restore_error.log"

grep -oE 'line [0-9]+' "$err" | awk '{print $2}' | while read line
do

    # DATABASE dari dump
    db=$(awk -v target="$line" '
        NR > target { exit }
        /^USE `/ {
            db=$0
            sub(/^USE `/, "", db)
            sub(/`;$/, "", db)
        }
        END {
            print db
        }
    ' "$dump")

    # TABLE dari restore_error.log
    table=$(awk -v target="$line" '
        /^CREATE TABLE `/ {
            table=$0
            sub(/^CREATE TABLE `/, "", table)
            sub(/`.*/, "", table)
        }

        /at line / {
            if ($0 ~ "at line " target ":") {
                print table
                exit
            }
        }
    ' "$err")

    # ERROR
    error=$(grep "at line $line:" "$err" |
            sed "s/.*at line $line: //")

    echo "ERROR LINE : $line"
    echo "DATABASE   : ${db:-UNKNOWN}"
    echo "TABLE      : ${table:-UNKNOWN}"
    echo "ERROR      : ${error:-UNKNOWN}"
    echo

done
[root@teguhth10x mirortest]#

2. script cek_compare_backup_error_detail.sh

sh cek_compare_backup_error_detail.sh

[root@teguhth10x mirortest]# pwd
/data/mirror/mirortest
[root@teguhth10x mirortest]# cat cek_compare_backup_error_detail.sh
#!/bin/bash

#dump="oridbsf.sql"
dump="teguhth_ori.sql"
err="restore_error.log"

grep -oE 'line [0-9]+' "$err" | awk '{print $2}' | while read line
do

    # DATABASE
    db=$(awk -v target="$line" '
        NR > target { exit }

        /^USE `/ {
            db=$0
            sub(/^USE `/, "", db)
            sub(/`;$/, "", db)
        }

        END {
            print db
        }
    ' "$dump")


    # TABLE
    table=$(awk -v target="$line" '
        /^CREATE TABLE `/ {
            t=$0
            sub(/^CREATE TABLE `/, "", t)
            sub(/`.*/, "", t)
        }

        $0 ~ "at line " target ":" {
            print t
            exit
        }
    ' "$err")


    # ERROR
    error=$(grep "at line $line:" "$err" |
            sed "s/.*at line $line: //")


    # CREATE TABLE
    create_table=$(awk -v target="$line" '
        /^CREATE TABLE `/ {
            capture=1
            block=$0
            next
        }

        capture {
            block=block "\n" $0
        }

        $0 ~ "ERROR .* at line " target ":" {
            print block
            exit
        }
    ' "$err")


    echo "============================================================"
    echo "ERROR LINE : $line"
    echo "DATABASE   : ${db:-UNKNOWN}"
    echo "TABLE      : ${table:-UNKNOWN}"
    echo "ERROR      : ${error:-UNKNOWN}"
    echo
    echo "$create_table"
    echo

done
[root@teguhth10x mirortest]#

3. script cek_compare_backup_error_detail_compres.sh

sh cek_compare_backup_error_detail_compres.sh

[root@teguhth10x mirortest]# pwd
/data/mirror/mirortest
[root@teguhth10x mirortest]# cat cek_compare_backup_error_detail_compres.sh
#!/bin/bash

#dump="oridbsf.sql.gz"
dump="teguhth_ori.sql.gz"
err="restore_error.log"

grep -oE 'line [0-9]+' "$err" |
awk '{print $2}' |
sort -n |
uniq |
while read line
do

    # ============================================================
    # DATABASE
    # ============================================================
    db=$(zcat "$dump" 2>/dev/null |
        awk -v target="$line" '
            NR > target {
                exit
            }

            /^USE `/ {
                db=$0
                sub(/^USE `/, "", db)
                sub(/`;$/, "", db)
            }

            END {
                print db
            }
        ')


    # ============================================================
    # TABLE
    # ============================================================
    table=$(awk -v target="$line" '
        /^CREATE TABLE `/ {
            t=$0
            sub(/^CREATE TABLE `/, "", t)
            sub(/`.*/, "", t)
        }

        $0 ~ "at line " target ":" {
            print t
            exit
        }
    ' "$err")


    # ============================================================
    # ERROR
    # ============================================================
    error=$(grep "at line $line:" "$err" |
        sed "s/.*at line $line: //")


    # ============================================================
    # CREATE TABLE
    # ============================================================
    create_table=$(awk -v target="$line" '
        /^CREATE TABLE `/ {
            capture=1
            block=$0
            next
        }

        capture {
            block=block "\n" $0
        }

        $0 ~ "ERROR .* at line " target ":" {
            print block
            exit
        }
    ' "$err")


    echo "============================================================"
    echo "ERROR LINE : $line"
    echo "DATABASE   : ${db:-UNKNOWN}"
    echo "TABLE      : ${table:-UNKNOWN}"
    echo "ERROR      : ${error:-UNKNOWN}"
    echo
    echo "$create_table"
    echo

done
[root@teguhth10x mirortest]#

4. script cek_compare_backup_error_detail_compres_enc.sh

sh cek_compare_backup_error_detail_compres_enc.sh

[root@teguhth10x mirortest]# cat cek_compare_backup_error_detail_compres_enc.sh
#!/bin/bash

dumpenc="teguhthenc.sql.enc"
tmpdump="/tmp/teguhthenc_$$.sql"
err="restore_error.log"

# ============================================================
# DECRYPT DUMP KE TEMPORARY FILE
# ============================================================

openssl enc -d -aes-256-cbc -md sha1 \
    -in "$dumpenc" \
    -out "$tmpdump" \
    -pass pass:"$dumpenc"

if [ $? -ne 0 ]; then
    echo "ERROR: decrypt gagal"
    rm -f "$tmpdump"
    exit 1
fi

trap 'rm -f "$tmpdump"' EXIT


# ============================================================
# CEK ERROR LINE
# ============================================================

grep -oE 'line [0-9]+' "$err" |
awk '{print $2}' |
sort -n |
uniq |
while read line
do

    # ========================================================
    # DATABASE
    # ========================================================

    db=$(awk -v target="$line" '
        NR > target {
            exit
        }

        /^USE `/ {
            db=$0
            sub(/^USE `/, "", db)
            sub(/`;$/, "", db)
        }

        END {
            print db
        }
    ' "$tmpdump")


    # ========================================================
    # TABLE
    # ========================================================

    table=$(awk -v target="$line" '
        NR > target {
            exit
        }

        /^CREATE TABLE `/ {
            t=$0
            sub(/^CREATE TABLE `/, "", t)
            sub(/`.*/, "", t)
        }

        END {
            print t
        }
    ' "$tmpdump")


    # ========================================================
    # ERROR
    # ========================================================

    error=$(grep "at line $line:" "$err" |
        sed "s/.*at line $line: //" |
        head -1)


    # ========================================================
    # CREATE TABLE DARI ERROR LOG
    # ========================================================

    create_table=$(awk -v target="$line" '
        /^CREATE TABLE `/ {
            capture=1
            block=$0
            next
        }

        capture {
            block=block "\n" $0
        }

        $0 ~ "ERROR .* at line " target ":" {
            print block
            exit
        }
    ' "$err")


    echo "============================================================"
    echo "ERROR LINE : $line"
    echo "DATABASE   : ${db:-UNKNOWN}"
    echo "TABLE      : ${table:-UNKNOWN}"
    echo "ERROR      : ${error:-UNKNOWN}"
    echo
    echo "$create_table"
    echo

done
[root@teguhth10x mirortest]#























[root@teguhth10x mirortest]# cat restore_error.log
--------------
CREATE TABLE `barang_maria_federated_srcdef` (
  `KODE_BARANG` char(6) NOT NULL,
  `NAMA_BARANG` varchar(25) DEFAULT NULL,
  `SATUAN_BARANG` varchar(20) DEFAULT NULL,
  `STOK_BARANG` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`KODE_BARANG`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='dblink_maria/barang'
--------------

ERROR 1434 (HY000) at line 887445: Can't create federated table. Foreign data src error:  database: 'teguhth'  username: 'admin'  hostname: '10.10.10.8'


biar outputnya.bgmna scrip sheelnya


ERROR LINE : 887445
DATABASE   : dblink
TABLE      : barang_maria_federated_srcdef
ERROR      : Can't create federated table. Foreign data src error:  database: 'teguhth'  username: 'admin'  hostname: '10.10.10.8'

CREATE TABLE `barang_maria_federated_srcdef` (
  `KODE_BARANG` char(6) NOT NULL,
  
  
  
#!/bin/bash

LOG="$1"

if [ -z "$LOG" ]; then
    echo "Usage: $0 restore_error.log"
    exit 1
fi

awk '
/^CREATE TABLE `/ {
    create=$0

    # Ambil nama table
    table=$0
    sub(/^CREATE TABLE `/, "", table)
    sub(/`.*/, "", table)

    next
}

/^ERROR [0-9]+/ {
    error_line=""
    error_msg=$0

    # ERROR LINE
    if (match($0, /at line [0-9]+/)) {
        tmp=substr($0, RSTART, RLENGTH)
        sub(/^at line /, "", tmp)
        error_line=tmp
    }

    # ERROR message
    sub(/^ERROR [0-9]+ \([^)]*\): /, "", error_msg)

    next
}

/Foreign data src error:/ {
    db=""
    user=""
    host=""

    # database
    if (match($0, /database: '\''[^'\'']+'\''/)) {
        tmp=substr($0, RSTART, RLENGTH)
        sub(/^database: '\''/, "", tmp)
        sub(/'\''$/, "", tmp)
        db=tmp
    }

    # username
    if (match($0, /username: '\''[^'\'']+'\''/)) {
        tmp=substr($0, RSTART, RLENGTH)
        sub(/^username: '\''/, "", tmp)
        sub(/'\''$/, "", tmp)
        user=tmp
    }

    # hostname
    if (match($0, /hostname: '\''[^'\'']+'\''/)) {
        tmp=substr($0, RSTART, RLENGTH)
        sub(/^hostname: '\''/, "", tmp)
        sub(/'\''$/, "", tmp)
        host=tmp
    }

    print "ERROR LINE : " error_line
    print "DATABASE   : dblink"
    print "TABLE      : " table
    print "ERROR      : " error_msg
    print ""
    print create
    print ""
    print "FOREIGN DB : " db
    print "USERNAME   : " user
    print "HOSTNAME   : " host
    print ""
    print "----------------------------------------"
    print ""

    create=""
    table=""
}
' "$LOG"

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

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

 

correlation 
Check ETA (Estimated Time of Arrival) restore status MariaDB MarinaDB on Persent (%) 

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

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

 

1. Total database/folder (result 11 folder or db)
 

ls -d /var/lib/mysql/*/ | wc -l

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

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

[root@teguhth10x data]# du -s /var/lib/mysql/marinadb/
516132  /var/lib/mysql/marinadb/
[root@teguhth10x data]#

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