.::: How to enable the MySQL/MariaDB general query logs :::.

1. enable general log temporary

SET GLOBAL general_log_file='/var/lib/mysql/general.log';
SET GLOBAL log_output = 'FILE,TABLE';
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log = 1;
 

.::: Create User Access MariaDB MySQL using PAM example OS Linux Login :::.

 
1. install pam plugin

INSTALL PLUGIN pam SONAME 'auth_pam';

MariaDB [(none)]> INSTALL PLUGIN pam SONAME 'auth_pam';
ERROR 1968 (HY000): Plugin 'pam' already installed
MariaDB [(none)]>

2. check plugin pam

show plugins
SELECT plugin_name,plugin_version,plugin_status,plugin_type,plugin_library,plugin_author FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%pam%';

.::: Script Backup Full + Differential + Transaction Log in MariaDB MySQL & Scheduler just like SQL Server for SingleDB & MultipleDB include Restore in One File Script :::.

 
A. SingleDB

1. run script one time to full backup

sh /data/script/scriptbackup/backup_full_log_single_db.sh
 

2. run script two time to log backup

sh /data/script/scriptbackup/backup_full_log_single_db.sh

.::: How to configure Transparent Data Encryption (TDE), Monitoring & Remove TDE in SQL Server :::.

 

A. Create TDE

1. Create Master Key

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='TDEpassword!';
GO
 
 
2. Create Certificate protected by master key
CREATE CERTIFICATE TDE_Cert
WITH
SUBJECT='Database_Encryption';
GO

.::: Additional Environment PostgreSQL EDB example pg_ctl: command not found :::.

 1. Run pg_ctl

pg_ctl
[root@teguhth ~]# su - postgres
Last login: Sun May 12 08:18:13 WIB 2024 on pts/1
-bash-4.2$ pg_ctl -D /var/lib/pgsql/15/data status
-bash: pg_ctl: command not found
-bash-4.2$
-bash-4.2$ pg_ctl -D /var/lib/pgsql/15/data start
-bash: pg_ctl: command not found
-bash-4.2$
-bash-4.2$ pg_ctl -D /var/lib/pgsql/15/data stop
-bash: pg_ctl: command not found
-bash-4.2$
-bash-4.2$

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

 

correlation with https://teguhth.blogspot.com/2025/02/add-sensor-microsoft-sql-v2-sensor.html

 

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