检测mysql同步状态实现代码(php/linux)

本文章介绍两个实例来介绍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 ( empty 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 <<  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 ) { //默认状态未知 //开源代码phpfensi.com $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 empty ( $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:端口" ; $mysql_instances [] = "远程ip:端口" ; function get_instances() { global $mysql_instances ; return $mysql_instances ; }
将以上三个PHP文件放在虚拟目录中,然后通过URL访问.

访问方式：http://ip/check_repl.php?key=xupeng

查看更多关于检测mysql同步状态实现代码(php/linux) - php高级应用的详细内容...

声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did30291

更新时间：2022-09-18 阅读：44次