Mysql主从同步状态监控 | 系统运维

Mysql主从同步状态监控

发表于478 天前 ⁄ 运维|存储技术, 运维|运维监控 ⁄ 评论数 5 ⁄ 被围观 +

【最终效果图】

实现过程：

check_rep.php

<!--?php<br /-->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 <<

<!-- 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

<!--?php<br /-->/*
 * 检查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  //GRANT REPLICATION CLIENT ON *.* TO '用户名'@'监控主机ip' IDENTIFIED BY '密码';  $mysql_instances = array(); $mysql_instances[] = "远程ip:端口";  $mysql_instances[] = "远程ip:端口"; function get_instances() {  	global $mysql_instances;  	return $mysql_instances;  }  ?-->

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

固定链接: Mysql主从同步状态监控 | 系统运维 | Web2.0 +复制链接