MySQL 数据库主从状态监控

2020年1月8日11:01:15 评论
前言

mysql数据库做了主从状态 需要知道主从的状态如何 如果有错误 立即发邮件

数据库主机是多端口的 先查出所有端口号,根据端口进行主从状态监控,查询主从状态的日志文件记录在本机,如果 Slave_IO_Running 和 Slave_SQL_Running 状态有一个不是yes 会向主控机发送日志内容 主控机调用mailx来发送邮件 提示管理员数据库主从异常

#!/bin/bash

#获取mysql端口号
MysqlPort=`netstat -an|grep "LISTEN"|grep "33*" | grep "36*" |awk -F ' ' '{print $4}'|awk -F ':' '{print $4}'`
for i in $MysqlPort
do
  case $i in
  "3306")
  #获取数据库同步状态
MysqlStatus=$(/usr/local/mysql/bin/mysql -S /MM/DB/server0/mysql.sock -u用户名 -p密码 -e "show slave status\G" |grep -i "running"|head -n2)
#分别获取2个io是不是yes状态
Slave_IO_Running=`echo $MysqlStatus | grep Slave_IO_Running |awk '{print $2}'`
Slave_SQL_Running=`echo $MysqlStatus | grep Slave_SQL_Running |awk '{print $2}'`
ToDay=`date +"%y-%m-%d %H:%M:%S"`
#判断MySQL同步状态是不是yes
if [ "$Slave_IO_Running" == "Yes" -a "$Slave_SQL_Running" == "Yes" ]
  then
      echo "######  $ToDay  ######" >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3306 从同步正常........" >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3306 端口 Slave_IO_Running:" $Slave_IO_Running >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3306 端口 Slave_SQL_Running:"$Slave_SQL_Running >> /MM/LOG/mysql-master-to-slave-5.log
elif [ "$Slave_IO_Running" != "Yes" -o "$Slave_SQL_Running" != "Yes" ]
  then
      echo "######  $ToDay  ######" >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3306 从同步异常........" >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3306 端口 Slave_IO_Running:" $Slave_IO_Running >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3306 端口 Slave_SQL_Running:"$Slave_SQL_Running >> /MM/LOG/mysql-master-to-slave-5.log
      tail -n 4 /MM/LOG/mysql-master-to-slave-5.log > /MM/LOG/db-5-report.txt
      scp -r /MM/LOG/db-5-report.txt 用户名@106.75.*.*:/MM/
fi
;;
  "3307")
  #获取数据库同步状态
MysqlStatus=$(/usr/local/mysql/bin/mysql -S /MM/DB/server1/mysql.sock -u用户名 -p密码 -e "show slave status\G" |grep -i "running"|head -n2)
#分别获取2个io是不是yes状态
Slave_IO_Running=`echo $MysqlStatus | grep Slave_IO_Running |awk '{print $2}'`
Slave_SQL_Running=`echo $MysqlStatus | grep Slave_SQL_Running |awk '{print $2}'`
ToDay=`date +"%y-%m-%d %H:%M:%S"`
#判断MySQL同步状态是不是yes
if [ "$Slave_IO_Running" == "Yes" -a "$Slave_SQL_Running" == "Yes" ]
  then
      echo "######  $ToDay  ######" >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3307 从同步正常........" >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3307 端口 Slave_IO_Running:" $Slave_IO_Running >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3307 端口 Slave_SQL_Running:"$Slave_SQL_Running >> /MM/LOG/mysql-master-to-slave-5.log
elif [ "$Slave_IO_Running" != "Yes" -o "$Slave_SQL_Running" != "Yes" ]
  then
      echo "######  $ToDay  ######" >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3306 从同步异常........" >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3306 端口 Slave_IO_Running:" $Slave_IO_Running >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3306 端口 Slave_SQL_Running:"$Slave_SQL_Running >> /MM/LOG/mysql-master-to-slave-5.log
      tail -n 4 /MM/LOG/mysql-master-to-slave-5.log > /MM/LOG/db-5-report.txt
      scp -r /MM/LOG/db-5-report.txt 用户名@106.75.*.*:/MM/
