.::: 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,Lock, Locking 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 Tablem size Index :::.

 

 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;