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