Tag Archives: php

04 Dec

MySQL master master replication monitor with php code

For monitoring replication we know a lot of tools and codes – but this one is different because I wrote it 😉
Well this is fairly simple php code for monitoring a master master replication setup.

It requires a single shared login id available on both MySQL servers.

It will display in tabular format following details:

  • File: Present binary log
  • Position: Binary log position
  • Io run: Slave IO Thread Running status
  • Sql run: SQL Thread Running status
  • ErrorNum: Error number
  • ErrorMeg: Error message

You can easily stop / start each server’s slave (STOP SLAVE / START SLAVE) with a single click.
It also does provide detailed information of global status variables on same page.
It is auto refreshes by default every 2 seconds to provide updated status of both MySQL Servers in Replication.

In code you just need to adjust following parameters as per requirement:

#host-name & ports for replication servers
$slave = “localhost”;
$master = “localhost”;
$slaveport=3306;
$masterport=3307;


#Refresh rate
$refreshRate=2;


#considered a common username password for accessing both servers#
$username=”root”;
$password=”kedar”;

You can download code here: mysql-replication-monitor.php

MySQL Replication Monitor Using PHP:

mysql-replication-monitor-php

11 Nov

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

30 Jan

Upload Image to MySQL using PHP

Upload Image to MySQL using PHP

As a new-bie to php/mysql, I tried different stuffs. So here I’m with my php code for Image Upload to MySQL. Its a quite simple code with two php files one to display and one to upload.
For Image Upload code, I’ve added code download link upload-image-mysql-demo.zip at the end of the page.

As a new-bie to php/mysql, I tried different stuffs. So here I’m with my php code for Image Upload to MySQL. Its a quite simple code with two php files one to display and one to upload.

For Image Upload code, I’ve added code download link upload-image-mysql-demo.zip at the end of the page.

CREATE TABLE `pix` (

`pic_id` int(11) NOT NULL auto_increment,

`pic_name` varchar(100) NOT NULL,

`pic_data` longblob NOT NULL,

PRIMARY KEY  (`pic_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Get 2 php files:- Image.php, showImage.php.

Set Connection parameters accordingly.

/* Image.php*/

<?php

$con = mysql_connect(“127.0.0.1:3306″,”root”,””);

if (!$con)

{

die(“Could not connect: ” . mysql_error());

}

$DB = mysql_select_db(“test”, $con);

move_uploaded_file($_FILES[“uploadedfile”][“tmp_name”],”latest.img”);

$instr = fopen(“latest.img”,”rb”);

$image = addslashes(fread(fopen(“latest.img”,”r”),filesize(“latest.img”)));

mysql_query (“insert into pix (pic_name, pic_data) values (“myImage”, “‘.$image.'”);”);

?>

<html>

<form enctype=”multipart/form-data” method=”POST”>

<img src=showImage.php?gim=1 width=500 height=150 alt=”hell”>

<br><hr>

<input type=”hidden” name=”MAX_FILE_SIZE” value=”100000″ />

Choose a file to upload(<100 KB): <input name=”uploadedfile” type=”file” /><br/>

<input type=”submit” value=”submit” name=”submit” />

</form>

<html>

/*showImage.php*/

<?php

$con = mysql_connect(“127.0.0.1:3306″,”root”,””);

if (!$con)

{

die(“Could not connect: ” . mysql_error());

}

$DB = mysql_select_db(“test”, $con);

$res = @mysql_query(“select * from pix order by pic_id desc limit 1”);

if ($row = @mysql_fetch_assoc($res))

{

$title = htmlspecialchars($row[pic_name]);

$bytes = $row[pic_data];

}

header(“Content-type: image/jpg”);

print $bytes;

mysql_close();

?>

Click here to download zip file for above demo of Uploading image to mysql using php.

-- Kedar Vaijanapurkar --