install php https://infotechys.com/install-php-8-3-on-rhel-9-centos-9/
1. create bash monitoring
[root@teguhth03 web]# cat /data/web/server31.sh
#!/bin/bash
# Variabel koneksi
USER="admin"
PASS="xxxxx"
HOST="10.10.10.31"
PORT="3306"
# Test koneksi ke MySQL server
mysql --skip-ssl -u$USER -p$PASS -h$HOST -P$PORT -e "SELECT 1;" >/dev/null 2>&1
if [ $? -ne 0 ]; then
echo "{ \"connections\": \"\", \"role\": \"Down\", \"gtid\": \"\",\"hostname\": \"$hostnamex\", \"host\": \"$HOST\", \"port\": \"$PORT\",\"dbv\": \"$versionx\",\"osv\": \"$osx\" }"
exit 0
fi
# Ambil jumlah koneksi
CONNECTIONS=$(mysql --skip-ssl -u$USER -p$PASS -h$HOST -P$PORT -N -e "SHOW STATUS LIKE 'Threads_connected';" | awk '{print $2}')
hostnamex=$(mysql --skip-ssl -u$USER -p$PASS -h$HOST -P$PORT -N -e "select @@hostname;")
versionx=$(mysql --skip-ssl -u$USER -p$PASS -h$HOST -P$PORT -N -e "select @@version;")
osx=$(grep -i pretty /etc/os-release | cut -d= -f2 | tr -d '"')
# Ambil file binlog & posisi
read start_binlog start_pos <<< $(mysql --skip-ssl -u$USER -p$PASS -h$HOST -P$PORT -N -e "SHOW MASTER STATUS;" | awk '{print $1, $2}')
# Ambil GTID Binlog
GTID=$(mysql --skip-ssl -u$USER -p$PASS -h$HOST -P$PORT -N -e "SELECT BINLOG_GTID_POS('$start_binlog', $start_pos);" 2>/dev/null)
# Ambil role (Master/Slave)
IS_SLAVE=$(mysql --skip-ssl -u$USER -p$PASS -h$HOST -P$PORT \
-e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -c "Slave_IO_State")
if [ "$IS_SLAVE" -eq 0 ]; then
ROLE="Master"
else
ROLE="Slave"
fi
# Cetak dalam format JSON
echo "{ \"connections\": \"$CONNECTIONS\", \"role\": \"$ROLE\", \"gtid\": \"$GTID\", \"hostname\": \"$hostnamex\", \"host\": \"$HOST\", \"port\": \"$PORT\", \"dbv\": \"$versionx\" , \"osv\": \"$osx\" }"
[root@teguhth03 web]#
2. create php mirror.php
[root@teguhth03 html]# pwd
/var/www/html
[root@teguhth03 html]# ls
info.php mirror.php server31.php server33.php server90.php servermirror.php
[root@teguhth03 html]#
[root@teguhth03 html]# cat /var/www/html/mirror.php
<?php
date_default_timezone_set("Asia/Jakarta"); // Set timezone ke GMT+7
// Jalankan shell script untuk ambil data monitoring
$servers = [
["name" => "db31", "script" => "sh /data/web/server31.sh"],
["name" => "db32", "script" => "sh /data/web/server32.sh"],
["name" => "db33", "script" => "sh /data/web/server33.sh"]
];
?>
<!DOCTYPE html>
<html>
<head>
<title>MariaDB Replication Monitor</title>
<meta http-equiv="refresh" content="10"> <!-- refresh otomatis setiap 10 detik -->
<style>
body { font-family: Arial; background: #f8f9fa; }
.card {
display:inline-block;
background:#333; color:white;
padding:20px; margin:10px;
border-radius:10px; width:250px;
text-align:center;
}
.status { font-size:14px; margin-top:10px; padding:5px; border-radius:5px; }
.master { background:green; }
.slave { background:blue; }
.down { background:red; }
</style>
</head>
<body>
<h2>MariaDB Replication Dashboard</h2>
<?php
foreach ($servers as $srv) {
$json = shell_exec($srv["script"]);
$data = json_decode($json, true);
echo "<div class='card'>";
echo "<h3>{$data['connections']}<br>Connections</h3>";
echo "<h4>{$srv['name']}</h4>";
if ($data['role'] == "Master") {
echo "<div class='status master'>Master Running</div>";
} elseif ($data['role'] == "Slave") {
echo "<div class='status slave'>Slave Running</div>";
} elseif ($data['role'] == "Down") {
echo "<div class='status down'>Server Down</div>";
}
echo "<pre style='text-align: left; font-family: monospace;'>
Hostname: {$data['hostname']}
IP : {$data['host']}
Port : {$data['port']}
DB : {$data['dbv']}
OS : {$data['osv']}
gtid : {$data['gtid']}
</pre>";
echo "</div>";
}
?>
<footer style="margin-top:20px; text-align:center; font-size:20px; color:#555;">
© <?php echo date("Y"); ?> Teguh Triharto | https://www.linkedin.com/in/teguhth | <?php echo date("H:i:s"); ?>
</footer>
</body>
</html>
[root@teguhth03 html]#
3. Result
3.1 Normal
3.2 DB Service Down
3.3 Server down
No comments:
Post a Comment