php在linux下检测mysql同步状态的方法
本文实例讲述了php在linux下检测mysql同步状态的方法。分享给大家供大家参考。具体分析如下：
这里通过两个实例来介绍mysql同步状态检测实现方法。代码如下:
#!/bin/sh
#checkMySQL_SlaveStatus
#crontabtime00:10
MYSQL_USER="root"
MYSQL_PWD="123456"
MYSQL_SLAVE_LOG="/tmp/check_mysql_slave.log"
EMAIL="1351010****@139.com"
MYSQL_PORT=`netstat-na|grep"LISTEN"|grep"3306"|awk-F[:""]+'{print$5}'`
MYSQL_IP=`ifconfigeth0|grep"inetaddr"|awk-F[:""]+'{print$4}'`
MYSQL_SLAVE_STATUS=$(/usr/local/webserver/mysql/bin/mysql-uroot-psylc23hua-S/tmp/mysql.sock-e
"showslavestatusG"|grep-i"running")
IO_ENV=`echo$MYSQL_SLAVE_STATUS|grepIO|awk'{print$2}'`
SQL_ENV=`echo$MYSQL_SLAVE_STATUS|grepSQL|awk'{print$2}'`
NOW=$(date-dtoday+'%Y-%m-%d%H:%M:%S')
if["$MYSQL_PORT"="3306"];then
echo"mysqlisrunning!"
else
mail-s"warn!server:$MYSQL_IPmysqlisdown""$EMAIL"
fi
if["$IO_ENV"="Yes"-a"$SQL_ENV"="Yes"];then
echo"Slaveisrunning!"
else
echo"[$NOW]Slaveisnotrunning!">>"$MYSQL_SLAVE_LOG"
cat"$MYSQL_SLAVE_LOG"|mail-s"WARN!${MySQL_IP}_replicate_error""$EMAIL"
fi
exit0php实例代码,代码如下:
check_rep.php：
if(emptyempty($_REQUEST["key"]))die(':)missingkey');
if($_REQUEST["key"]!='xupeng')die(':)errorkey');
include("mysql_instance.php");
include("check_status_api.php");
define("USERNAME","用户名");
define("PASSWORD","密码");
define("DEBUGMODE",false);
$instances=get_instances();
if($instances){
echo<<
<!--30分钟自动刷新-->
END;
echo"
n";
if(!DEBUGMODE){
echo"
n";
}else{
echo"
n";
}
foreach($instancesas$host){
$res=check_mysql_replication_status($host,USERNAME,PASSWORD);
if(!DEBUGMODE){
switch($res["result"]){
case-4:
$memo="未知异常";
break;
case-3:
$memo="查询失败";
break;
case-2:
$memo="无法连接端口";
break;
case-1:
$memo="状态未知";
break;
case0:
$memo="OK";
break;
case1:
$memo="同步失败";
if($res["Slave_IO_Running"]<>"Yes"){
$memo.=$res["Last_IO_Error"]."(". $res
["Last_IO_Errno"].")";
}
if($res["Slave_SQL_Running"]<>"Yes"){
$memo.=$res["Last_SQL_Error"]."(". $res
["Last_SQL_Errno"].")";
}
break;
case2:
$memo="数据库未设置同步";
break;
}
echo"
n";
}else{
echo"
n";
}
}
echo"
<tableborder="">
<tbody>
<tr>
<td>instance</td>
<td>result</td>
<td>Slave_IO_Running</td>
<td>Slave_SQL_Running</td>
<td>Master_Host</td>
<td>Master_Port</td>
<td>Replicate_Do_DB</td>
<td>memo</td>
</tr>
<tr>
<td>instance</td>
<td>result</td>
<td>Slave_IO_Running</td>
<td>Slave_SQL_Running</td>
<td>Master_Host</td>
<td>Master_Port</td>
<td>Replicate_Do_DB</td>
<td>Slave_IO_State</td>
<td>Last_IO_Errno</td>
<td>Last_IO_Error</td>
<td>Last_SQL_Errno</td>
<td>Last_SQL_Error</td>
</tr>
<tr>
<td>{$host}</td>
<td>{$res['result']}</td>
<td>{$res['Slave_IO_Running']}</td>
<td>{$res['Slave_SQL_Running']}</td>
<td>{$res['Master_Host']}</td>
<td>{$res['Master_Port']}</td>
<td>{$res['Replicate_Do_DB']}</td>
<td>{$memo}</td>
</tr>
<tr>
<td>{$host}</td>
<td>{$res['result']}</td>
<td>{$res['Slave_IO_Running']}</td>
<td>{$res['Slave_SQL_Running']}</td>
<td>{$res['Master_Host']}</td>
<td>{$res['Master_Port']}</td>
<td>{$res['Replicate_Do_DB']}</td>
<td>{$res['Slave_IO_State']}</td>
<td>{$res['Last_IO_Errno']}</td>
<td>{$res['Last_IO_Error']}</td>
<td>{$res['Last_SQL_Errno']}</td>
<td>{$res['Last_SQL_Error']}</td>
</tr>
</tbody>
</table>
n";
echo<<
END;
}else{
die("nomysqlinstancesdefined.");
}check_status_api.php：
/*
*检查mysql服务器的同步状态
*/
functioncheck_mysql_replication_status($host,$username,$password)
{
//默认状态未知
$r=array(
"result"=>-1
);
try{
$dbh=@mysql_connect($host,$username,$password);
if(!$dbh){
//无法连接
$r["result"]=-2;
return($r);
}
$query="SHOWSLAVESTATUS";
$res=@mysql_query($query,$dbh);
$err=@mysql_error();
if($err){
//无法连接
$r["result"]=-3;
return($r);
}
$row=mysql_fetch_array($res);
$r=$row;
if(($r["Slave_IO_Running"]=="Yes")&&($r["Slave_SQL_Running"]=="Yes"))
{
$r["result"]=0;
}else{
if(!emptyempty($row)){
$r["result"]=1;
}else{
$r["result"]=2;
}
}
}catch(Exception$e){
$r["result"]=-4;
}
return($r);
}mysql_instance.php：
//GRANTREPLICATIONCLIENTON*.*TO'用户名'@'监控主机ip'IDENTIFIEDBY'密码'; $mysql_instances= array(); $mysql_instances[]="远程ip:端口";
functionget_instances() { global$mysql_instances; return$mysql_instances; }