fi
;;
  "3601")
  #获取数据库同步状态
MysqlStatus=$(/usr/local/mysql/bin/mysql -S /MM/DB/db-point-3601/mysql.sock -umm_admin -p密码 -e "show slave status\G" |grep -i "running"|head -n2)
#分别获取2个io是不是yes状态
Slave_IO_Running=`echo $MysqlStatus | grep Slave_IO_Running |awk '{print $2}'`
Slave_SQL_Running=`echo $MysqlStatus | grep Slave_SQL_Running |awk '{print $2}'`
ToDay=`date +"%y-%m-%d %H:%M:%S"`
#判断MySQL同步状态是不是yes
if [ "$Slave_IO_Running" == "Yes" -a "$Slave_SQL_Running" == "Yes" ]
  then
      echo "######  $ToDay  ######" >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3601 从同步正常........" >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3601 端口 Slave_IO_Running:" $Slave_IO_Running >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3601 端口 Slave_SQL_Running:"$Slave_SQL_Running >> /MM/LOG/mysql-master-to-slave-5.log
elif [ "$Slave_IO_Running" != "Yes" -o "$Slave_SQL_Running" != "Yes" ]
  then
      echo "######  $ToDay  ######" >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3601 从同步异常........" >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3601 端口 Slave_IO_Running:" $Slave_IO_Running >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3601 端口 Slave_SQL_Running:"$Slave_SQL_Running >> /MM/LOG/mysql-master-to-slave-5.log
      tail -n 4 /MM/LOG/mysql-master-to-slave-5.log > /MM/LOG/db-5-report.txt
      scp -r /MM/LOG/db-5-report.txt 用户名@106.75.*.*:/MM/
fi
;;
  "3602")
  #获取数据库同步状态
MysqlStatus=$(/usr/local/mysql/bin/mysql -S /MM/DB/db-wxqiyehao-3602/mysql.sock -u用户名 -p密码 -e "show slave status\G" |grep -i "running"|head -n2)
#分别获取2个io是不是yes状态
Slave_IO_Running=`echo $MysqlStatus | grep Slave_IO_Running |awk '{print $2}'`
Slave_SQL_Running=`echo $MysqlStatus | grep Slave_SQL_Running |awk '{print $2}'`
ToDay=`date +"%y-%m-%d %H:%M:%S"`
#判断MySQL同步状态是不是yes
if [ "$Slave_IO_Running" == "Yes" -a "$Slave_SQL_Running" == "Yes" ]
  then
      echo "######  $ToDay  ######" >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3602 从同步正常........" >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3602 端口 Slave_IO_Running:" $Slave_IO_Running >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3602 端口 Slave_SQL_Running:"$Slave_SQL_Running >> /MM/LOG/mysql-master-to-slave-5.log
elif [ "$Slave_IO_Running" != "Yes" -o "$Slave_SQL_Running" != "Yes" ]
  then
      echo "######  $ToDay  ######" >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3602 从同步异常........" >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3602 端口 Slave_IO_Running:" $Slave_IO_Running >> /MM/LOG/mysql-master-to-slave-5.log
      echo " MySQL-db-server-5数据库 3602 端口 Slave_SQL_Running:"$Slave_SQL_Running >> /MM/LOG/mysql-master-to-slave-5.log
      tail -n 4 /MM/LOG/mysql-master-to-slave-5.log > /MM/LOG/db-5-report.txt
      scp -r /MM/LOG/db-5-report.txt 用户名@106.75.*.*:/MM/
fi
;;

  *)
  ;;
  esac
done

rm -rf /MM/LOG/db-5-report.txt
weinxin
我的微信
这是我的微信扫一扫

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: