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

[root@teguhth01 ~]# mysql -uroot -proot -e "SHOW MASTER STATUS;" | awk '{print $1}'
File
mysql-bin.000016
[root@teguhth01 ~]# mysql -uroot -proot -e "SHOW MASTER STATUS;" | awk '{print $2}'
Position
471
[root@teguhth01 ~]# mysql -uroot -proot -e "SHOW MASTER STATUS;" | awk '{print $3}'
Binlog_Do_DB

[root@teguhth01 ~]# mysql -uroot -proot -e "SHOW MASTER STATUS;" | awk '{print $4}'
Binlog_Ignore_DB

[root@teguhth01 ~]#
 

2. info from master

start_binlog = File > mysql-bin.000016
start_pos = Position > 471
gtid_binlog = SELECT BINLOG_GTID_POS('start_binlog', 'start_pos');

3. get gtid master

mysql -uroot -proot -e "SELECT @@hostname,BINLOG_GTID_POS('mysql-bin.000016','471')"

[root@teguhth01 ~]# mysql -uroot -proot -e "SELECT @@hostname,BINLOG_GTID_POS('mysql-bin.000016','471')"
+------------+-------------------------------------------+
| @@hostname | BINLOG_GTID_POS('mysql-bin.000016','471') |
+------------+-------------------------------------------+
| teguhth01  | 1-1-119                                   |
+------------+-------------------------------------------+
[root@teguhth01 ~]#
 
4. cek slave status G in slave

mysql -uroot -proot -e "show slave status\G"
mysql -uroot -proot -e "show slave status\G" | grep -i Relay_Master_Log_File
mysql -uroot -proot -e "show slave status\G" | grep -i Exec_Master_Log_Pos
mysql -uroot -proot -e "show slave status\G" | grep -i Gtid_IO_Pos


[root@teguhth02 ~]# mysql -uroot -proot -e " show slave status\G" | grep -i Relay_Master_Log_File
         Relay_Master_Log_File: mysql-bin.000016
[root@teguhth02 ~]# mysql -uroot -proot -e " show slave status\G" | grep -i Exec_Master_Log_Pos
           Exec_Master_Log_Pos: 471
[root@teguhth02 ~]#  mysql -uroot -proot -e " show slave status\G" | grep -i Gtid_IO_Pos
                   Gtid_IO_Pos: 1-1-119
[root@teguhth02 ~]#

5. conclusion

 

  

6. script get gtid

 

[root@teguhth01 data]# cat script_get_gtid.sh

## login to MariaDB
mariadbx=$(echo "mysql -uroot -pxxx -N");

## File binlogs
start_binlog=$(echo "SHOW MASTER STATUS;" | $mariadbx | awk '{print $1}');

## Position
start_pos=$(echo "SHOW MASTER STATUS;" | $mariadbx | awk '{print $2}');
gtid_binlog=$(echo "SELECT BINLOG_GTID_POS('$start_binlog', $start_pos);" | $mariadbx | awk '{print $1}');
logx=gtidlox.txt
echo "binlog: "$start_binlog > $logx
echo "position: "$start_pos >> $logx
echo "GTID :"$gtid_binlog >> $logx
cat $logx
[root@teguhth01 data]#
[root@teguhth01 data]#
 

7. Script gtid

 

[root@teguhth01 gtid]# cat gtid_get.sh
#!/bin/bash

USER="admin"
PASS="admin"
HOST="10.10.10.32"
PORT="3306"

# Ambil file binlog
start_binlog=$(mysql -u$USER -p$PASS -h$HOST -P$PORT -N -e "SHOW MASTER STATUS;" | awk '{print $1}')

# Ambil posisi binlog
start_pos=$(mysql -u$USER -p$PASS -h$HOST -P$PORT -N -e "SHOW MASTER STATUS;" | awk '{print $2}')

# Jalankan query BINLOG_GTID_POS
gtid_binlog=$(mysql -u$USER -p$PASS -h$HOST -P$PORT -N -e "SELECT BINLOG_GTID_POS('$start_binlog', $start_pos);")

# Tampilkan hasil
echo "GTID: $gtid_binlog"

[root@teguhth01 gtid]#
[root@teguhth01 gtid]# sh gtid_get.sh
GTID: 0-1-2178
[root@teguhth01 gtid]#