.::: Enable userstat USER_STATISTICS, CLIENT_STATISTICS, INDEX_STATISTICS, and the TABLE_STATISTICS MariaDB MySQL :::.

A. Enable Statistic Plugin

1. Check configuration statistic
From query
show variables like '%userstat%';

From mariadb configuration
# cat /etc/my.cnf.d/server.cnf | grep userstat


2. Enable userstat configuration From Query ( for temporary configuration)

SET GLOBAL userstat=1;
show variables like 'userstat';

.::: Backup & Restore Database MongoDB Using Mongodump & mongorestore :::.

Step 1: Create Direct Backups Using Mongodump

mongodump
 
[root@teguhth ~]# mongodump
2024-04-15T14:59:40.783+0700    writing admin.system.users to dump/admin/system.users.bson
2024-04-15T14:59:40.789+0700    done dumping admin.system.users (3 documents)
2024-04-15T14:59:40.790+0700    writing admin.system.version to dump/admin/system.version.bson
2024-04-15T14:59:40.790+0700    done dumping admin.system.version (2 documents)
2024-04-15T14:59:40.792+0700    writing teguhth.pembelian to dump/teguhth/pembelian.bson
2024-04-15T14:59:40.793+0700    writing teguhth.pasok to dump/teguhth/pasok.bson
2024-04-15T14:59:40.794+0700    writing teguhth.suplier to dump/teguhth/suplier.bson
2024-04-15T14:59:40.795+0700    writing kampus.mahasiswi to dump/kampus/mahasiswi.bson
2024-04-15T14:59:40.797+0700    done dumping teguhth.pembelian (15 documents)
2024-04-15T14:59:40.798+0700    writing teguhth.barang to dump/teguhth/barang.bson
2024-04-15T14:59:40.799+0700    done dumping teguhth.pasok (12 documents)
2024-04-15T14:59:40.800+0700    writing teguhth.customer to dump/teguhth/customer.bson
2024-04-15T14:59:40.801+0700    done dumping kampus.mahasiswi (8 documents)
2024-04-15T14:59:40.801+0700    writing tka.barang to dump/tka/barang.bson
2024-04-15T14:59:40.802+0700    done dumping teguhth.barang (6 documents)
2024-04-15T14:59:40.803+0700    writing tka.Barang to dump/tka/Barang.bson
2024-04-15T14:59:40.803+0700    done dumping teguhth.customer (6 documents)
2024-04-15T14:59:40.806+0700    done dumping tka.barang (6 documents)
2024-04-15T14:59:40.807+0700    done dumping teguhth.suplier (6 documents)
2024-04-15T14:59:40.808+0700    done dumping tka.Barang (0 documents)
[root@teguhth ~]#

.::: How to enable CDC (Change Data Capture) in PostgreSQL, EDB :::.

1. Enable WAL (Write-Ahead Logging): PostgreSQL uses Write-Ahead Logging for durability. Ensure that your PostgreSQL server is configured to use WAL.

2. Create Audit Table: Create an audit table where you will log the changes.

