Monitoring a replication is an important aspect. As replication includes multiple nodes, it is essential to track activity and status across all mysql servers involved in replication.
To monitor replication we know commands like:
Show slave status;
Show master status;
Refer: http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-replication.html
But when it comes to non-gui interface, it becomes little tedious.
With some efforts, I managed to make a php script to monitor mysql servers.
It can:
- Get per second status of slaves.
- Start / Stop slave from any of the mysql servers in replication.
- Display error number along with description in case of error in replication.
- Shows detailed information of both the server’s global status variables.
Well there is no fancy UI, my bad, but it works for me. I’ll try to make it better, may be you can suggest.
Code:
[ Download here: monitor-replication.txt] ( ** Check Updated Monitoring Script. )
<?php
#Resets warning messages
error_reporting(E_ALL ^ E_NOTICE);
#Function to display slave status.
function slavestatus($host,$username,$password,$slaveport=3306)
{
$con=mysql_connect(“$host:$slaveport”,$username,$password)
or die(“can’t connect server”);
$result=mysql_query(“show slave status”);
$timeResult=mysql_query(“select now()”);
$time=mysql_result($timeResult,0,0);
while($status = mysql_fetch_array($result))
{
$file=$status[5];
$position=$status[6];
$sql_run=$status[10];
$io_run=$status[11];
$errorNum=$status[18];
$errorMeg=$status[19];
}
print “<tr align=center>
<td> $time </td>
<td> $host : $slaveport </td>
<td> $file </td>
<td> $position </td>
<td> $sql_run </td>
<td> $io_run </td>
<td> $errorNum </td>
<td> $errorMeg </td>
<td> <form name=’form’ action=’#’ method=’POST’>
<input type=’submit’ name=’stop’ id=’stop’ value=\”STOP $host:$slaveport\”>
<input type=’submit’ name=’start’ id=’start’ value=\”START $host:$slaveport\”>
</form> </td>
<td> <a href=’?detail=$host:$slaveport’> detailed </a></td>
</tr>”;
}
#Function for starting and stopping mysql server.
function start_stop($machine,$username,$password,$task=’no’)
{
if($task==’no’) exit;
$con=mysql_connect($machine,$username,$password) or die(“can’t connect server”);
$sql= $task . ” slave”;
$result=mysql_query($sql);
$_POST = array();
$task=”no”;
$VAR=$_SERVER[“REQUEST_URI”];
#Refreshes page, updates server status in table.
header(‘refresh: 1; url=’.$VAR);
}
#Function for showing detailed status
function get_status($machine,$username,$password)
{
$hostname = $_SERVER[‘SCRIPT_NAME’];
echo “<center><a href=\”$hostname\”>Reset</a></center>”;
if($task==’no’) exit;
$con=mysql_connect($machine,$username,$password) or die(“can’t connect server”);
$sql=”show global status”;
echo “<table border=1 align=center><tr><th>Variable</th><th>Value</th></tr>”;
$res=mysql_query($sql);
while($row = mysql_fetch_assoc($res))
{
echo ‘<tr align=center><td>’.$row[‘Variable_name’].
‘</td><td>’.$row[‘Value’].'</td></tr>’;
}
echo “</table>”;
#$_POST = array();
$task=”no”;
}
echo ‘<html>’;
if(!isset($_GET[‘detail’]))
{
#Auto refreshing page for continuous status
#content=”3;” page refreshes after 3 seconds
echo ‘<meta http-equiv=”refresh” content=”3;”>’;
}
echo'<body>
<h1><font face=”verdana” size=10><small>
MySQL Replication Monitor</small></font></h1>’;
#host-name & ports for replication servers
$slave = “localhost”;
$master = “localhost”;
$slaveport=3306;
$masterport=3307;
#considered a common username password for accessing both servers
$username=”root”;
$password=”kedar”;
print “<table border=1><tr>
<th> time </th>
<th> host : port </th>
<th> file </th>
<th> position </th>
<th> io run </th>
<th> sql run </th>
<th> errorNum </th>
<th> errorMeg </th>
<th> Stop / Start </th>
<th> Extra </th>
</tr>”;
slavestatus($slave,$username,$password,$slaveport);
slavestatus($master,$username,$password,$masterport);
print “</table>”;
if (isset($_POST[‘start’])) {
list($task,$machine)=split(” “,$_POST[‘start’]);
start_stop($machine,$username,$password,$task);
}
if (isset($_POST[‘stop’])) {
list($task,$machine)=split(” “,$_POST[‘stop’]);
start_stop($machine,$username,$password,$task);
}
if (isset($_GET[‘detail’])) {
echo “<br><center><b>Showing global status for: “.
$_GET[‘detail’] . “</b></center><br>”;
get_status($_GET[‘detail’],$username,$password);
}
echo ‘<div align=right><font face=”verdana” color=”gray” size=”2px”>
Kedar.</div></div></body>
</html>’;
?>
Update: Above script with little changes for monitoring more mysql servers using php script with easiest possible configuration.
Download: MySQL-replication-monitoring-PHP-Script. ( ** Check Updated Monitoring Script. )
The script give reads server configurations from a config file ‘replmon_conf.txt’ and thus facilitate to monitor simultaneously any number of mysql database servers.
You have to specify connection parameters in replmon_conf.txt as follows:
hostname,username,password,port
To monitor more than one mysql database servers you just need to add one more line of connection parameters.
Hope this helps.
Update 2: Tookout some time and modified the script for monitoring mysql replication using php.