.::: Script Drop All Database non-system in MariaDB MySQL in Centos 9 :::.

 

1. run script drop database non-system 

sh drop_database_nosystem_all.sh
 

2. run script drop database non-system with duration

sh drop_database_nosystem_all_duration.sh 

.::: Backup Database MariaDB with encripted -aes-256-cbc dan restore mariadb database :::.

1. backup enkripsi

mysqldump -uroot -pxxx teguhth | openssl enc -aes-256-cbc -md sha1 -salt -out teguhthenc.sql.enc
mysqldump -uroot -pxxx teguhth | openssl enc -pass pass:abcde -aes-256-cbc -md sha1 -salt -out teguhthenc.sql.enc
mysqldump -uroot -pxxx teguhth | openssl enc -pass pass:abcde -aes-256-cbc -md sha1 -salt -out teguhthenc.sql.enc | gzip -c  > teguhthenc.gz.sql.enc
 
 

2. convert to sql from enkripsi file to sql
openssl enc -d -aes-256-cbc -md sha1 -in teguhthenc.sql.enc -out teguhth.sql -pass pass:abcde
 

.::: Script restore database MariaDB using record to table include duration Restore :::.

 

A. simple record

1. create table restore_log

CREATE TABLE IF NOT EXISTS restore_log (
    db_name VARCHAR(100),
    restore_time DATETIME,
    backup_file VARCHAR(255),
    restored_by VARCHAR(100)
);

2. create script 

.::: Script Backup Full & Incremental All Databases Using Record to Table include start, finish & duration backup in PostgreSQL EDB :::.

  

https://teguhth.blogspot.com/2025/02/script-backup-full-all-database-using.html
https://teguhth.blogspot.com/2025/09/script-backup-full-incremental-all.html

1. create database & table

CREATE TABLE backuplogduration (
    host VARCHAR(255) NOT NULL,
    backuptime TIMESTAMP NOT NULL,
    start TIMESTAMP NOT NULL,
    finish TIMESTAMP NOT NULL,
    duration VARCHAR(255) NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
);

.::: Create CDB & Sample Database in Oracle 19C :::.

 

1. Login sebagai SYS atau SYSTEM

Masuk ke CDB lalu kasih privilege ke HRADMIN:

-- Masuk ke CDB sebagai SYS
sqlplus sys@//localhost:1521/TGHDB as sysdba

-- Pindah ke PDB TGHDBHR
ALTER SESSION SET CONTAINER=TGHDBHR;

-- Beri hak penuh

CREATE USER hradmin IDENTIFIED BY hrpass;
GRANT CONNECT, RESOURCE TO hradmin;
ALTER USER hradmin QUOTA UNLIMITED ON USERS;

GRANT CONNECT, RESOURCE TO hradmin;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO hradmin;

-- Kalau mau full DBA di PDB
GRANT DBA TO hradmin;

sqlplus hradmin/hrpass@//localhost:1521/TGHDBHR
 

.::: Create New Container or add Container CDB in Oracle 19C :::.

 

1. Cek dulu container yang ada

sqlplus sys/oracle as sysdba;

SHOW CON_NAME;
SHOW PDBS;
 

2. Buat PDB baru dari PDB$SEED (template bawaan)

Misalnya mau buat container baru bernama TGHDBHR:

CREATE PLUGGABLE DATABASE TGHDBHR
  ADMIN USER hradmin IDENTIFIED BY hrpass
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/TGH/pdbseed/',
                     '/u01/app/oracle/oradata/TGH/TGHDBHR/');

.::: First time login Oracle using CDB (Container Database / Multitenant) :::.

 

1. login as sysdba 
sqlplus sys/oracle as sysdba;

oracle@teguhth ~]$ sqlplus sys/oracle as sysdba;

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 24 14:12:31 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 
 

.::: Script Backup Full & Incremental All Database Using Record to Table in PostgreSQL EDB :::.

correlation with https://teguhth.blogspot.com/2025/02/script-backup-full-all-database-using.html
correlation with https://teguhth.blogspot.com/2025/01/script-backup-full-all-database-using.html

1. create table 

-- Table: public.backuplogs

CREATE TABLE backuplogs (
    host VARCHAR(255) NOT NULL,
    date TIMESTAMP NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    backup VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
);

.::: Script to Restore Backup Full & all Transaction (Restore Incremental) in one Command for EnterpriseDB PostgreSQL :::.

 

correlation https://teguhth.blogspot.com/2024/02/script-to-restore-backup-full-all.html
correlation https://teguhth.blogspot.com/2025/09/script-daily-backup-full-incremental.html

1. check before restore
 
2. check directory backup & create dblist,txt
[postgres@teguhth dwh]$ pwd
/var/lib/pgsql/16/manual/dwh
[postgres@teguhth dwh]$
[postgres@teguhth dwh]$ cat dblist.txt
FULL_dwh_20250913_000001.sql.gz
INC_dwh_20250913_010002.sql.gz
INC_dwh_20250913_020002.sql.gz
INC_dwh_20250913_030002.sql.gz
INC_dwh_20250913_040002.sql.gz
INC_dwh_20250913_050002.sql.gz
INC_dwh_20250913_060002.sql.gz
INC_dwh_20250913_070001.sql.gz
INC_dwh_20250913_080002.sql.gz
INC_dwh_20250913_090002.sql.gz
INC_dwh_20250913_100001.sql.gz
INC_dwh_20250913_110002.sql.gz
INC_dwh_20250913_120002.sql.gz
INC_dwh_20250913_130001.sql.gz
INC_dwh_20250913_140002.sql.gz
INC_dwh_20250913_150002.sql.gz
INC_dwh_20250913_160001.sql.gz
INC_dwh_20250913_170002.sql.gz
INC_dwh_20250913_180002.sql.gz
INC_dwh_20250913_190001.sql.gz
INC_dwh_20250913_200002.sql.gz
INC_dwh_20250913_210002.sql.gz
INC_dwh_20250913_220002.sql.gz
INC_dwh_20250913_230001.sql.gz
[postgres@teguhth dwh]$
 

.::: Script Daily Backup FULL & Incremental PostgreSQL EDB Using pgdump & pgaudit :::.

 

correlation with https://teguhth.blogspot.com/2025/09/how-to-create-backup-incremental-in.html

1. Backup All FULL
 

 

.::: How to Create Backup Incremental in PostgreSQL EDB as Logical Backup (like SQL Server or MariaDB with mysqlbinlog) using pgaudit pgauditlogtofile :::.

 

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

B. convert to table 

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

C. Testing sample CRUD Activity (sample database 'dwh')

1. create table pembelian;

create table barang(
KODE_BARANG char(6) not null ,
NAMA_BARANG varchar(25),
SATUAN_BARANG varchar(20),
STOK_BARANG decimal(4),
primary key (KODE_BARANG));
  

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