bob体育平台检测MySQL主从备份是否运行

2019-12-17 13:16栏目:bob体育app
TAG:

通过查看 slave  状态,确保 Slave_IO_Running: Yes Slave_SQL_Running: Yes

发布:thebaby   来源:net     【大 中 小】

本文实例讲述了php在linux下检测mysql同步状态的方法。分享给大家供大家参考。具体分析如下:

#!/bin/bash
#Author:Darius-D
mysql -uroot -p123123 -e 'show slave statusG' >a.txt
IO=`head -12 a.txt | tail -1|awk -F ':' '{print $2}'`
SQL=`head -13 a.txt | tail -1|awk -F ':' '{print $2}'`
if [ $IO == 'Yes' ];then
echo "Slave_IO_Running:Yes"
if [ $SQL == 'Yes' ];then
echo "Slave_SQL_Running:Yes"
else
echo "Slave_SQL_Running:No"
fi
else
echo "Slave_IO_Running:No"
fi
有关于mysql主从备份相关原理及备份链接进入查看。

分享一例shell脚本,用于修改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 0php实例代码,代码如下:check_rep.php:复制代码 代码如下:if(emptyempty die; if($_REQUEST["key"] != 'xupeng') die; include; include("check_status_api.php"); define; define; define; $instances = get_instances{ echo << END; echo " n"; if{ echo " n"; }else{ echo " n"; } foreach{ $res = check_mysql_replication_status($host, USERNAME, PASSWORD); if{ switch{ 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 "

一个可以修改mysql主从同步的shell脚本。

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']}

版权声明:本文由bob体育app发布于bob体育app,转载请注明出处:bob体育平台检测MySQL主从备份是否运行