.::: Grouping if using filter Day,date & year in MariaDB MySQL :::.

1. correlation with archive table http://teguhth.blogspot.com/2023/12/grouping-sql-server-by-dateday-dan_18.html

2. sample query all
select @@hostname,ID, Tanggal, Jumlah from teguhth.Transaksi;

.::: Grouping SQl Server by Date/day. dan Archive Database (table) base on Date/Day In MariaDB / MySQL :::.

1. create database source & destination;
create database teguhth;
create database teguhth_archive;

.::: Grouping SQl Server by Date/day. dan Archive Database (table) base on Date/Day SQL Server :::

 
1. create database source & destination
create database teguhth;
create database teguhth_archive;

select @@servername as ServerName,* from sys.sysdatabases where name like '%teguhth%'

.::: Script to backup All user & specific MySQL MariaDB & MarinaDB :::.

A. Backup Specification User MariaDB MySQL

1. capture user
select @@hostname,user,host from mysql.user where user like 'teguh%';
show grants for 'teguh'@'%';
show grants for 'teguh'@'localhost';

MariaDB [(none)]> select @@hostname,user,host from mysql.user where user like 'teguh%';
+------------+-------+-----------+
| @@hostname | User  | Host      |
+------------+-------+-----------+
| teguhth    | teguh | %         |
| teguhth    | teguh | localhost |
+------------+-------+-----------+
2 rows in set (0.006 sec)
 

.::: How to SwitchOver Master Slave GTID Replication MariaDB Using MaxScale :::.

 A. check status before swo

1. query to cek master
mysql -uroot -p -e "select @@hostname,@@version";
mysql -uroot -p -e "show master status";
mysql -uroot -p -e "show slave status\G" | grep -i -E "Slave_IO_State:| Master_Host:| Master_User:| Master_Port:| Relay_Master_Log_File:| Exec_Master_Log_Pos:| Seconds_Behind_Master:| Master_Server_Id:| Using_Gtid:| Gtid_IO_Pos:| Slave_SQL_Running_State:";

.::: Using MariaDB MySQL Show Binary Logs in, clause, limit, convert to sql base datetime & position :::.

 

1. show bin log event (in, from, limit)
MariaDB [(none)]> SHOW BINLOG EVENTS in 'mysql-bin.000027';
+------------------+------+-------------------+-----------+-------------+------------------------------------------------+
| Log_name         | Pos  | Event_type        | Server_id | End_log_pos | Info                                           |
+------------------+------+-------------------+-----------+-------------+------------------------------------------------+
| mysql-bin.000027 |    4 | Format_desc       |         1 |         256 | Server ver: 10.5.21-MariaDB-log, Binlog ver: 4 |
| mysql-bin.000027 |  256 | Gtid_list         |         1 |         299 | [1-1-138]                                      |
| mysql-bin.000027 |  299 | Binlog_checkpoint |         1 |         342 | mysql-bin.000027                               |
| mysql-bin.000027 |  342 | Gtid              |         1 |         384 | GTID 1-1-139                                   |
| mysql-bin.000027 |  384 | Query             |         1 |         485 | create database mariabinlog                    |
| mysql-bin.000027 |  485 | Gtid              |         1 |         527 | GTID 1-1-140                                   |
| mysql-bin.000027 |  527 | Query             |         1 |         610 | drop database tkl                              |
| mysql-bin.000027 |  610 | Gtid              |         1 |         652 | GTID 1-1-141                                   |
| mysql-bin.000027 |  652 | Query             |         1 |         735 | drop database tk2                              |
| mysql-bin.000027 |  735 | Gtid              |         1 |         777 | GTID 1-1-142                                   |
| mysql-bin.000027 |  777 | Query             |         1 |         860 | drop database tka                              |
| mysql-bin.000027 |  860 | Gtid              |         1 |         902 | GTID 1-1-143                                   |
| mysql-bin.000027 |  902 | Query             |         1 |         985 | drop database asd                              |
| mysql-bin.000027 |  985 | Gtid              |         1 |        1027 | GTID 1-1-144                                   |
| mysql-bin.000027 | 1027 | Query             |         1 |        1112 | drop database asde                             |
| mysql-bin.000027 | 1112 | Gtid              |         1 |        1154 | GTID 1-1-145                                   |
| mysql-bin.000027 | 1154 | Query             |         1 |        1241 | drop database asdet                            |
| mysql-bin.000027 | 1241 | Gtid              |         1 |        1283 | GTID 1-1-146                                   |
| mysql-bin.000027 | 1283 | Query             |         1 |        1390 | create database triallogbackup                 |
| mysql-bin.000027 | 1390 | Gtid              |         1 |        1432 | GTID 1-1-147                                   |
| mysql-bin.000027 | 1432 | Query             |         1 |        1533 | create database mafiabackup                    |
| mysql-bin.000027 | 1533 | Gtid              |         1 |        1575 | GTID 1-1-148                                   |
| mysql-bin.000027 | 1575 | Query             |         1 |        1656 | drop database ls                               |
+------------------+------+-------------------+-----------+-------------+------------------------------------------------+
23 rows in set (0.000 sec)
 

