.::: Create user limited to access only spesific table or specific view, store procedure,function in MariaDB MySQL :::.

corelation http://teguhth.blogspot.com/2022/08/create-user-admin-readonly-database.html


1. create user only akses 1 table
CREATE USER 'onlyone'@'%' IDENTIFIED BY 'onlyone';
GRANT SELECT, INSERT, UPDATE, DELETE ON teguhth.pembelian TO 'onlyone'@'%';
ALTER USER 'onlyone'@'%' IDENTIFIED BY 'onlyone';

CREATE USER 'onlyone'@'localhost' IDENTIFIED BY 'onlyone';
GRANT SELECT, INSERT, UPDATE, DELETE ON teguhth.pembelian TO 'onlyone'@'localhost';
ALTER USER 'onlyone'@'localhost' IDENTIFIED BY 'onlyone';

FLUSH PRIVILEGES;
 

.::: Install PostgreSQL ODBC Driver on Linux & test DSN or odbcins.ini and odbc.ini Access PostgreSQL using ODBC :::.

1. install odbc postgres
yum install unixODBC unixODBC-devel postgresql-odbc -y

[root@teguhth data]# rpm -qa | grep -i odbc
msodbcsql18-18.3.2.1-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 data]#
 

-bash-4.2$ cat postgresql.conf | grep -i listen_a
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses ='*'                                   # comma-separated list of addresses;
-bash-4.2$
-bash-4.2$ cat pg_hba.conf | grep -i admin
host   all   admin 10.0.0.0/8 md5
-bash-4.2$

.::: Change the file created date & Modified file in Linux :::.

 1. check size & time file
[root@teguhth data]# ls -lh
total 932K
drwxr-xr-x 3 root root   19 Jan 17 18:01 backup
-rw-r--r-- 1 root root 924K Jun 23  2023 psqlodbc-15.00.0000.tar.gz
-rw-r--r-- 1 root root 7.9K Jan 16 14:59 teguhth_original.sql
[root@teguhth data]#
[root@teguhth data]#
[root@teguhth data]# cp psqlodbc-15.00.0000.tar.gz psqlodbc-15.00.0000_test.tar.gz
[root@teguhth data]# ls -lh
total 1.9M
drwxr-xr-x 3 root root   19 Jan 17 18:01 backup
-rw-r--r-- 1 root root 924K Jun 23  2023 psqlodbc-15.00.0000.tar.gz
-rw-r--r-- 1 root root 924K Jan 29 10:15 psqlodbc-15.00.0000_test.tar.gz
-rw-r--r-- 1 root root 7.9K Jan 16 14:59 teguhth_original.sql
[root@teguhth data]#
[root@teguhth data]#
 

.::: Shell Script to Get CPU Memory Usage (%), Swap (%) & Local Check, all_check GTID Mirroring,IP & Hostname,uptime MariaDB in Centos 9 Redhat 9 Rocky Linux 9 :::.

 1. local check

##[root@teguhth data]# cat local_check.sh
#  cat check_cpu_mem_usage.sh
#!/bin/bash
date
CPU=$(top -bn1 | grep load | awk '{printf "%.2f%%\t\t\n", $(NF-2)}')

Mem_Used_mb=$(free -m | awk 'NR==2{printf "%.f", $3 }')
Mem_Total_mb=$(free -m | awk 'NR==2{printf "%.f", $2 }')

Mem_Used_kb=$(free -m | awk 'NR==2{printf "%.f", $3*1024 }')
Mem_Total_kb=$(free -m | awk 'NR==2{printf "%.f", $2*1024 }')

Mem_Used_gb=$(free -m | awk 'NR==2{printf "%.f", $3/1024 }')
Mem_Total_gb=$(free -m | awk 'NR==2{printf "%.f", $2/1024 }')

.::: Create a dblink to a remote server MariaDB MySQL MarinaDB in Linux to MSSQL SQL Server 2022 in Windows Server 2022 using ha_connect,unixODBC,odbcinst.ini :::.

A. Install & Configuration DBLink
1. install haconnect in Linux Redhat

before 

http://teguhth.blogspot.com/2024/01/install-configure-odbcinstini-odbc-for.html
yum install MariaDB-connect-engine -y

[root@teguhth ~]# yum install MariaDB-connect-engine -y
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.cepatcloud.id
 * epel: mirror2.totbb.net
 * extras: mirror.cepatcloud.id
 * updates: mirror.cepatcloud.id
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-connect-engine.x86_64 0:10.11.6-1.el7.centos will be installed
--> Finished Dependency Resolution
 

.::: Install & Configure odbcinst.ini ODBC for MSSQL SQL Server, Oracle, MySQL MariaDB, PostgreSQL in Linux Centos 7, 8, 9 :::.

 A. ODBC Linux for MSSQL
1. prefer link
https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver16&tabs=redhat18-install%2Calpine17-install%2Cdebian8-install%2Credhat7-13-install%2Crhel7-offline

#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version

.::: Create a link or dblink to a remote server MariaDD MySQL to MSSQL SQL Server using ha_connect :::.

 A. Configure dblink MariaDB
1. install plugin ha_connect
INSTALL SONAME 'ha_connect';

2. install driver Driver=SQL Server Native Client 11.0 or mssqlodbc

SQL Server Native Client 11.0
https://www.microsoft.com/en-us/download/details.aspx?id=50402

or
mssqlodbc
https://www.microsoft.com/en-us/download/details.aspx?id=36434

.::: Create sample multiple dblink MariaDB MySQL to run multiple query Inner Join, Normal Join :::.

1. correlation dblink

https://teguhth.blogspot.com/2024/01/create-link-or-dblink-to-remote-server.html

2. check normal query from remoteserver

select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p
where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;

select @@hostname,b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p
where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;
 

.::: Create a link or dblink to a remote server MariaDD MySQL using federated ha_federatedx.so plugin :::.

A. Sample other database to get database (source)
create database teguhth_maria_dwh;
use teguhth_maria_dwh;
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));

.::: Script to check File backup Database MariaDB MySQL , SQL Server MSSQL success or not in NAS or FS :::.

 1. sample for linux backup mariadb & windows sql server

db linux

db_mariadb_teguh;
db_mariadb_teguh_dmart;

db windows

db_mssql_teguh;
db_mssql_teguh_dmart;
 

.::: Create Quary Daily Backup All Database MariaDB MySQL & retention and Crontab :::.

1. Create script all backup using custom

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

infotgl=$(date)

echo ".::: Backup all database using custom in $Server 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`;
echo "Backup begin $infotgl"
 

.::: How to migrate a SQL Server database to a lower version or Downgrade MSSQL 2022 to 2019 or lower ::.

As of my last knowledge update in January 2022, SQL Server 2022 had not been released. However, if you're using a newer version of SQL Server and want to downgrade to SQL Server 2019,
it's important to note that downgrading a SQL Server database is not a straightforward process. Microsoft does not support direct downgrades, and you may need to go through a series of steps to achieve this.
Here are the general steps you might follow:

A. check version & sample query before downgrade (SQL 2022)