#!/bin/bash
userx="admin"
passwdx="admin"
datex=$(date)
portx="3306"
ip1="10.10.10.31"
ip2="10.10.10.32"
ip3="10.10.10.33"
host1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@hostname")
dbversion1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@version;")
host1_short=$(echo "$host1" | cut -d'.' -f1) # hanya ambil sebelum titik
host2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@hostname")
dbversion2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@version;")
host2_short=$(echo "$host2" | cut -d'.' -f1) # hanya ambil sebelum titik
host3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@hostname")
dbversion3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@version;")
host3_short=$(echo "$host3" | cut -d'.' -f1) # hanya ambil sebelum titik
master1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -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:")
slave2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -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:")
slave3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -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:")
uptime1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT
TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE), '%Hh %im') AS Uptime
FROM information_schema.global_status
WHERE variable_name='Uptime';")
uptime2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT
TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE), '%Hh %im') AS Uptime
FROM information_schema.global_status
WHERE variable_name='Uptime';")
uptime3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT
TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE), '%Hh %im') AS Uptime
FROM information_schema.global_status
WHERE variable_name='Uptime';")
sqlstart1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT DATE_FORMAT(NOW() - INTERVAL VARIABLE_VALUE SECOND, '%Y-%m-%d %H:%i:%s') AS 'MariaDB Started'
FROM information_schema.global_status
WHERE variable_name='Uptime';
")
sqlstart2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT DATE_FORMAT(NOW() - INTERVAL VARIABLE_VALUE SECOND, '%Y-%m-%d %H:%i:%s') AS 'MariaDB Started'
FROM information_schema.global_status
WHERE variable_name='Uptime';
")
sqlstart3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT DATE_FORMAT(NOW() - INTERVAL VARIABLE_VALUE SECOND, '%Y-%m-%d %H:%i:%s') AS 'MariaDB Started'
FROM information_schema.global_status
WHERE variable_name='Uptime';
")
Threads_running1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_running';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
Threads_running2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_running';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
Threads_running3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_running';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
Threads_connected1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_connected';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
Threads_connected2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_connected';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
Threads_connected3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_connected';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
## for master
# Ambil file binlog
start_binlog=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SHOW MASTER STATUS;" | awk '{print $1}')
# Ambil posisi binlog
start_pos=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SHOW MASTER STATUS;" | awk '{print $2}')
# Jalankan query BINLOG_GTID_POS
gtid_binlog=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT BINLOG_GTID_POS('$start_binlog', $start_pos);")
### for slave
#gtid_pos2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E "Gtid_IO_Pos" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
gtid_pos2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E "Gtid_IO_Pos" | awk -F': ' '{print $2}')
#gtid_pos3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E "Gtid_IO_Pos" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
gtid_pos3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E "Gtid_IO_Pos" | awk -F': ' '{print $2}')
# Tampilkan hasil
echo ""
echo ".::: Monitoring GTID Mirroring at $datex :::."
echo ""
echo "Master -> $ip1 $host1_short $dbversion1 GTID: $gtid_binlog $Threads_running1 $Threads_connected1"
echo "Slave1 -> $ip2 $host2_short $dbversion2 GTID: $gtid_pos2 $Threads_running2 $Threads_connected2"
echo "Slave2 -> $ip3 $host3_short $dbversion3 GTID: $gtid_pos3 $Threads_running3 $Threads_connected3"
echo ""
echo "Master => $ip1 with start on '$sqlstart1' uptime '$uptime1'"
echo "$master1"
echo ""
echo "Slave1 => $ip2 with start on '$sqlstart2' uptime '$uptime2'"
echo "$slave2" | sed 's/^[[:space:]]*//' | column -t -s ':'
echo ""
echo "Slave2 => $ip3 with start on '$sqlstart3' uptime '$uptime3'"
echo "$slave3" | sed 's/^[[:space:]]*//' | column -t -s ':'
echo ""
echo "Copyright by : Teguh Triharto"
echo "Website : https://www.linkedin.com/in/teguhth"
echo ""
4. Query Colour
userx="admin"
passwdx="admin"
datex=$(date)
portx="3306"
ip1="10.10.10.31"
ip2="10.10.10.32"
ip3="10.10.10.33"
host1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@hostname")
dbversion1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@version;")
host1_short=$(echo "$host1" | cut -d'.' -f1) # hanya ambil sebelum titik
host2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@hostname")
dbversion2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@version;")
host2_short=$(echo "$host2" | cut -d'.' -f1) # hanya ambil sebelum titik
host3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@hostname")
dbversion3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@version;")
host3_short=$(echo "$host3" | cut -d'.' -f1) # hanya ambil sebelum titik
master1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -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:")
slave2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -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:")
slave3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -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:")
uptime1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT
TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE), '%Hh %im') AS Uptime
FROM information_schema.global_status
WHERE variable_name='Uptime';")
uptime2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT
TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE), '%Hh %im') AS Uptime
FROM information_schema.global_status
WHERE variable_name='Uptime';")
uptime3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT
TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE), '%Hh %im') AS Uptime
FROM information_schema.global_status
WHERE variable_name='Uptime';")
sqlstart1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT DATE_FORMAT(NOW() - INTERVAL VARIABLE_VALUE SECOND, '%Y-%m-%d %H:%i:%s') AS 'MariaDB Started'
FROM information_schema.global_status
WHERE variable_name='Uptime';
")
sqlstart2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT DATE_FORMAT(NOW() - INTERVAL VARIABLE_VALUE SECOND, '%Y-%m-%d %H:%i:%s') AS 'MariaDB Started'
FROM information_schema.global_status
WHERE variable_name='Uptime';
")
sqlstart3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT DATE_FORMAT(NOW() - INTERVAL VARIABLE_VALUE SECOND, '%Y-%m-%d %H:%i:%s') AS 'MariaDB Started'
FROM information_schema.global_status
WHERE variable_name='Uptime';
")
Threads_running1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_running';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
Threads_running2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_running';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
Threads_running3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_running';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
Threads_connected1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_connected';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
Threads_connected2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_connected';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
Threads_connected3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_connected';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
## for master
# Ambil file binlog
start_binlog=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SHOW MASTER STATUS;" | awk '{print $1}')
# Ambil posisi binlog
start_pos=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SHOW MASTER STATUS;" | awk '{print $2}')
# Jalankan query BINLOG_GTID_POS
gtid_binlog=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT BINLOG_GTID_POS('$start_binlog', $start_pos);")
### for slave
#gtid_pos2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E "Gtid_IO_Pos" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
gtid_pos2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E "Gtid_IO_Pos" | awk -F': ' '{print $2}')
#gtid_pos3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E "Gtid_IO_Pos" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
gtid_pos3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E "Gtid_IO_Pos" | awk -F': ' '{print $2}')
# Tampilkan hasil
echo ""
echo ".::: Monitoring GTID Mirroring at $datex :::."
echo ""
echo "Master -> $ip1 $host1_short $dbversion1 GTID: $gtid_binlog $Threads_running1 $Threads_connected1"
echo "Slave1 -> $ip2 $host2_short $dbversion2 GTID: $gtid_pos2 $Threads_running2 $Threads_connected2"
echo "Slave2 -> $ip3 $host3_short $dbversion3 GTID: $gtid_pos3 $Threads_running3 $Threads_connected3"
echo ""
echo "Master => $ip1 with start on '$sqlstart1' uptime '$uptime1'"
echo "$master1"
echo ""
echo "Slave1 => $ip2 with start on '$sqlstart2' uptime '$uptime2'"
echo "$slave2" | sed 's/^[[:space:]]*//' | column -t -s ':'
echo ""
echo "Slave2 => $ip3 with start on '$sqlstart3' uptime '$uptime3'"
echo "$slave3" | sed 's/^[[:space:]]*//' | column -t -s ':'
echo ""
echo "Copyright by : Teguh Triharto"
echo "Website : https://www.linkedin.com/in/teguhth"
echo ""
4. Query Colour
#!/bin/bash
userx="admin"
passwdx="admin"
datex=$(date)
portx="3306"
ip1="10.10.10.31"
ip2="10.10.10.32"
ip3="10.10.10.33"
host1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@hostname")
dbversion1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@version;")
host1_short=$(echo "$host1" | cut -d'.' -f1) # hanya ambil sebelum titik
host2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@hostname")
dbversion2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@version;")
host2_short=$(echo "$host2" | cut -d'.' -f1) # hanya ambil sebelum titik
host3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@hostname")
dbversion3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@version;")
host3_short=$(echo "$host3" | cut -d'.' -f1) # hanya ambil sebelum titik
master1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -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:")
slave2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -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:")
slave3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -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:")
uptime1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT
TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE), '%Hh %im') AS Uptime
FROM information_schema.global_status
WHERE variable_name='Uptime';")
uptime2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT
TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE), '%Hh %im') AS Uptime
FROM information_schema.global_status
WHERE variable_name='Uptime';")
uptime3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT
TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE), '%Hh %im') AS Uptime
FROM information_schema.global_status
WHERE variable_name='Uptime';")
sqlstart1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT DATE_FORMAT(NOW() - INTERVAL VARIABLE_VALUE SECOND, '%Y-%m-%d %H:%i:%s') AS 'MariaDB Started'
FROM information_schema.global_status
WHERE variable_name='Uptime';
")
sqlstart2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT DATE_FORMAT(NOW() - INTERVAL VARIABLE_VALUE SECOND, '%Y-%m-%d %H:%i:%s') AS 'MariaDB Started'
FROM information_schema.global_status
WHERE variable_name='Uptime';
")
sqlstart3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT DATE_FORMAT(NOW() - INTERVAL VARIABLE_VALUE SECOND, '%Y-%m-%d %H:%i:%s') AS 'MariaDB Started'
FROM information_schema.global_status
WHERE variable_name='Uptime';
")
Threads_running1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_running';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
Threads_running2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_running';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
Threads_running3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_running';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
Threads_connected1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_connected';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
Threads_connected2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_connected';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
Threads_connected3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_connected';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
# Warna ANSI
RED='\033[1;31m'
GREEN='\033[1;32m'
YELLOW='\033[1;33m'
CYAN='\033[1;36m'
BLUE='\033[1;34m'
MAGENTA='\033[1;35m'
WHITE='\033[1;37m'
RESET='\033[0m'
# Fungsi pewarnaan slave status
colorize_slave_status() {
local data="$1"
echo "$data" | while IFS= read -r line; do
key=$(echo "$line" | awk -F':' '{print $1}' | xargs)
value=$(echo "$line" | awk -F':' '{print $2}' | xargs)
# lebar kolom kiri (key)
width=25
case "$key" in
"Seconds_Behind_Master")
if [[ "$value" == "0" ]]; then
printf "${CYAN}%-${width}s${RESET}: ${GREEN}%s${RESET}\n" "$key" "$value"
else
printf "${CYAN}%-${width}s${RESET}: ${RED}%s${RESET}\n" "$key" "$value"
fi
;;
"Slave_IO_State"|"Master_Host"|"Master_User"|"Master_Port"|"Relay_Master_Log_File"|"Exec_Master_Log_Pos"|"Master_Server_Id")
printf "${CYAN}%-${width}s${RESET}: ${WHITE}%s${RESET}\n" "$key" "$value"
;;
"Using_Gtid"|"Gtid_IO_Pos")
printf "${CYAN}%-${width}s${RESET}: ${YELLOW}%s${RESET}\n" "$key" "$value"
;;
"Slave_SQL_Running_State")
printf "${CYAN}%-${width}s${RESET}: ${MAGENTA}%s${RESET}\n" "$key" "$value"
;;
*)
printf "${CYAN}%-${width}s${RESET}: ${WHITE}%s${RESET}\n" "$key" "$value"
;;
esac
done
}
## for master
# Ambil file binlog
start_binlog=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SHOW MASTER STATUS;" | awk '{print $1}')
# Ambil posisi binlog
start_pos=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SHOW MASTER STATUS;" | awk '{print $2}')
# Jalankan query BINLOG_GTID_POS
gtid_binlog=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT BINLOG_GTID_POS('$start_binlog', $start_pos);")
### for slave
#gtid_pos2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E "Gtid_IO_Pos" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
gtid_pos2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E "Gtid_IO_Pos" | awk -F': ' '{print $2}')
#gtid_pos3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E "Gtid_IO_Pos" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
gtid_pos3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E "Gtid_IO_Pos" | awk -F': ' '{print $2}')
echo ""
echo -e "${CYAN}.::: Monitoring GTID Mirroring at ${YELLOW}$datex${CYAN} :::.${RESET}"
echo ""
echo -e "${GREEN}Master ->${RESET} ${WHITE}$ip1${RESET} ${MAGENTA}$host1_short${RESET} ${YELLOW}$dbversion1${RESET} ${BLUE}GTID:${RESET} ${CYAN}$gtid_binlog${RESET} ${GREEN}$Threads_running1${RESET} ${RED}$Threads_connected1${RESET}"
echo -e "${GREEN}Slave1 ->${RESET} ${WHITE}$ip2${RESET} ${MAGENTA}$host2_short${RESET} ${YELLOW}$dbversion2${RESET} ${BLUE}GTID:${RESET} ${CYAN}$gtid_pos2${RESET} ${GREEN}$Threads_running2${RESET} ${RED}$Threads_connected2${RESET}"
echo -e "${GREEN}Slave2 ->${RESET} ${WHITE}$ip3${RESET} ${MAGENTA}$host3_short${RESET} ${YELLOW}$dbversion3${RESET} ${BLUE}GTID:${RESET} ${CYAN}$gtid_pos3${RESET} ${GREEN}$Threads_running3${RESET} ${RED}$Threads_connected3${RESET}"
echo ""
echo -e "${BLUE}Master =>${RESET} ${WHITE}$ip1${RESET} ${CYAN}with start on${RESET} '${YELLOW}$sqlstart1${RESET}' ${CYAN}uptime${RESET} '${GREEN}$uptime1${RESET}'"
colorize_slave_status "$master1"
echo ""
echo -e "${BLUE}Slave1 =>${RESET} ${WHITE}$ip2${RESET} ${CYAN}with start on${RESET} '${YELLOW}$sqlstart2${RESET}' ${CYAN}uptime${RESET} '${GREEN}$uptime2${RESET}'"
colorize_slave_status "$slave2"
echo ""
echo -e "${BLUE}Slave2 =>${RESET} ${WHITE}$ip3${RESET} ${CYAN}with start on${RESET} '${YELLOW}$sqlstart3${RESET}' ${CYAN}uptime${RESET} '${GREEN}$uptime3${RESET}'"
colorize_slave_status "$slave3"
echo ""
echo -e "${MAGENTA}Copyright by :${RESET} ${YELLOW}Teguh Triharto${RESET}"
echo -e "${MAGENTA}Website :${RESET} ${CYAN}https://www.linkedin.com/in/teguhth${RESET}"
echo ""
userx="admin"
passwdx="admin"
datex=$(date)
portx="3306"
ip1="10.10.10.31"
ip2="10.10.10.32"
ip3="10.10.10.33"
host1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@hostname")
dbversion1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@version;")
host1_short=$(echo "$host1" | cut -d'.' -f1) # hanya ambil sebelum titik
host2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@hostname")
dbversion2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@version;")
host2_short=$(echo "$host2" | cut -d'.' -f1) # hanya ambil sebelum titik
host3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@hostname")
dbversion3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT @@version;")
host3_short=$(echo "$host3" | cut -d'.' -f1) # hanya ambil sebelum titik
master1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -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:")
slave2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -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:")
slave3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -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:")
uptime1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT
TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE), '%Hh %im') AS Uptime
FROM information_schema.global_status
WHERE variable_name='Uptime';")
uptime2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT
TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE), '%Hh %im') AS Uptime
FROM information_schema.global_status
WHERE variable_name='Uptime';")
uptime3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT
TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE), '%Hh %im') AS Uptime
FROM information_schema.global_status
WHERE variable_name='Uptime';")
sqlstart1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT DATE_FORMAT(NOW() - INTERVAL VARIABLE_VALUE SECOND, '%Y-%m-%d %H:%i:%s') AS 'MariaDB Started'
FROM information_schema.global_status
WHERE variable_name='Uptime';
")
sqlstart2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT DATE_FORMAT(NOW() - INTERVAL VARIABLE_VALUE SECOND, '%Y-%m-%d %H:%i:%s') AS 'MariaDB Started'
FROM information_schema.global_status
WHERE variable_name='Uptime';
")
sqlstart3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "
SELECT DATE_FORMAT(NOW() - INTERVAL VARIABLE_VALUE SECOND, '%Y-%m-%d %H:%i:%s') AS 'MariaDB Started'
FROM information_schema.global_status
WHERE variable_name='Uptime';
")
Threads_running1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_running';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
Threads_running2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_running';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
Threads_running3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_running';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
Threads_connected1=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_connected';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
Threads_connected2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_connected';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
Threads_connected3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -sNe "show status where variable_name like 'Threads_connected';" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
# Warna ANSI
RED='\033[1;31m'
GREEN='\033[1;32m'
YELLOW='\033[1;33m'
CYAN='\033[1;36m'
BLUE='\033[1;34m'
MAGENTA='\033[1;35m'
WHITE='\033[1;37m'
RESET='\033[0m'
# Fungsi pewarnaan slave status
colorize_slave_status() {
local data="$1"
echo "$data" | while IFS= read -r line; do
key=$(echo "$line" | awk -F':' '{print $1}' | xargs)
value=$(echo "$line" | awk -F':' '{print $2}' | xargs)
# lebar kolom kiri (key)
width=25
case "$key" in
"Seconds_Behind_Master")
if [[ "$value" == "0" ]]; then
printf "${CYAN}%-${width}s${RESET}: ${GREEN}%s${RESET}\n" "$key" "$value"
else
printf "${CYAN}%-${width}s${RESET}: ${RED}%s${RESET}\n" "$key" "$value"
fi
;;
"Slave_IO_State"|"Master_Host"|"Master_User"|"Master_Port"|"Relay_Master_Log_File"|"Exec_Master_Log_Pos"|"Master_Server_Id")
printf "${CYAN}%-${width}s${RESET}: ${WHITE}%s${RESET}\n" "$key" "$value"
;;
"Using_Gtid"|"Gtid_IO_Pos")
printf "${CYAN}%-${width}s${RESET}: ${YELLOW}%s${RESET}\n" "$key" "$value"
;;
"Slave_SQL_Running_State")
printf "${CYAN}%-${width}s${RESET}: ${MAGENTA}%s${RESET}\n" "$key" "$value"
;;
*)
printf "${CYAN}%-${width}s${RESET}: ${WHITE}%s${RESET}\n" "$key" "$value"
;;
esac
done
}
## for master
# Ambil file binlog
start_binlog=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SHOW MASTER STATUS;" | awk '{print $1}')
# Ambil posisi binlog
start_pos=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SHOW MASTER STATUS;" | awk '{print $2}')
# Jalankan query BINLOG_GTID_POS
gtid_binlog=$(mysql -h"$ip1" -u"$userx" -p"$passwdx" -P"$portx" -sNe "SELECT BINLOG_GTID_POS('$start_binlog', $start_pos);")
### for slave
#gtid_pos2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E "Gtid_IO_Pos" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
gtid_pos2=$(mysql -h"$ip2" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E "Gtid_IO_Pos" | awk -F': ' '{print $2}')
#gtid_pos3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E "Gtid_IO_Pos" | sed 's/^[[:space:]]*//;s/[[:space:]]\+/ /g')
gtid_pos3=$(mysql -h"$ip3" -u"$userx" -p"$passwdx" -P"$portx" -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E "Gtid_IO_Pos" | awk -F': ' '{print $2}')
echo ""
echo -e "${CYAN}.::: Monitoring GTID Mirroring at ${YELLOW}$datex${CYAN} :::.${RESET}"
echo ""
echo -e "${GREEN}Master ->${RESET} ${WHITE}$ip1${RESET} ${MAGENTA}$host1_short${RESET} ${YELLOW}$dbversion1${RESET} ${BLUE}GTID:${RESET} ${CYAN}$gtid_binlog${RESET} ${GREEN}$Threads_running1${RESET} ${RED}$Threads_connected1${RESET}"
echo -e "${GREEN}Slave1 ->${RESET} ${WHITE}$ip2${RESET} ${MAGENTA}$host2_short${RESET} ${YELLOW}$dbversion2${RESET} ${BLUE}GTID:${RESET} ${CYAN}$gtid_pos2${RESET} ${GREEN}$Threads_running2${RESET} ${RED}$Threads_connected2${RESET}"
echo -e "${GREEN}Slave2 ->${RESET} ${WHITE}$ip3${RESET} ${MAGENTA}$host3_short${RESET} ${YELLOW}$dbversion3${RESET} ${BLUE}GTID:${RESET} ${CYAN}$gtid_pos3${RESET} ${GREEN}$Threads_running3${RESET} ${RED}$Threads_connected3${RESET}"
echo ""
echo -e "${BLUE}Master =>${RESET} ${WHITE}$ip1${RESET} ${CYAN}with start on${RESET} '${YELLOW}$sqlstart1${RESET}' ${CYAN}uptime${RESET} '${GREEN}$uptime1${RESET}'"
colorize_slave_status "$master1"
echo ""
echo -e "${BLUE}Slave1 =>${RESET} ${WHITE}$ip2${RESET} ${CYAN}with start on${RESET} '${YELLOW}$sqlstart2${RESET}' ${CYAN}uptime${RESET} '${GREEN}$uptime2${RESET}'"
colorize_slave_status "$slave2"
echo ""
echo -e "${BLUE}Slave2 =>${RESET} ${WHITE}$ip3${RESET} ${CYAN}with start on${RESET} '${YELLOW}$sqlstart3${RESET}' ${CYAN}uptime${RESET} '${GREEN}$uptime3${RESET}'"
colorize_slave_status "$slave3"
echo ""
echo -e "${MAGENTA}Copyright by :${RESET} ${YELLOW}Teguh Triharto${RESET}"
echo -e "${MAGENTA}Website :${RESET} ${CYAN}https://www.linkedin.com/in/teguhth${RESET}"
echo ""
No comments:
Post a Comment