18Nov/091
Web based php script to check MySQL replication status
This is a basic php script to check replication status of single or multiple MySQL servers. You should change variables in the script to run, like user, password, ip of server etc.
Here is the script code:
<? ########## # # dbrepstatus.php by Jagbir Singh (jags@linuxing.org) # # if you want to discuss any feature or add improments or check updated version, pls visit: www.linuxing.org # # Release Log: # # v1.0 17-Nov-09: Initial release. # ######### ?> <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 were 4 servers. one is master and all other slaves so what I assumed here is that all servers have same user/pass to access information. you should change this script per your requirement before using. if you want to discuss about this or have some cool idea, pls post in the forum post dedicated for this script.