This is a basic PHP script to check replication status of single or multiple MySQL servers. You have to update variables in the script to run in browser like user, password, ip of server etc.
Here is the script code:
<?
##########
#
# dbrepstatus.php by Jagbir Singh (jags@jagbir.com)
#
#
#########
?>
<html><head>
<title> Online DB Server status </title>
<body>
<form name="ShowStatus" action="<? echo $_SERVER['PHP_SELF']; ?>" method="POST">
<br/>
<table border=1 align=center>
<tr align=center bgcolor=gray><td colspan=2><b> Online DB Server Status </b></td> </tr>
<!--- TODO: replace your server name in the select list below !>
<tr ><td align=centre> Select Server: <select name="server">
<option value="s1" <? if ($_POST[server] == "s1") echo "selected"; ?> > Server 1
<option value="s2" <? if ($_POST[server] == "s2") echo "selected"; ?> > Server 2
<option value="s3" <? if ($_POST[server] == "s3") echo "selected"; ?> > Server 3
<option value="s4" <? if ($_POST[server] == "s4") echo "selected"; ?> > Server 4
</select> </td>
<td align=centre> <input type=submit name='show' value='Show Status'></td></tr>
</form>
</table>
<?
$serverUp=1; ## if any of your server not configured, set this variable to 0 below
$serverString="First Server"; ## server name
$serverIP="w.x.y.z"; ## default IP, anyhow will be changed later
$dbuser="demouser";
$dbpassword="demopass";
$imgShow="<img src='led-green.gif' />";
if ($_SERVER['REQUEST_METHOD'] == 'POST'){
switch($_POST[server]){
case "s1":
$serverString="Server 1";
$serverIP="IP1";
break;
case "s2":
$serverString="Server 2";
$serverIP="IP2";
break;
case "s3":
$serverString="Server 3";
$serverIP="IP3";
break;
case "s4":
$serverString="Server 4";
$serverIP="IP4";
$serverUp=0;
break;
}
?>
<br/>
<table border=1 align=center width=50%>
<tr align=center bgcolor=gray><td colspan=4><b> Status of <? echo $serverString." : ". $serverIP; ?> </b></td> </tr>
<tr > <th bgcolor=lightgray> Status </th> <th bgcolor=lightgray > Replication </th> <th bgcolor=lightgray> Delay </th> <th bgcolor=lightgray> Error </th> </tr>
<?
if (!$serverUp) {
?>
<tr> <td align=center colspan=4> This server is not configured yet! </td> </tr>
<?
}
else {
$contactAdmin="<a href='mailto:dbadmin@yourmail.com?subject=Replication issue in $serverString'> Contact Admin </a>";
$RepStatus = `mysql -h $serverIP -u $dbuser -p$dbpassword -e "show slave status\G;" | egrep "Slave_IO_Running: | Slave_SQL_Running: | Seconds_Behind_Master:" | cut -d':' -f2`; $RepStatus= trim($RepStatus);
list($RepRun, $SqlRun, $RepDelay) = split(" ",$RepStatus);
$RepRun = trim($RepRun);
$SqlRun = trim($SqlRun);
$RepDelay = trim($RepDelay);
// echo "<br/> Reprun=$RepRun, IO Run =$SqlRun, Replication delay =$RepDelay";
if($RepRun == "Yes") { $RepRun = "OK"; $contactAdmin=""; } else { $RepRun = "Replication (IO) Not Running!"; $imgShow="<img src='led-red.gif' />"; }
if($SqlRun == "Yes") { $SqlRun = "None"; $contactAdmin=""; } else {$SqlRun = "SQL thread stopped! "; $imgShow="<img src='led-red.gif' />";}
if($RepDelay == 0 ) { $RepDelay = "None"; } else { $RepDelay .= " seconds."; $contactAdmin=""; }
?>
<tr> <td align=center> <? echo $imgShow; ?> </td> <td align=center> <? echo $RepRun; ?> </td> <td align=center> <? echo $RepDelay; ?> </td> <td align=center> <? echo $SqlRun; ?> </td> </tr>
<?
if ($contactAdmin != "") { echo "<tr> <td align=center colspan=4> $contactAdmin </td></tr>"; }
}
?>
</table>
<?
}
?>
</html>
</body>In my case, there are 4 servers. First is master and all other slaves so what I assumed here is that all servers have same user/pass to access information. Don’t forget to change user/pass/ip etc. before trying to run this script in browser. Let me know through comments if you any suggestion/enhancement/issue regarding this.
Permanent link to this post (96 words, estimated 23 secs reading time)
