监控服务器上的Mysql

前言

   这一期是纯代码分享，最近我在服务器上安装了个MYSQL数据库，但自建的数据库肯定比不上云上的数据库，所以我开始想办法对其进行监控。接下来就分享我的监控脚本

1.监控mysql的shell脚本

#!/bin/bash
#取个标记变量，来判断是否发送告警信息
flag=0
#获取mysql服务的存活状态
active=`systemctl status mysqld|awk /active/|awk -F " " '{print $1 $2 $3}'`
dateTime=`date +'%Y-%m-%d %H:%M:%S'`

#判断服务是否存活
if [ "$active" == "Active:active(running)" ];then
  echo  "$dateTime:[Info] Mysql $active" 
else
  #如果服务已经挂掉，打印日志写到monitor.log文件中
  echo "$dateTime:[Error] Mysql $active" >> /root/log/monitor.log
  flag=$(($flag+1))
  #并去读取mysql存放的日志文件获取ERROR信息并写到monitor.log文件中
  tail -f -n50 /var/log/mysqld.log|awk '/[ERROR]/'|while read logInfo
  do
   echo $logInfo >> /root/log/monitor.log
   flag=$(($flag+1))
  done
  
  #当有异常时,改变flag的值
fi

#获取磁盘文件系统的可用空间数据
df -h|awk '! /Filesystem/'|awk -F " " '{print $1 "%" $5}'|while read diskInfo
do
  fileSystem=`echo $diskInfo|awk -F "%" '{print $1}'`
  useInfo=`echo $diskInfo|awk -F "%" '{print $2}'`
  
  #判断文件系统的磁盘占比
  if [ "$useInfo" -ge 78 ] && [ "$useInfo" -lt 90 ];then
    echo  "$dateTime:[Warn] $fileSystem used $useInfo, and remaining space is not enough !!!" >> /root/log/monitor.log
  flag=$(($flag+1))
  #当某个文件系统可用空间剩余不到10%，则打印日志到monitor.log文件中
  elif [ "$useInfo" -ge 90 ];then
  echo  "$dateTime:[Serious Warn] $fileSystem used $useInfo, you must clean up the fileSystem disk !!!"  >> /root/log/monitor.log
  flag=$(($flag+1))
  #当某个文件系统可用空间剩余不足，则打印日志到monitor.log文件中
  elif [ "$useInfo" -ge 99 ];then
    echo  "$dateTime:[Error] $fileSystem is full !!!" >> /root/log/monitor.log
  flag=$(($flag+1))
  else
  echo  "$dateTime:[Info] $fileSystem used $useInfo, and remaining space is enough"
  fi
  #这里有个坑！！！使用管道符的方式写while循环,循环内的变量随着循环结束就会失效,所以我将变量存于一个临时文件中
  echo $flag >/root/scripts/readline.txt
done

#获取临时文件中的数值
varNum=`cat /root/scripts/readline.txt|awk -F " " '{print $1}'`
echo "临时文件中的数值为: $varNum"
if [ $varNum -gt 0 ];then
  flag=$varNum
  #将临时文件的数值变为0
  echo 0 >/root/scripts/readline.txt
fi

if [ $flag -gt 0 ];then
  echo "有异常发生"
  # 通过flag来获取最新写入到monitor.log的几行数据
  Msg=`tail -n$flag /root/log/monitor.log`
  echo $flag

  # 将告警信息传到脚本中并发送告警信息
  java -jar /root/scripts/SendMsg-1.0.jar $Msg 
fi  

2.发送告警信息的JAVA脚本 

Maven依赖

