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> $host : $slaveport </td>

<td> $position </td>

<td> $errorNum </td>

<td> $errorMeg </td>

</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>

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> position </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

Siddharth says:

March 31, 2016 at 1:07 pm

Hi Kedar,

Emails were not sent, through script. Please help.

Mark Richards says:

March 27, 2012 at 10:14 pm

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

1. Kedar says:
  
  May 16, 2012 at 4:25 pm
  
  Thanks Mark for visiting and sharing.
  
  – Kedar.
  
Andrew says:

February 8, 2012 at 10:43 am

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. Kedar says:
  
  May 16, 2012 at 4:26 pm
  
  🙂 not possible if you don’t have access to one of the server considering not every thing is preconfigured.
  
  – Kedar.
  
Galen says:

January 20, 2011 at 4:18 pm

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 says:
  
  October 5, 2011 at 9:01 pm
  
  Hi, same problem with the detailed link. Any solution?
  
  1. Kedar says:
    
    October 6, 2011 at 6:21 am
    
    @Michael,
    
    Check updated mysql replication monitoring script at the end of the article. I hope this fixes our issue 🙂
    
    1. Michael says:
      
      October 6, 2011 at 1:56 pm
      
      Thanks Kedar. Works perfectly.
      
2. Kedar says:
  
  October 6, 2011 at 6:20 am
  
  Galen,
  
  Took too long but seems like I’ve modified the script! let me know if you LIKE it 🙂
  
Kedar says:

December 7, 2010 at 6:57 am

Hey James,
Check the update! I hope it will work for you.

James says:

December 6, 2010 at 4:48 pm

Great Script!

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

Monitor multiple mysql replication using php: Updated

Download mysql monitor using php: mysql replication monitor php

Kedar

Related Tags

12 comments

Leave a Reply Cancel reply