CREATE TABLE audit_table (
    id SERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    action_type TEXT NOT NULL,
    old_data JSONB,
    new_data JSONB,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 

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

 continue from http://teguhth.blogspot.com/2024/02/enable-pgaudit-pgauditlogtofile-in.html

1. Create table pgaudit_log

CREATE TABLE pgaudit_log (
log_time text,
user_name text,
dbname text,
process_id text,
connection_from text,
session_id text,
session_line_num text,
command_tag text,
session_start_time text,
virtual_transaction_id text,
transaction_id text,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos text,
context text,
query text,
query_pos text,
location text,
application_name text,
backend_type text,
leader_pid text,
query_id text,
custom_1 text,
custom_2 text,
custom_3 text,
console text
);
 

.::: Check Status Backup & Restore Progress in % Percent MSSQL SQL Server :::.

A. Backup

1. Query 1

SELECT @@servername as ServerName, command, percent_complete,total_elapsed_time, estimated_completion_time, start_time
  FROM sys.dm_exec_requests
  WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

.::: How to enable CDC (Change Data Capture) in MariaDB, MySQL, MarinaDB in Table :::.

Enable Binary Logging

1. check table for sample

SELECT *,@@hostname,@@version FROM teguhth.pembelian
 

2. Create table for cdc

# create table

CREATE TABLE audit_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(100),
    action_type ENUM('INSERT', 'UPDATE', 'DELETE'),
    old_data JSON,
    new_data JSON,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SELECT *,@@hostname,@@version FROM audit_table;

.::: Script check cpu usage & memory usage & Total Memory Server SQL Server include using sqlcmd to monitoring NMS :::.

 1. create store procedure cpu monitoring sql server for monitoring

CREATE PROCEDURE sp_total_usage_cpu_SQLProcessUtilization

AS
BEGIN
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

SELECT TOP(1)
               100 - SystemIdle - SQLProcessUtilization + SQLProcessUtilization AS [Total Usage CPU SQL Utilization]
  

.::: Script to check cpu & memory usage service in linux include custome service, mariadb, mysql, mongodb :::.

1. check custome cpu usage and memory usage

[root@teguhth special]# cat custom_service.sh

#!/bin/bash

echo "run custom_service.sh <name_service>"
PIDX=${1}
# Mendapatkan PID dari proses mariadbd
MARIADB_PID=$(pgrep $PIDX)

# Mendapatkan informasi tentang proses mariadbd dari top
TOP_INFO=$(top -n 1 -b -p "$MARIADB_PID" | tail -n +8 | head -n 1)
Mem_Total_mb=$(free -m | awk 'NR==2{printf "%.f", $2 }')
Mem_Total_gb=$(free -m | awk 'NR==2{printf "%.2f", $2/1024 }')

# Mengekstrak nilai CPU dan penggunaan memori dari output top
CPU_USAGE=$(echo "$TOP_INFO" | awk '{print $9}')
MEMORY_USAGE=$(echo "$TOP_INFO" | awk '{print $10}')
MEMORY_B=$(echo "$TOP_INFO" | awk '{print $6 }')

echo "CPU Usage(%) $PIDX   : ${CPU_USAGE}%"
echo "Memory Usage(%) $PIDX: ${MEMORY_USAGE}%"
echo "Memory Usage $PIDX   : ${MEMORY_B}"
echo "Memory Total Server   : $Mem_Total_mb MB or $Mem_Total_gb GB"

[root@teguhth special]#

.::: Study Kasus Praktis belajar Query create & insert table/Collection MongoDB (Data Warehouse) dengan Primary Key :::.

1. correlation with other post

http://teguhth.blogspot.com/2020/12/study-kasus-praktis-belajar-query.html

2. create collection with primarykey

use teguhth
show dbs

show collections
db.createCollection("barang")
db.createCollection("suplier")
db.createCollection("customer")
db.createCollection("pasok")
db.createCollection("pembelian")
show collections
 

.::: Create User MongoDB role admin and readonly and authenticationRestrictions :::.

1. create user admin

use admin
db.createUser({ user: "admin",pwd: "admin",
roles: [{ role: "readAnyDatabase", db: "admin" },{ role: "readWriteAnyDatabase", db: "admin" }]});
show users;

.::: Create a dblink to a remote server MariaDD MySQL to PostgreSQL EDB and using metode SRCDEF :::.

1. install ODBC postgresql * configure

http://teguhth.blogspot.com/2024/01/install-postgresql-odbc-driver-on-linux.html

A. using dblink

1. create table dblink example table barang
CREATE TABLE table_edb_barang(
KODE_BARANG char(6) not null ,
NAMA_BARANG varchar(25),
SATUAN_BARANG varchar(20),
STOK_BARANG decimal(4),
primary key (KODE_BARANG)) ENGINE = CONNECT TABLE_TYPE=ODBC TABNAME='barang' CONNECTION='DSN=edbdb';

.::: Access MariaDB from PostgreSQL using dblink /FOREIGN DATA WRAPPER mysql_fdw :::.

1. install mysql_fdw
wget https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-7.9-x86_64/mysql_fdw_15-2.8.0-2.rhel7.x86_64.rpm
yum install mysql_fdw_15-2.8.0-2.rhel7.x86_64.rpm -y

2. create extension;

teguhth=# \c edbmaria;
You are now connected to database "edbmaria" as user "postgres".
edbmaria=#

edbmaria=# CREATE EXTENSION mysql_fdw;
CREATE EXTENSION
edbmaria=#
SELECT * FROM pg_extension;
 

.::: Enable pgaudit & pgauditlogtofile in PostgreSQL EDB ::.

1. Download pgaudit & pgauditlogtofile
https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8.2-x86_64/
yum install -y https://rpmfind.net/linux/centos-stream/9-stream/AppStream/x86_64/os/Packages/pgaudit-16.0-1.module_el9+807+b1de07f3.x86_64.rpm
yum install -y https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8.2-x86_64/pgauditlogtofile_16-1.5.12-1PGDG.rhel8.x86_64.rpm

2. check rpm & extension

[root@teguhth ~]# rpm -qa | grep -i postgres
postgresql16-server-16.0-1PGDG.rhel8.x86_64
postgresql16-contrib-16.0-1PGDG.rhel8.x86_64
postgresql16-16.0-1PGDG.rhel8.x86_64
postgresql16-libs-16.0-1PGDG.rhel8.x86_64
[root@teguhth ~]#