<dependencies>
  <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.76</version>
  </dependency>
  <dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>4.0.12</version>
  </dependency>
  <dependency>
    <groupId>javax.activation</groupId>
    <artifactId>activation</artifactId>
    <version>1.1.1</version>
  </dependency>
  <dependency>
    <groupId>com.google.collections</groupId>
    <artifactId>google-collections</artifactId>
    <version>1.0</version>
  </dependency>
  <dependency>
    <groupId>commons-codec</groupId>
    <artifactId>commons-codec</artifactId>
    <version>1.6</version>
  </dependency>
  <!-- HttpClinet 核心包 -->
  <dependency>
    <groupId>org.apache.httpcomponents</groupId>
    <artifactId>httpclient</artifactId>
    <version>4.5.13</version>
  </dependency>
  <!-- HttpClinet 请求时，涉及文件上传需要的包 -->
  <dependency>
    <groupId>org.apache.httpcomponents</groupId>
    <artifactId>httpmime</artifactId>
    <version>4.5.13</version>
  </dependency>
  <dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-api</artifactId>
    <version>1.7.5</version>
  </dependency>
</dependencies>

Util工具类

import com.alibaba.fastjson.JSON;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
import java.io.*;
import java.net.URL;
import java.net.URLConnection;
import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;
 
public class SendHttps {
    private static Logger logger = LoggerFactory.getLogger(SendHttps.class);
    /**
     * 发送POST请求，参数是Map, contentType=x-www-form-urlencoded
     *
     * @param url
     * @param mapParam
     * @return
     */
    public static String sendPostByMap(String url, Map<String, Object> mapParam) {
        Map<String, String> headParam = new HashMap<>();
        headParam.put("Content-type", "application/json;charset=UTF-8");
        return sendPost(url, mapParam, headParam);
    }
 
    /**
     * 向指定 URL 发送POST方法的请求
     *
     * @param url   发送请求的 URL
     * @param param 请求参数，
     * @return 所代表远程资源的响应结果
     */
    public static String sendPost(String url, Map<String, Object> param, Map<String, String> headParam) {
        PrintWriter out = null;
        BufferedReader in = null;
        String result = "";
        try {
            URL realUrl = new URL(url);
            // 打开和URL之间的连接
            URLConnection conn = realUrl.openConnection();
            // 设置通用的请求属性 请求头
            conn.setRequestProperty("accept", "*/*");
            conn.setRequestProperty("connection", "Keep-Alive");
            conn.setRequestProperty("user-agent", "Fiddler");
 
            if (headParam != null) {
                for (Entry<String, String> entry : headParam.entrySet()) {
                    conn.setRequestProperty(entry.getKey(), entry.getValue());
                }
            }
            // 发送POST请求必须设置如下两行
            conn.setDoOutput(true);
            conn.setDoInput(true);
            // 获取URLConnection对象对应的输出流
            out = new PrintWriter(conn.getOutputStream());
            // 发送请求参数
            out.print(JSON.toJSONString(param));
            // flush输出流的缓冲
            out.flush();
            // 定义BufferedReader输入流来读取URL的响应
            in = new BufferedReader(
                    new InputStreamReader(conn.getInputStream()));
            String line;
            while ((line = in.readLine()) != null) {
                result += line;
            }
        } catch (Exception e) {
            logger.info("发送 POST 请求出现异常！" + e);
            e.printStackTrace();
        }
        //使用finally块来关闭输出流、输入流
        finally {
            try {
                if (out != null) {
                    out.close();
                }
                if (in != null) {
                    in.close();
                }
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
        return result;
    }
    
}

Dao操作类

import com.fantec.util.SendHttps;

import java.util.HashMap;
import java.util.Map;

public class SendMsgDao {
    public static void main(String[] args) {
        String message = "Hello world";
        System.out.println(message);
        // 钉钉的webhook
        String dingDingToken="钉钉机器人webhook";
        // 请求的JSON数据，这里我用map在工具类里转成json格式
        Map<String,Object> json=new HashMap<>();
        Map<String,Object> text=new HashMap<>();
        json.put("msgtype","text");
        text.put("content", message);
        json.put("text",text);
        // 发送post请求
        String response = SendHttps.sendPostByMap(dingDingToken, json);
        System.out.println("相应结果："+response);
    }
}

3.设置定时任务

# 这里我用的是服务器自带的定时器
crontab -e # 编写定时任务

如果大佬看完小编肝完的代码觉得有地方还可以进一步优化的，请一定要联系小编，相互交流是进步的一种捷径。