.::: How to Administration & Maintenance MaxScale v23 for MariaDB, MySQL :::.

 corelation http://teguhth.blogspot.com/2022/06/how-to-install-and-configure-maxscale.html

1. set maintenance
maxctrl list servers
maxctrl set server galera02-v12 maintenance
maxctrl list servers

.::: How to Check Uptime PostgreSQL, EDB, Yugabyte & Service PostgreSQL server was started :::.

 
1. check starttime

teguhth=# SELECT pg_postmaster_start_time() as StartTime;
           starttime
-------------------------------
 2023-10-19 09:12:12.569427+07
(1 row)

teguhth=#

.::: Get BINLOG_GTID_POS, Slave status, master status in MariaDB, MySQL :::.

 
1. check master status in master

SHOW MASTER STATUS

[root@teguhth01 ~]# mysql -uroot -proot -e "SHOW MASTER STATUS;"
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000016 |      471 |              |                  |
+------------------+----------+--------------+------------------+
[root@teguhth01 ~]#

.:: How to Fix Error: Failed to download metadata for repo CentOS-8 'AppStream': Cannot prepare internal mirrorlist: No URLs in mirrorlist (Install Centos 8) :::.

 
Make sure for instalation like

add repository "Minimal"
file://    /run/install/repo/minimal
 

1. Try Install
[root@teguhth02 ~]# yum install wget -y
CentOS-8 - AppStream                                                                                                               50  B/s |  38  B     00:00
Error: Failed to download metadata for repo 'AppStream': Cannot prepare internal mirrorlist: No URLs in mirrorlist
[root@teguhth02 ~]#
 

.::: Upgrade PostgreSQL from 15 to 16 on CentOS 8 :::.

 1. check version before upgrade
SELECT version();
select pg_read_file('/etc/hostname') as hostname, version();
 
[root@teguhth ~]# su - postgres
Last login: Wed Oct 11 11:15:58 WIB 2023 on pts/1
-bash-4.2$ psql
psql (15.4)
Type "help" for help.

postgres=# SELECT version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

postgres=# select pg_read_file('/etc/hostname') as hostname, version();
 hostname |                                                 version
----------+---------------------------------------------------------------------------------------------------------
 teguhth +| PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
          |
(1 row)

postgres=#
 

.:: Simulate Deadlock in PostgreSQL & Solution :::.

 1. create table

CREATE TABLE ABC (ID INT PRIMARY KEY);
CREATE TABLE XYZ (ID INT PRIMARY KEY);

2. Open Session – 1 and execute below code:

BEGIN;
INSERT INTO ABC VALUES (1); select '1';

.::: How to Sample Simulation Blocking in PostgreSQL EDB :::.

1. Create a Test Table:

-bash-4.2$ psql
psql (15.4)
Type "help" for help.

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


First, create a test table in your PostgreSQL database:
\c teguhth

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);
 

.::: Enable NFS server in windows Server for Linux & Windows clients :::.

A. enable NSF Server & NSF Client in windows

1. enable role nfs

2. add folder to becoma sharefolder

B. Enable NFS Client to Linux
1. Install nfs client
yum install nfs-utils -y

2. mount for temporary
mount -t nfs mssql:/tmp /media/backup
 

.::: Install Percona XtraDB Cluster 5.7 in Centos 7 (Galera for MySQL ) :::.

 A. Install Percona XtraDB 5.7

1. Install requirement lsof qpress socat
yum install https://mirrors.cloud.tencent.com/percona/centos/7/RPMS/x86_64/qpress-11-1.el7.x86_64.rpm -y
yum install lsof qpress socat -y

[root@teguhth01 data]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.51 teguhth01
10.10.10.52 teguhth02
[root@teguhth01 data]#
 

.::: Enabling GTIDs Replication in MySQL 8 for Master - Slave or Mirroring :::.

almost from mariadb
http://teguhth.blogspot.com/2023/06/enabling-gtids-replication-in-mariadb.html

A. Configure GTID Replica MySQL 8
1. install mysql8 on server01 & server02
http://teguhth.blogspot.com/2023/07/how-to-install-mysql-8-on-centos-7.html

2. configure in server01 & restart mysql

server_id = 1          # Unique ID for the primary server
log-bin = mysql-bin    # Binary log file name prefix
binlog_format = mixed  # Mixed format allows GTIDs
gtid_mode = ON         # Enable GTID mode
enforce_gtid_consistency = true
slave-skip-errors=1062,1032

