.::: Find ENCRYPTION_KEY_ID & CURRENT_KEY_ID MariaDB from OS & Database MariaDB :::.

 

correlation https://teguhth.blogspot.com/2023/06/enable-encryption-table-database-for.html
https://teguhth.blogspot.com/2023/05/how-to-remove-encryptionkeyid-from-file.html


A. from OS

1. query 1 
grep -o 'ENCRYPTION_KEY_ID`=[0-9]*' teguhth_enc.sql | sort | uniq

2. query 2

grep -o 'ENCRYPTION_KEY_ID`=[0-9]*' teguhth_enc.sql | cut -d= -f2 | sort | uniq

3. query 3

grep -i  ENCRYPTION_KEY_ID teguhth_enc.sql
 

 

.::: Sample Create VIEW, Routine / Store Procedure, Trigger with DEFINER=`simple_admin`@`%` in MariaDB :::.

 

correlation https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query-mysql.html#more

1. create user 

SELECT Host, User FROM mysql.user WHERE User='simple_admin';

create user 'simple_admin'@'%' identified by 'simple_admin';

GRANT CREATE ROUTINE, ALTER ROUTINE, TRIGGER, CREATE VIEW
ON teguhth.* TO `simple_admin`@`%`;

GRANT EXECUTE ON PROCEDURE teguhth.sp_insert_pembelian
TO 'simple_admin'@'%';

GRANT ALL PRIVILEGES ON *.* TO 'simple_admin'@'%' WITH GRANT OPTION;

flush PRIVILEGES;

Kalau DEFINER tidak ada ? ERROR 1449
 

.::: Sample Access Database MariaDB via Frontend & Backend using php :::.

 

1. instal httpd

2. config 

[root@teguhth-all html]# pwd
/var/www/html
[root@teguhth-all html]# cat config.php
<?php
$host = "10.10.10.90";
$user = "admin";
$pass = "admin";
$db   = "teguhth";
$port = 3306;

$conn = new mysqli($host, $user, $pass, $db, $port);

if ($conn->connect_error) {
    die("Koneksi gagal: " . $conn->connect_error);
}
?>

[root@teguhth-all html]#
 

.::: create Tools Migration from SQL Server MSSQL to MariaDB using bash shell script :::.

 

correlation with correlation with https://teguhth.blogspot.com/2024/01/install-configure-odbcinstini-odbc-for.html

1. sample data mssql & mariadb

MySQL MariaDB
https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query-mysql.html

MSSQL
https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query.html

2. data server A & B 
server A sql server
ip : 10.10.10.7
db : teguhth
schema : dbo
port 1433
user : admin 
password : admin11!!

server B Mariadb
ip : 10.10.10.90
db : teguhthsql
port 3306
user : admin
password : admin

mysql -h 10.10.10.90 -uadmin -p --skip-ssl

buat script shell untuk migrasi dari server A mssql to server B 

.::: create Tools Migration from MariaDB to MongoDB using bash shell script :::.

 

 

 

correlation with https://teguhth.blogspot.com/2025/12/create-tools-migration-from-mongodb-to.html
 

1. data migration server A Maria to server B mongo
server A Mariadb
ip : 10.10.10.90
db : teguhth
port 3306
user : admin
password : admin

server B mongodb
ip : 10.10.10.9
db : mariadbsample
port 27017
user : admin
password : admin

buat script shell untuk migrasi dari server A maria to server B mongo 
 

.::: create Tools Migration from MongoDB to MariaDB using bash shell script :::.

 
1. sample data edb & mariadb

MySQL MariaDB
https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query-mysql.html

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

2. data server A & B 
server A mongodb
ip : 10.10.10.9
db : teguhth
port 27017
user : admin
password : admin

server B Mariadb
ip : 10.10.10.90
db : teguhthedb
port 3306
user : admin
password : admin

buat script shell untuk migrasi dari server A postgres to server B

.::: create Tools Migration from PostgreSQL to MariaDB using bash shell script :::.

  

1. sample data edb & mariadb

MySQL MariaDB
https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query-mysql.html

PostgreSQL
https://teguhth.blogspot.com/2019/09/study-kasus-praktis-belajar-query.html

2. data server A & B 
server A Postgres
ip : 10.10.10.9
db : teguhth
port 5432
user : admin
password : admin

server B Mariadb
ip : 10.10.10.90
db : teguhthedb
port 3306
user : admin
password : admin

.::: DBLink MariaDB Manipulation with Shell Scipt to insert table from Server A to Server B :::.

 

A. sample source server A server B sample 

1. record source & destination

=== source ===
ip       : 10.10.10.90
porr     : 3306
database : dbatools
table    : dbsizeos

=== destination ==
ip       : 10.10.10.9
porr     : 3306
database : dbatools
table    : list_dbsizeos
 

.::: Script insert database size & record to table base on size folder in OS Linux example MariaDB :::.

 

correlation https://teguhth.blogspot.com/2025/12/script-check-size-database-base-on-size.html

A. Using basic table info

1. create table

CREATE TABLE IF NOT EXISTS dbsizeos (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    capture_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    db_name VARCHAR(100) NOT NULL,
    size_bytes BIGINT NOT NULL,
    size_mb DECIMAL(12,2) NOT NULL  
);

2.script 

.::: Create Script Backup All Database Full, Differential & Log Using T-SQL & Run using CMD SQL Server - Basic Update :::.

 
correlation https://teguhth.blogspot.com/2023/08/create-script-backup-database-full.html

1. enable xp_cmdshell

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
    
2. create t-sql sp_backup_full_basic

IF DB_ID('dbatools') IS NULL
BEGIN
    CREATE DATABASE [dbatools];
END
GO

USE [dbatools];
GO

CREATE PROCEDURE [dbo].[sp_backup_full_basic]

-- =============================================  
-- Author: Teguh Triharto
-- Create date:  22 March 2000
-- Description: Script Backup Database
-- Website : https://www.linkedin.com/in/teguhth"
-- =============================================  
 

.::: Script check size database base on size folder in OS Linux example database size MariaDB :::.

  

ini shell script sederhana, rapi, dan standar DBA untuk list folder di /var/lib/mysql + hitung size-nya.

1. Versi Paling Simpel (langsung pakai du) semual folder n non folder 
[root@teguhth sizex]# cat cek_foldernisi.sh
#!/bin/bash

BASE_DIR="/var/lib/mysql"

echo "Database Directory Size Report"
echo "=============================="
printf "%-25s %10s\n" "FOLDER" "SIZE"
echo "------------------------------"

du -sh ${BASE_DIR}/* 2>/dev/null | sort -h | while read size folder; do
    printf "%-25s %10s\n" "$(basename "$folder")" "$size"
done

echo "------------------------------"
echo "TOTAL:"
du -sh "$BASE_DIR" | awk '{print $1}'

[root@teguhth sizex]#
 

.::: Simulation API HEAD Using Phyton & MariaDB MySQL with HTTP Status Code 200,404 :::.

 

1. Install phyton

sudo dnf install python3 python3-pip -y
pip3 install flask mysql-connector-python

2. create database sample & insert data  

CREATE DATABASE hris;
USE hris;

CREATE TABLE buy_period (
    id INT AUTO_INCREMENT PRIMARY KEY,
    period_year INT NOT NULL,
    period_month INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL
);

.::: Simulation API DELETE Using Phyton & MariaDB MySQL with HTTP Status Code 500, 422, 200, 405 :::.

  

1. Install phyton

sudo dnf install python3 python3-pip -y
pip3 install flask mysql-connector-python

2. create database sample & insert data  

CREATE DATABASE hris;
USE hris;

CREATE TABLE buy_period (
    id INT AUTO_INCREMENT PRIMARY KEY,
    period_year INT NOT NULL,
    period_month INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL
);
 INSERT INTO buy_period (period_year, period_month, start_date, end_date)
VALUES (2025, 1, '2025-01-01', '2025-01-31');

INSERT INTO buy_period (period_year, period_month, start_date, end_date)
VALUES (2025, 5, '2025-05-01', '2025-05-31');