.::: 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 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: