Monitor multiple mysql replication using php: Updated

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.

Download mysql monitor using php: mysql replication monitor php

12 comments
  1. Based upon your script, I have created a simple commandline utility that checks for properly running replication and re-synchronizes the slave to the master if there is an error.

    You can find it on GitHub:

    https://github.com/k1mgy/MySql-Replication-Utilities

  2. I can’t make typical replication Master-Slave (I dont have acces to external dbase conf files). How can I make a replication from the local mysql (winamp server) to outside mysql server only via (PHP?) script solution?

    1. 🙂 not possible if you don’t have access to one of the server considering not every thing is preconfigured.

      – Kedar.

  3. Hey Kedar,

    This is awesome. I was going to write my own until I found yours! Thanks this is so great!

    The slave status is working fine using the replmon_conf.txt file but the detailed link isn’t working for me. I’m getting a can’t connect to server. Should I just put the login creds directly into the get_status function?

    Thanks again!!!

      1. @Michael,

        Check updated mysql replication monitoring script at the end of the article. I hope this fixes our issue 🙂

  4. Great Script!

    do you know how I could include everything again in the same page to connect to another pair of servers?

Leave a Reply

Your email address will not be published. Required fields are marked *