本文实例讲述了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测试数据" 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."); }
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程序设计有所帮助。
查看更多关于php在linux下检测mysql同步状态的方法的详细内容...