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

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