首页 > 后端开发 > php教程 > 检测mysql同步状态实现代码(php/linux)_PHP教程

检测mysql同步状态实现代码(php/linux)_PHP教程

WBOY
发布: 2016-07-20 11:11:15
原创
799 人浏览过

本文章介绍两个实例来介绍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(empty($_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

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 "




















































instance result Slave_IO_Running Slave_SQL_Running Master_Host Master_Port Replicate_Do_DB memo
instance result Slave_IO_Running Slave_SQL_Running Master_Host Master_Port Replicate_Do_DB Slave_IO_State Last_IO_Errno Last_IO_Error Last_SQL_Errno Last_SQL_Error
{$host} {$res['result']} {$res['Slave_IO_Running']} {$res['Slave_SQL_Running']} {$res['Master_Host']} {$res['Master_Port']} {$res['Replicate_Do_DB']} {$memo}
{$host} {$res['result']} {$res['Slave_IO_Running']} {$res['Slave_SQL_Running']} {$res['Master_Host']} {$res['Master_Port']} {$res['Replicate_Do_DB']} {$res['Slave_IO_State']} {$res['Last_IO_Errno']} {$res['Last_IO_Error']} {$res['Last_SQL_Errno']} {$res['Last_SQL_Error']}

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(!empty($row)){
    $r["result"] = 1;
   }else{
    $r["result"] = 2;
   }
  }
 }catch(Exception $e){
  $r["result"] = -4;
 }
 return($r);
}
mysql_instance.php

将以上三个PHP文件放在虚拟目录中,然后通过URL访问。
访问方式:http://ip/check_repl.php?key=xupeng


www.bkjia.comtruehttp://www.bkjia.com/PHPjc/444665.htmlTechArticle本文章介绍两个实例来介绍mysql同步状态检测实现程序有需要的朋友可参考一下。 代码如下 复制代码 #!/bin/sh #check MySQL_Slave Status #crontab ti...
来源:php.cn
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
最新问题
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板