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. )

#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 “

$time

$host : $slaveport

$file

$position

$sql_run

$io_run

$errorNum

$errorMeg

detailed

”;

}

#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 “

Reset”;

if($task==’no’) exit;

$con=mysql_connect($machine,$username,$password) or die(“can’t connect server”);

$sql=”show global status”;

echo “

”;

$res=mysql_query($sql);

while($row = mysql_fetch_assoc($res))

{

echo ‘

’;

}

echo “

Variable	Value
’.$row[‘Variable_name’]. ‘	’.$row[‘Value’].'

”;

#$_POST = array();

$task=”no”;

}

echo ‘’;

if(!isset($_GET[‘detail’]))

{

#Auto refreshing page for continuous status

#content=”3;” page refreshes after 3 seconds

echo ‘’;

}

echo'

MySQL Replication Monitor’;

#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 “

”;

slavestatus($slave,$username,$password,$slaveport);

slavestatus($master,$username,$password,$masterport);

print “

time	host : port	file	position	io run	sql run	errorNum	errorMeg	Stop / Start	Extra

”;

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 “

Showing global status for: “.

$_GET[‘detail’] . “
”;

get_status($_GET[‘detail’],$username,$password);

}

echo ‘