欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

php在linux下检测mysql同步状态的方法

程序员文章站 2022-04-16 08:27:57
本文实例讲述了php在linux下检测mysql同步状态的方法。分享给大家供大家参考。具体分析如下: 这里通过两个实例来介绍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程序设计有所帮助。