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