linux mysql 判断用户是否存在_php在linux下检测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 <<

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(!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程序设计有所帮助。