Detecting mysql synchronization status implementation code (php/linux)

Detecting mysql synchronization status implementation code (php/linux)_PHP tutorial

WBOY

Release： 2016-07-20 11:11:15

Original

791 people have browsed it

This article introduces two examples to introduce the MySQL synchronization status detection implementation program. Friends in need can refer to it.

The code is as follows

Copy code

代码如下

复制代码

"show slave statusG" | grep -i "running")
IO_ENV=`echo $MYSQL_SLAVE_STATUS | grep IO | awk ' {print }'`
SQL_ENV=`echo $MYSQL_SLAVE_STATUS | grep SQL | awk '{print }'`
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

#!/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 example code

The code is as follows

Copy code

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", "Username");
define("PASSWORD", "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 = "Unknown exception";
break;
case -3:
$memo = "Query failed";
break;
case -2:
$memo = "Unable to connect to port";
break;
case -1:
$memo = "Status unknown";
break;
case 0:
$memo = "OK";
break;
case 1:
$memo = "Synchronization failed";
if($res["Slave_IO_Running"] <> "Yes"){
.

if($res["Slave_SQL_Running"] <> "Yes"){

$memo .= $res["Last_SQL_Error"] . "(" . $res

[" Last_SQL_Errno"] . ")";
}

break;

case 2:
$memo = "Database is not synchronized";
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

Place the above three PHP files in the virtual directory and access them via URL.
Access method: http://ip/check_repl.php?key=xupeng