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
#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.");
}
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服务器的同步状态
*/
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:端口";
array();
$mysql_instances[] = "远程ip:端口";
function get_instances()
{
global $mysql_instances;
return $mysql_instances;
}
将以上三个php文件放在虚拟目录中,然后通过url访问.
访问方式:http://ip/check_repl.php?key=xupeng
希望本文所述对大家的php程序设计有所帮助。
上一篇: BZOJ 1823: [JSOI2010]满汉全席
下一篇: html、css简述面试题
推荐阅读
-
在Linux环境下mysql的root密码忘记解决方法(三种)
-
在Linux环境下mysql的root密码忘记解决方法(三种)
-
linux系统下将php和mysql命令加入到环境变量中的方法
-
CentOS Linux 下配置Apache2+PHP5+MySQL5+GD库的方法
-
php在linux下检测mysql同步状态的方法
-
利用PHP脚本在Linux下用md5函数加密字符串的方法
-
Linux下安装PHP,APACHE,MYSQL,PERL的方法
-
检测mysql同步状态实现代码(php/linux)
-
PHP在Linux下连接MSSQLServer的配置方法简述(二、FreeTDS库)_PHP教程
-
PHP在Linux下连接MSSQLServer的配置方法简述(一、Sybase库)_PHP教程