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