这篇文章主要介绍了php在linux下检测mysql同步状态的方法,是Linux下使用php检测mysql同步状态的实用技巧,具有一定参考借鉴价值,需要的朋友可以参考下

本文实例讲述了php在linux下检测mysql同步状态的方法。分享给大家供大家参考。具体分析如下：

这里通过两个实例来介绍mysql同步状态检测实现方法。代码如下:

#!/bin/sh  
   
#check MySQL_Slave Status  
#crontab time 00: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=`ifconfig eth0|grep "inet addr" | awk -F[:" "]+ '{print $4}'`  
MYSQL_SLAVE_STATUS=$(/usr/local/webserver/mysql/bin/mysql -u root -psylc23hua -S /tmp/mysql.sock -e 
 
"show slave statusG" | grep -i "running")  
IO_ENV=`echo $MYSQL_SLAVE_STATUS | grep IO | awk ' {print $2}'`  
SQL_ENV=`echo $MYSQL_SLAVE_STATUS | grep SQL | awk '{print $2}'`  
NOW=$(date -d today +'%Y-%m-%d %H:%M:%S')  
   
if [ "$MYSQL_PORT" = "3306" ];then 
  echo "mysql is running!" 
else 
  mail -s "warn!server: $MYSQL_IP mysql is down" "$EMAIL" 
fi 
   
if [ "$IO_ENV" = "Yes" -a "$SQL_ENV" = "Yes" ];then 
  echo "Slave is running!" 
else 
  echo "[ $NOW ] Slave is not running!" >> "$MYSQL_SLAVE_LOG" 
  cat "$MYSQL_SLAVE_LOG" | mail -s "WARN! ${MySQL_IP}_replicate_error" "$EMAIL" 
fi 
   
exit 0

php实例代码,代码如下:
check_rep.php：

if(emptyempty($_REQUEST["key"])) die(':) missing key'); 
if($_REQUEST["key"] != 'xupeng') die(':) error key'); 
 
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($instances as $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; 
    case 0: 
     $memo = "OK"; 
     break; 
    case 1: 
     $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; 
    case 2: 
     $memo = "数据库未设置同步"; 
     break; 
   } 
   echo " 
 
n"; 
  }else{ 
   echo " 
 
n"; 
  } 
 } 
 echo " 
<table border=""> 
<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("no mysql instances defined."); 
}

check_status_api.php：

/* 
 * 检查mysql服务器的同步状态 
 */ 
function check_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 = "SHOW SLAVE STATUS"; 
  $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：

//GRANT REPLICATION CLIENT ON *.* TO '用户名'@'监控主机ip' IDENTIFIED BY '密码';  $mysql_instances = 
 
array();  
$mysql_instances[] = "远程ip:端口"; 

function get_instances()  
{    
global $mysql_instances;    
return $mysql_instances;   
}

将以上三个PHP文件放在虚拟目录中,然后通过URL访问.

访问方式：http://ip/check_repl.php?key=xupeng

希望本文所述对大家的php程序设计有所帮助。