.::: 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, get gtid, 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