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

.::: How to Use backup & Restore Database MySQL MariaDB using MariaDB Backup :::.

 
A. Backup with mariabackup
1. install mariabackup

yum install -y MariaDB-backup -y

2. check data before testing

select * from teguhth.barang;
select * from teguhth.pembelian;
 

.::: Create dblink using dblink_connect & dblink in PostgreSQL EDB :::.

1. check before setting
select pg_read_file('/etc/hostname') as hostname, version();

psql -h 10.10.10.8 -U admin -p 5432 -d teguhth -c "select pg_read_file('/etc/hostname') as hostname, version();";
psql -h 10.10.10.71 -U admin -p 5444 -d teguhth -c "select pg_read_file('/etc/hostname') as hostname, version();";
 

2. create create new database dblink_edb <optional only case>
teguhth=# create database dblink_edb;
CREATE DATABASE
teguhth=#
 

.::: Create External Table PostgreSQL, EDB using Foreign Data Wrapper (postgres_fdw) like DBLink :::.

1. create new externaldb for sample
create database externaldb;
\c externaldb;

teguhth=# create database externaldb;
CREATE DATABASE
teguhth=# \c externaldb;
You are now connected to database "externaldb" as user "postgres".
externaldb=#

.::: Script Backup Full All Database in PostgreSQL EDB :::.

corelation with http://teguhth.blogspot.com/2023/08/backup-and-restore-postgresql-edb.html

 

 1. check data from table
select * from teguhth.public.pembelian;
select * from edb.public.barang;
 

2. run backup
su - postgres
sh backup_edb_full_script.sh

.::: Script to Restore Backup Full & all Transaction in one Command for MariaDB MySQL :::.

1. check before restore
2. check directory backup & create dblist,txt
ls > dblist.txt

[root@teguhth teguhth]# ls > dblist.txt
[root@teguhth teguhth]# cat dblist.txt
dblist.txt
[FULL]_teguhth_20240217_155359.sql.gz
[LOG]_teguhth_20240217_155414.sql.gz
[LOG]_teguhth_20240217_185039.sql.gz
[LOG]_teguhth_20240217_185041.sql.gz
read.sh
test.sh

.::: Restore Transaction Log Backup in MySQL MariaDB :::.

 
corelation with http://teguhth.blogspot.com/2024/02/script-backup-full-differential.html
and http://teguhth.blogspot.com/2023/09/set-up-incremental-backup-restore-in.html

1. backup full & transaction log
[root@teguhth fix]# sh backup_full_script.sh
[root@teguhth fix]# sh backup_log_script.sh
 

.::: Script Backup Full + Differential + Transaction Log in MariaDB MySQL & Scheduler just like SQL Server :::.

 

1. create script backup full

[root@teguhth fix]# cat backup_full_script.sh

echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address

infotgl=$(date)
dbversion=$(mysql -uroot -proot -sNe "select @@version as VersionDB");
echo ".::: Backup Full All database using custom in '$Server $dbversion' with $ip_address :::."
echo ""
#!/bin/bash
#
#clear;
year=`date +%Y`; month=`date +%m`; day=`date +%d`; hour=`date +%H`; min=`date +%M`; sec=`date +%S`; dayname=`date +%a`;underscore='_';
datex=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S" ");
#echo "Backup begin $infotgl"
 backuppath="/backup";
#mkdir -p $backuppath/$year-$month-$day;
#path="$backuppath/$year-$month-$day";
# Find the latest mysql-bin log file
LATEST_BINLOG=$(ls -1t /var/lib/mysql/mysql-bin.* | grep -v 'mysql-bin.index' | head -n 1)
 

.::: Create user limited to access only spesific table or specific view, store procedure,function in MSSQL SQL Server :::.

 
1. create user & password
USE [master]
GO
CREATE LOGIN [onlyone] WITH PASSWORD=N'admin', DEFAULT_DATABASE=[teguhth], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
use [master];
GO
USE [teguhth]
GO
CREATE USER [onlyone] FOR LOGIN [onlyone]
GO
use [teguhth];
GO
USE [teguhth]
GO
CREATE USER [onlyone] FOR LOGIN [onlyone]
GO
 

.::: Create table DbLink MariaDB MySQL using ODBC/DSN using metode SRCDEF base on query :::.

1. Install Connect Engine
yum install MariaDB-connect-engine -y

2. Check ODBC INI

[root@teguhth ~]# cat /etc/odbc.ini

[edbdb]
Description         = PostgreSQL connection to SallyDB
Driver              = PostgreSQL
Database            = teguhth
Servername          = 10.10.10.8
UserName            = admin
Password            = admin
Port                = 5432
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
#ConnSettings        =
 

.::: Install MariaDB ODBC Driver on Linux & test DSN or odbcins.ini and odbc.ini :::.

 
1. Install mariadb connector odbc
[root@teguhth ~]# yum install -y https://dlm.mariadb.com/3680404/Connectors/odbc/connector-odbc-3.1.20/mariadb-connector-odbc-3.1.20-centos74-amd64.rpm

2. check rpm odbc

[root@teguhth ~]# rpm -qa | grep -i odbc
msodbcsql18-18.3.2.1-1.x86_64
mariadb-connector-odbc-3.1.20-1.x86_64
postgresql-odbc-09.03.0100-2.el7.x86_64
unixODBC-devel-2.3.11-1.rh.x86_64
unixODBC-2.3.11-1.rh.x86_64
[root@teguhth ~]#

.::: Create SQL Audit in SQL Server MSSQL for example Schema Change & Permission Change :::.

 A. Schema Audit
1. Schema change Audit script

USE [master]
GO

/****** Object:  Audit [Schema_Change_Sample]    Script Date: 8/3/2022 6:08:55 PM ******/
CREATE SERVER AUDIT [Schema_Change_Sample]
TO FILE
(    FILEPATH = N'C:\audit\' ---- Change Audit file path
    ,MAXSIZE = 20 MB
    ,MAX_ROLLOVER_FILES = 5
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = 'e5abe6c5-f451-41f3-936c-b1da1c0786ce'
)
ALTER SERVER AUDIT [Schema_Change_Sample] WITH (STATE = ON)
GO