Shell脚本监控Mysql主从同步状态+钉钉提醒

脚本编写

vim mysql_status.sh

#!/bin/bash

DING_TOKEN='https://oapi.dingtalk.com/robot/send?access_token=xxx'
MYSQL_PORT=3306
MYSQL_VIP1=192.168.1.111
MYSQL_VIP2=192.168.1.112
MYSQL_USERNAME=root
MYSQL_PASSWORD=root
GET_MYSQL_PORT=`netstat -na|grep "LISTEN"|grep "3306"|awk -F[:" "]+ '{print $5}'`
CHECK_MYSQL=`mysql -u$MYSQL_USERNAME -p$MYSQL_PASSWORD -h $MYSQL_VIP1 --connect_timeout=5 -e "show databases;"`
STATUS=$(mysql -u$MYSQL_USERNAME -p$MYSQL_PASSWORD -h $MYSQL_VIP1 -e "show slave status\G" | grep -i "running")
IO_env=`echo $STATUS | grep IO | awk ' {print $2}'`
SQL_env=`echo $STATUS | grep SQL | awk '{print $2}'`

function checkMysqlStatus(){
  if [ "$GET_MYSQL_PORT" == "3306" ]
  then
    if [ "$CHECK_MYSQL" -ne 0 ]
    then
      dingAlert "Server: $1 mysql is down, please try to restart mysql by manual!"
    else
      echo "Server: $1 mysql is running..."
    fi
  else
    dingAlert "WARN!Server: $1 mysql is down."
  fi

  if [ "$IO_env" = "Yes" -a "$SQL_env" = "Yes" ]
  then
    echo "Server: $1 MySQL Slave is running!"
  else
    dingAlert "Server: $1 MySQL Slave is not running!"
  fi
}

function dingAlert(){
  curl $DING_TOKEN \
    -H 'Content-Type: application/json' \
    -d "{\"msgtype\": \"text\", \"text\": {\"content\": \"数据库监控测试\n$1\"}}"
}

checkMysqlStatus $MYSQL_VIP1

脚本调用

chmod +x mysql_status.sh

./mysql_status.sh

参考资料

mysql主从监控shell