[root@teguhth01 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
 

.::: Backup User User in MariaDB MySQL & restore user user using mysql dump :::.

 1. create user
create user01, user02, user03

CREATE USER 'user01'@'localhost' IDENTIFIED BY 'user';
GRANT ALL PRIVILEGES ON *.* TO 'user01'@'localhost' WITH GRANT OPTION;

CREATE USER 'user02'@'localhost' IDENTIFIED BY 'user';
GRANT ALL PRIVILEGES ON *.* TO 'user02'@'localhost' WITH GRANT OPTION;

CREATE USER 'user03'@'localhost' IDENTIFIED BY 'user';
GRANT ALL PRIVILEGES ON *.* TO 'user03'@'localhost' WITH GRANT OPTION;

FLUSH PRIVILEGES;

check user
select @@hostname, user, host, password, authentication_string from mysql.user;

.::: Clear out /Cleansing MySql MariaDB binary log files :::.

A. clearing using interval day

1. show binary logs before;

MariaDB [(none)]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       389 |
| mysql-bin.000002 |      1110 |
| mysql-bin.000003 |      9773 |
| mysql-bin.000004 |       342 |
+------------------+-----------+
4 rows in set (0.000 sec)

MariaDB [(none)]> 

.::: Monitor Mirroring SQL Server using T-SQL SQL Server :::.

continue from http://teguhth.blogspot.com/2018/10/how-to-mirroring-microsoft-sql-server.html

1. Query monitoring fron Principal  
SELECT @@servername as ServerName,
   SERVERPROPERTY('ServerName') AS Principal,
   m.mirroring_partner_instance AS Mirror,
   DB_NAME(m.database_id) AS DatabaseName,
   SUM(f.size*8/1024) AS DatabaseSizeMB,
   CASE m.mirroring_safety_level
      WHEN 1 THEN 'HIGH PERFORMANCE'
      WHEN 2 THEN 'HIGH SAFETY'
   END AS 'OperatingMode',
   RIGHT(m.mirroring_partner_name, CHARINDEX( ':', REVERSE(m.mirroring_partner_name) + ':' ) - 1 ) AS Port
FROM sys.database_mirroring m
JOIN sys.master_files f ON m.database_id = f.database_id
WHERE m.mirroring_role_desc = 'PRINCIPAL'
GROUP BY m.mirroring_partner_instance, m.database_id, m.mirroring_safety_level, m.mirroring_partner_name

.::: Monitor Transaction Log shipping using T-SQL SQL Server :::.

 continue from http://teguhth.blogspot.com/2021/10/configure-log-shipping.html

A. Monitor Transaction Log shipping using T-SQL

1. Get last Backup of database with LSN (Publisher Side):

SELECT   d.name, b.*
FROM     master.sys.sysdatabases d
LEFT OUTER JOIN msdb..backupset b ON b.database_name = d.name AND b.type = 'L'
where d.name = clasicdb
--<dbnamenya apa>
ORDER BY backup_finish_date DESC
 

.:: Set Up Incremental Backup & restore in MariaDB MySQL using enable Binary Logs & convert mysqlbinlog to sql :::.

A. Enable incremental backup
There are two types of backups - full and incremental backups.

A full backup has a collection of all MySQL queries.

An incremental backup strategy saves the data that has been modified since the previous backup.

Learn how to set up MySQL incremental backups with Mysqldump and Binary log.

1. Enable Binary Logging in /etc/my.cnf.d/server.cnf

#log-bin=/data/mysql-bin.log
log_bin = /var/lib/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_format = mixed

# Disabling binary logging

# --binlog-ignore-db=database_name or --binlog-do-db=database_name
# skip-log-bin

cek mariadb

MariaDB [(none)]> show global variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_compress                | OFF                            |
| log_bin_compress_min_len        | 256                            |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
7 rows in set (0.001 sec)

MariaDB [(none)]>

.::: How to Sample Simulation Blocking, Identifying and Avoiding Deadlocks MariaDB MySQL :::.

 1. open 3 terminal to connect mariadb

[root@teguhth ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.5.21-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use teguhth;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [teguhth]>

.::: Upgrade PostgreSQL from 14 to 15 on CentOS 7 :::.

1. chek version before upgrade
SELECT version();
select pg_read_file('/etc/hostname') as hostname, version();
 

2. backup all
su - postgres -c "pg_dumpall -p 5432 > /data/edb/backupupgrade/all_14posg.sql"

3. install postgres15 as
detail http://teguhth.blogspot.com/2021/07/install-postgresql-linux-using.html

wget http://mirror.centos.org/centos/8-stream/BaseOS/x86_64/os/Packages/libzstd-1.4.4-1.el8.x86_64.rpm
rpm -ivh libzstd-1.4.4-1.el8.x86_64.rpm

yum -y install postgresql15-server

[root@teguhth data]# wget http://mirror.centos.org/centos/8-stream/BaseOS/x86_64/os/Packages/libzstd-1.4.4-1.el8.x86_64.rpm
[root@teguhth data]# rpm -ivh libzstd-1.4.4-1.el8.x86_64.rpm
[root@teguhth data]# yum -y install postgresql15-server
[root@teguhth data]#

.::: Check Database Size and Table Size in PostgreSQL , EDB, YugeByte :::.

 1. Check size db Postgresql
SELECT pg_database.datname,
pg_database_size(pg_database.datname) AS DBSizeB,
pg_database_size(pg_database.datname)/1024 AS DBSizeKB,
pg_database_size(pg_database.datname)/1024/1024 AS DBSizeMB,
pg_database_size(pg_database.datname)/1024/1024/1024 AS DBSizeGB
FROM pg_database;
 

.::: How to Check MySQL MariaDB Database Size, Total All Size Database And Table Size, Uptime Time, Create Time Table :::.

 1. type table
SELECT @@hostname as ServerName,
    table_schema,
    table_name,
    table_type,
    row_format,
    table_rows
FROM
    information_schema.tables
WHERE
    table_schema = 'teguhth'     
ORDER BY TABLE_NAME,table_rows desc;
 

::: Backup & Restore MariaDB MySQL using bat Script in windows :::.

1. run script fullbackup Full_backup_mariadb.bat

2. run script transaksi log back_log_mariadb_windows.bat

3. result

 

 

.:::How to Check Disk Type SSD in Linux & Windows :::.

A. Check Disk Type in Windows
you can also use the Command Prompt to check whether you have an SSD or an HDD in Windows 11. Here's how you can do it:

Open Command Prompt:
To open the Command Prompt, you can press Win + R to open the Run dialog, type in "cmd," and press Enter. Alternatively, you can search for "Command Prompt" in the Start menu and open it from there.

Run Command:
In the Command Prompt window, type the following command and press Enter:

.::: Query Script Backup Full & Transaction Log WAL Shipping PostgreSQL, EDB :::.

 
1. run script full backup
sh full_backup_edb.sh

-bash-4.2$ cd /var/lib/pgsql/14/edb/
-bash-4.2$ ls
full  full_backup_edb.sh  wal_backup_edb.sh  wal_edb
-bash-4.2$ sh full_backup_edb.sh
pg_dump: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "admin"
Full backup successfully created and compressed at 2023-08-11_16:56:33.
-bash-4.2$
-bash-4.2$ sh full_backup_edb.sh
pg_dump: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "admin"
Full backup successfully created and compressed at 2023-08-11_16:56:37.
-bash-4.2$
-bash-4.2$ sh full_backup_edb.sh
pg_dump: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "admin"
Full backup successfully created and compressed at 2023-08-11_16:57:12.
-bash-4.2$
-bash-4.2$ sh full_backup_edb.sh
pg_dump: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "admin"
Full backup successfully created and compressed at 2023-08-11_16:58:39.
-bash-4.2$
 

.::: Shell Script to Get CPU Memory Usage (%), Swap (%) & Local Check, all_check GTID Mirroring,IP & Hostname,uptime MariaDB in Unix, Linux & Solaris 10 from Local & Remote :::.

A. From Local

 

 1. create script to create cpu memory

[root@teguhth ~]# cat check_cpu_mem_usage.sh
#!/bin/bash

# Get CPU and Memory usage using top command
TOP_OUTPUT=$(top -b -n 1)

# Extract CPU usage percentage
CPU_USAGE=$(echo "$TOP_OUTPUT" | awk '/Cpu/ {print $2}')

.::: Microsoft Script Bat : Create Store Procedure Simple Calculator :::.

 
1. create Simple_Calculator.sh

@echo off
echo Simple Calculator
set /p "num1=Enter first number: "
set /p "num2=Enter second number: "
set /p "operator=Enter operator (+, -, *, /): "

if "%operator%"=="+" (
    set /a "result=num1 + num2"
) else if "%operator%"=="-" (
    set /a "result=num1 - num2"
) else if "%operator%"=="*" (
    set /a "result=num1 * num2"
) else if "%operator%"=="/" (
    set /a "result=num1 / num2"
) else (
    echo Invalid operator
    exit /b 1
)

echo Result: %result%
 

.::: Linux Unix Shell Script : Create Store Procedure Simple Calculator :::.

 1. create Simple_Calculator.sh
[root@teguhth data]# cat Simple_Calculator.sh
#!/bin/bash

echo "Simple Calculator"
echo "Enter first number:"
read num1

echo "Enter second number:"
read num2

.::: PostgreSQL EDB : Create Store Procedure Simple Calculator :::.

 
1. Create simple calculator
CREATE OR REPLACE FUNCTION Sp_SimpleCalculator(
    num1 NUMERIC,
    num2 NUMERIC,
    operator CHAR(1)
) RETURNS NUMERIC AS $$
BEGIN
    CASE
        WHEN operator = '+' THEN RETURN num1 + num2;
        WHEN operator = '-' THEN RETURN num1 - num2;
        WHEN operator = '*' THEN RETURN num1 * num2;
        WHEN operator = '/' THEN RETURN num1 / num2;
        ELSE RETURN NULL;
    END CASE;
END;
$$ LANGUAGE plpgsql;

.::: MariaDB MySQL : Create Store Procedure Simple Calculator :::.

 
1. Create store procedure

DELIMITER //

CREATE PROCEDURE Sp_SimpleCalculator(
    IN num1 DECIMAL(10, 2),
    IN num2 DECIMAL(10, 2),
    IN operator CHAR(1),
    OUT result DECIMAL(10, 2)
)
BEGIN
    CASE
        WHEN operator = '+' THEN SET result = num1 + num2;
        WHEN operator = '-' THEN SET result = num1 - num2;
        WHEN operator = '*' THEN SET result = num1 * num2;
        WHEN operator = '/' THEN SET result = num1 / num2;
        ELSE SET result = NULL;
    END CASE;
END //

DELIMITER ;
 

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

1. Create Script Backup Full > sp_backup_full


CREATE procedure [dbo].[sp_backup_full]
as
begin
DECLARE @BackupPath NVARCHAR(255);
DECLARE @DateSuffix NVARCHAR(20);
DECLARE @BackupFileName NVARCHAR(255);
declare @DBName NVARCHAR(255);
-- Replace 'YourDatabaseName' with the name of your database
SET @DBName ='teguhth'
SET @BackupPath = 'C:\backupdb_full\' -- Replace 'C:\YourBackupFolder\' with the desired backup folder path
 

.::: MSSQL: Create Store Procedure Simple Calculator :::.

1. Create store procedure Sp_Simple_Calculator

CREATE PROCEDURE Sp_Simple_Calculator
    @Num1 decimal,
    @Num2 decimal
AS
BEGIN
    SELECT @@ServerName as ServerName,db_name() as [DBName],getdate() [Date],'Result @Num1 & @Num2' as Desctiption,@Num1 as [Num1],@Num2 as [Num2], @Num1 + @Num2 AS 'Result Additional',
    @Num1 - @Num2 AS 'Result Substraction',@Num1 * @Num2 AS 'Result Multiplication',@Num1 / @Num2 AS 'Result Division';
END
 

.::: Backup and Restore PostgreSQL EDB Databases using Pg_dump Utility :::.

1. Backup Single Database

 pg_dump -h [host address] -Fc -o -U [database user] <database name> > [dump file]

 pg_dump -h 10.10.10.8 -p 5432 -U admin teguhth > teguhth_remote.sql 

restore

psql -h 10.10.10.8 -p 5432 -U admin teguhth  < teguhth_remote.sql

Backup

pg_dump teguhth  > teguhth_singledb.sql

-bash-4.2$ pg_dump teguhth  > teguhth_singledb.sql

[root@teguhth edb]# su - postgres
Last login: Tue Jul 25 13:09:57 WIB 2023 on pts/1
-bash-4.2$ cd 14/backups/
-bash-4.2$ pwd
/var/lib/pgsql/14/backups
-bash-4.2$
-bash-4.2$ ls
-bash-4.2$
-bash-4.2$ pg_dump teguhth  > teguhth_singledb.sql
-bash-4.2$
-bash-4.2$ ls
teguhth_singledb.sql
-bash-4.2$ ls -l
total 8
-rw-r--r-- 1 postgres postgres 7023 Jul 25 13:12 teguhth_singledb.sql
-bash-4.2$
 

.::: Backup & Restore MariaDB MySQL using remote from another Server/ Client :::.

1. check from destination (sever teguhth-lab02 database teguhth02_source )

mysql -uroot -p
select @@hostname,@@version;
show databases like '%teguhth02%';
select * from teguhth02_source.barang;
select * from teguhth02_destination.barang;

log

.::: Backup & Restore MariaDB MySQL using Shell Script in Linux :::.

1. create user backup

mysql -u root -p

CREATE USER 'backupuser'@'%' IDENTIFIED BY 'password';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON *.* TO 'backupuser'@'%';
GRANT RELOAD ON *.* TO 'backupuser'@'%';
FLUSH PRIVILEGES;

CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON *.* TO 'backupuser'@'localhost';
GRANT RELOAD ON *.* TO 'backupuser'@'localhost';
FLUSH PRIVILEGES;
 

.::: How to Maintenance Cleanup Task for delete file Backup Database Full, Differential & Transaction Log SQL Server Using Maintenance Plan :::.

1. Clean backup Database Full
2. Clean backup database Differential
3. Clean backup database Transaction Log

 

.::: How to Backup Database Full, Differential & Transaction Log SQL Server Using Maintenance Plan :::.

1. backup Database Full
2. backup database Differential
3. backup database Transaction Log

 

.::: How to Check Uptime MariaDB & Service MariaDB MySQL server was started :::.

 1. uptime 1
SHOW GLOBAL STATUS LIKE 'Uptime';
MariaDB [teguhth]> SHOW GLOBAL STATUS LIKE 'Uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 165   |
+---------------+-------+
1 row in set (0.001 sec)

MariaDB [teguhth]>
 

.::: Get backup Error ERROR 1118 (42000) at line 52172: Row size too large (> 8126) BLOB prefix of 768 bytes :::.

 

1. log as alonge backup

ERROR 1118 (42000) at line 52172: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
 

.::: How to Setting Streaming replication Redundant / Mirroring (Master Slave) EnterpriseDB 15 on Centos 7 :::.

 
 
A.

B. Setting di Master Node
1. Create user replication

su - enterprisedb

CREATE USER replicate REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'admin';
ALTER ROLE replicate CONNECTION LIMIT 5;

[root@teguhth ~]# su - enterprisedb
Last login: Thu Jul 20 14:21:33 WIB 2023 on pts/0
-bash-4.2$ psql edb
psql (15.3.0, server 15.3.0)
Type "help" for help.

edb=# CREATE USER replicate REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'admin';
CREATE ROLE
edb=#
edb=# ALTER ROLE replicate CONNECTION LIMIT 5;
ALTER ROLE
edb=#
 

.::: Install EnterpriseDB 15 (EDB 15) in Centos 7 :::.

 
1. get repo access from web login using new account only one month
https://www.enterprisedb.com/accounts/login

https://www.enterprisedb.com/repos


2. curl & install setup.rpm.sh
[root@teguhth data]# curl -1sLf 'https://downloads.enterprisedb.com/f9xxxxxxx/enterprise/setup.rpm.sh' | sudo -E bash
Executing the  setup script for the 'enterprisedb/enterprise' repository ...

   OK: Checking for required executable 'curl' ...
   OK: Checking for required executable 'rpm' ...
   OK: Detecting your OS distribution and release using system methods ...
 ^^^^: ... Detected/provided for your OS/distribution, version and architecture:
 >>>>:
 >>>>: ... distro=centos  version=7  codename=Core  arch=x86_64
 >>>>:

.::: How To Install MySQL 8 on CentOS 7 :::.

1. download repository
https://dev.mysql.com/downloads/repo/yum/
wget https://repo.mysql.com//mysql80-community-release-el7-7.noarch.rpm

[root@teguhth data]# wget https://repo.mysql.com//mysql80-community-release-el7-7.noarch.rpm
--2023-07-03 14:27:10--  https://repo.mysql.com//mysql80-community-release-el7-7.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 23.201.152.213
Connecting to repo.mysql.com (repo.mysql.com)|23.201.152.213|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 11196 (11K) [application/x-redhat-package-manager]
Saving to: ‘mysql80-community-release-el7-7.noarch.rpm’

100%[========================================================================================================================>] 11,196      --.-K/s   in 0.005s

2023-07-03 14:27:12 (2.11 MB/s) - ‘mysql80-community-release-el7-7.noarch.rpm’ saved [11196/11196]

[root@teguhth data]# ls
mysql80-community-release-el7-7.noarch.rpm
[root@teguhth data]#

.::: Backup Database Full & Transaction & Restore Database SQL Server Alternative Full Script :::.

 
still corelation http://teguhth.blogspot.com/2023/05/backup-database-full-transaction.html

1. check database position
select @@servername as ServerName,db_name(database_id) as DbName, name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('secretdb')

select @@servername as ServerName,db_name(database_id) as DbName, name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('db_restore');

.::: Move SQL Server MDF and LDF Files location :::.

Step 1: Original Location

SELECT @@servername as ServerName,name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('secretdb');
 

Step 2: Take Database Offline
ALTER DATABASE secretdb SET OFFLINE;

 

.::: Trouble Shooting GTID Replication, slave not running :::.

 1. check slave status
MariaDB [teguhth]> select @@hostname,@@version;
+------------+---------------------+
| @@hostname | @@version           |
+------------+---------------------+
| teguhth02  | 10.5.21-MariaDB-log |
+------------+---------------------+
1 row in set (0.000 sec)

.::: Enabling GTIDs Replication in MariaDB Server for Master - Slave or Mirroring :::.

 1. enable /etc/hosts <optional>

[root@teguhth01 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.31 teguhth01
10.10.10.32 teguhth02
10.10.10.33 teguhth03
10.10.10.34 teguhth04
10.10.10.35 teguhth05
[root@teguhth01 ~]#

.::: Upgrading from MariaDB 10.4 to MariaDB 10.6 :::.

1. check Repo for 10.4
[root@teguhth ~]# cat /etc/yum.repos.d/mariadb.repo
[mariadb-main]
name = MariaDB Server
baseurl = https://dlm.mariadb.com/repo/mariadb-server/10.4/yum/rhel/7/x86_64
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Server-GPG-KEY
gpgcheck = 0
enabled = 1

.::: Enable Log MariaDB MySQL & limited Size Log :::.

1. Check directory mariadb
[root@teguhth mysql]# pwd
/var/lib/mysql
[root@teguhth mysql]#
[root@teguhth mysql]# ls
aria_log.00000001  ib_buffer_pool  ib_logfile0  multi-master.info  mysql.sock          performance_schema  teguhth
aria_log_control   ibdata1         ibtmp1       mysql              mysql_upgrade_info  secretdb            teguhth.pid
[root@teguhth mysql]#

.::: Check Number Active User SQL Server connections :::.

sp_who2 'active'

1. Query 1
SELECT @@servername as ServerName,a.*
FROM
(SELECT
DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid, loginame) a
ORDER BY a.DBName, a.LoginName
 

.::: List sessions / active connections on MySQL, MariaDB :::.

show status where variable_name like 'threads%';

1. open tab useing cli or mysql workbench or dbviewer
 

::: Test Result Backup from Database NonEncrypted & Restore Database Encrypted and otherwise :::.

A. Continue from
http://teguhth.blogspot.com/2023/06/test-result-backup-from-database.html

backup restore nonencripted/ teguh02 and restore to encripted then continue below
then backup encripted teguhth and restore to nonencripted/ > result success

B. backup & restore again

1. backup database from restore encripted
MariaDB [nonsecretdb]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nonsecretdb        |
| performance_schema |
| secretdb           |
| teguhth            |
+--------------------+
6 rows in set (0.000 sec)
 

.::: Test Result Backup from Database Encrypted & Restore Database Non Encrypted and otherwise MariaDB, MySQL :::.

A. Environment Encrypted<teguhth01> to Database Non Encrypted <teguhth02>

SELECT  NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID,@@hostname,@@version FROM information_schema.INNODB_TABLESPACES_ENCRYPTION;
 

SELECT  @@hostname,@@version;

SELECT  NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID,@@hostname,@@version FROM information_schema.INNODB_TABLESPACES_ENCRYPTION;
 

.::: Enable Encryption Table & Database for MariaDB or MySQL or MarinaDB :::.

 
A. Pre Configure
1. Create database

create database secretdb;
use secretdb;

MariaDB [teguhth]> create database secretdb;
Query OK, 1 row affected (0.000 sec)

MariaDB [teguhth]>

MariaDB [teguhth]> use secretdb;
Database changed
MariaDB [secretdb]>

.::: Change Server Name in Microsoft SQL Server MSSQL :::.

1. Check before
select @@servername as ServerName, @@version as VersionSQL;
exec sp_helpserver;

 

 

.::: Set Limit Max Server Memory in SQL Server :::.

1. Check Memory server 

 

2. Capture before
SELECT @@servername as ServerName,[name],[value], [value_in_use], description
FROM sys.configurations
WHERE [name] = 'max server memory (MB)' or
      [name] = 'min server memory (MB)' ;

.::: Upgrade SQL Server 2019 to MSSQL 2022 :::.

1. Backup Database
2. Capture version before upgrade
---Open SSMS & Run Command to check version (Capture)
select @@servername as ServerName, @@version as VersionSQL;

-- select IP
SELECT  @@servername as ServerName,
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address

.::: Backup Database Full & Transaction & Restore Database SQL Server :::.

correlation with https://teguhth.blogspot.com/2023/06/backup-database-full-transaction.html

 

A. check status backup and restore

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

.::: Change License Key SQL Server 2019 from Evaluation to Enterprice :::.

A. Check before update License

 ---Open SSMS & Run Command to check version (Capture)
select @@servername as ServerName, @@version as VersionSQL;

-- select IP
SELECT  @@servername as ServerName,
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address
   

.:: Check ServerName, SQL Server Version, IP, compatibility_level, log_reuse_wait_desc, recovery_model_desc , Active Transaction :::.

---Open SSMS & Run Command to check version (Capture)
select @@servername as ServerName, @@version as VersionSQL;

-- select IP
SELECT  @@servername as ServerName,
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address

.::: Repair Unable to launch SQL Server Configuration Manager – Invalid class [0x80041010] :::.

1. Get Error open SQL SQL Server 2019 Configuration Manager
 

Here is the text of the error message:

—————————
SQL Server Configuration Manager
—————————
Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid class [0x80041010]
—————————
OK 

.::: Check Hostname, IP & Version for MSSQL, MariaDB, MySQL, OracleDB, PostgreSQL from SQL Server LinkedServer :::.

1. MSSQL
select @@servername as ServerName,@@VERSION as VersionSQL from sys.databases;
select @@servername as ServerName,name,log_reuse_wait_desc, recovery_model_desc,compatibility_level,@@VERSION as VersionSQL from sys.databases
 

.::: Remove a node to existing MariaDB Galera Cluster :::.

 A. Temporary remove
1. check status existing galera status before remove. for 3 node
show status like 'wsrep_incoming_addresses';
show status like 'wsrep_cluster_weight';
show status like 'wsrep_cluster_size';
show status like 'wsrep_connected';

.::: Adding a node to existing MariaDB Galera Cluster :::.

1. additional hosts node at hosts <add node3= teguhth03>
[root@teguhth01 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.15 loadbalancer
10.10.10.11 teguhth01
10.10.10.12 teguhth02
10.10.10.13 teguhth03
[root@teguhth01 ~]#

.::: How to Shrink Log Database SQL Server :::.

 1.check active transcation

select @@servername as ServerName,name,log_reuse_wait_desc, recovery_model_desc,compatibility_level,@@VERSION  from sys.databases
 

.::: Check Running Query In SQL Server/ Check Query Database Berjalan :::.

1. query 1 - full not simple

SET NOCOUNT ON   
SET ANSI_PADDING ON
SET QUOTED_IDENTIFIER ON  
DECLARE @record_id int, @SQLProcessUtilization int, @CPU int,@EventTime datetime--,@MaxCPUAllowed int   
select  top 1  @record_id =record_id,
      @EventTime=dateadd(ms, -1 * ((SELECT ms_ticks from sys.dm_os_sys_info) - [timestamp]), GetDate()),-- as EventTime,
      @SQLProcessUtilization=SQLProcessUtilization,
      --SystemIdle,
      --100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization,
      @CPU=SQLProcessUtilization + (100 - SystemIdle - SQLProcessUtilization) --as CPU_Usage
from (

 

.::: Reindex, Reorganize and Rebuild Indexes & Update statistics in SQL Server base on Fragmentation :::.

1. Check Database_ID (contoh database id 5 & 6 )
select @@Servername as ServerName,name as dbname,database_id  from sys.databases;
select @@Servername as ServerName,name as dbname,database_id  from sys.databases where name = 'teguhth';
select @@Servername as ServerName,name as dbname,database_id  from sys.databases where name = 'AdventureWorks2019';

.::: Install & Configure High Availability MariaDB 10.5 Using New Galera Cluster :::.

 1. Install MariaDB latest
http://teguhth.blogspot.com/2023/05/how-to-install-mariadb-105-or-latest.html

enable /etc/hosts

2. check galera location
[root@teguhth01 ~]# yum install -y mlocate
[root@teguhth01 ~]# updatedb
[root@teguhth01 ~]# locate libgalera_smm.so
/usr/lib64/galera-4/libgalera_smm.so
[root@teguhth01 ~]#

::: How to Install MariaDB 10.5 or Latest using mariadb_repo (Enable Remote Root) :::.

1. download repository
yum wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
yum install wget -y
wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup

[root@teguhth ]# wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup

2. install repo
chmod +x mariadb_repo_setup
sudo ./mariadb_repo_setup

[root@teguhth yum.repos.d]# chmod +x mariadb_repo_setup
[root@teguhth yum.repos.d]# sudo ./mariadb_repo_setup
# [info] Checking for script prerequisites.
# [info] MariaDB Server version 10.11 is valid
# [info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo
# [info] Adding trusted package signing keys...
/etc/pki/rpm-gpg /etc/yum.repos.d
/etc/yum.repos.d

.::: How to Remove ENCRYPTION_KEY_ID from file backup MariaDB / MySQL :::.

1. find ENCRYPTION_KEY_ID in backup mariadb example 'teguhth_encrypt.sql'
cat teguhth_encrypt.sql  | grep -i  ENCRYPTION_KEY_ID

 

[root@teguhth-lab02 backup]# cat teguhth_encrypt.sql  | grep -i  ENCRYPTION_KEY_ID
) ENGINE=InnoDB DEFAULT CHARSET=utf8 `ENCRYPTED`=YES `ENCRYPTION_KEY_ID`=1234;
) ENGINE=InnoDB DEFAULT CHARSET=utf8 `ENCRYPTED`=YES `ENCRYPTION_KEY_ID`=1234;
[root@teguhth-lab02 backup]#