python 实时监控mysql慢查询日志 发送信息到钉钉机器人

2019-08-03 08:30:26 星期六

1.mysql配置

# cat /etc/redhat-release 
CentOS release 6.5 (Final)

# mysql -V
mysql  Ver 14.14 Distrib 5.6.17, for linux-glibc2.5 (x86_64) using  EditLine wrapper

mysql.cnf配置
slow_query_log=1
long_query_time=1
slow_query_log_file=/usr/local/mysql/data/slow-query.log

2.安装python模块

# python -V
Python 2.7.15

安装python依赖包
pip install pyinotify
pip install requests
pip install json

3.pyhon脚本

两个脚本同级目录

py_inotify.py

# !/usr/bin/python
#coding:utf-8
import sys, os, pyinotify
import dingding
notifier = None
monfile = None
lastsize = 0
wm = None
wd = 0
def roll_file(filename):
    global lastsize
    fd = os.open(filename, os.O_RDONLY)
    try:
        newsize = os.fstat(fd).st_size
        if newsize <= lastsize: return
        os.lseek(fd, lastsize, os.SEEK_SET)
        while True:
            data = os.read(fd, 4096)
            if not data: break
            dingding.dingmessage(data)
        sys.stdout.flush()
        pos = os.lseek(fd, 0, os.SEEK_CUR)
        lastsize = pos if pos != lastsize else newsize
    finally:
        os.close(fd)
class EventHandler(pyinotify.ProcessEvent):
    def process_IN_CREATE(self, event):
        if monfile == event.pathname:
            global wd
            wd = wm.add_watch(monfile, pyinotify.IN_MODIFY).values()[0]
            roll_file(monfile)
    def process_IN_DELETE(self, event):
        global wd, lastsize
        if monfile == event.pathname:
            if wd > 0:
                try:
                    wm.rm_watch(wd, quiet=False)
                except pyinotify.WatchManagerError:
                    pass
                wd = 0
            lastsize = 0
    def process_IN_MOVED_FROM(self, event):
        self.process_IN_DELETE(event)
    def process_IN_MOVED_TO(self, event):
        self.process_IN_DELETE(event)
        self.process_IN_CREATE(event)
    def process_IN_MODIFY(self, event):
        roll_file(monfile)
def main():
    global notifier, lastsize, wm, wd, monfile
    monfile = "/usr/local/mysql/data/slow-query.log"
    #print "path={0}".format(monfile)
    lastsize = os.stat(monfile).st_size
    wm = pyinotify.WatchManager()
    notifier = pyinotify.Notifier(wm, EventHandler())
    wd = wm.add_watch(monfile, pyinotify.IN_MODIFY).values()[0]
    wm.add_watch(os.path.dirname(monfile),
                 pyinotify.IN_DELETE | pyinotify.IN_CREATE | pyinotify.IN_MOVED_FROM | pyinotify.IN_MOVED_TO)
    #print "watching {0} ...".format(monfile)
    while True:
        notifier.process_events()
        if notifier.check_events():
            notifier.read_events()
if __name__ == "__main__":
    try:
        main()
    finally:
        if notifier: notifier.stop()

dingding.py

#coding:utf-8
import requests
import json
def dingmessage(message):
# 请求的URL，WebHook地址
    webhook = "https://oapi.dingtalk.com/robot/send?access_token=xxx"
#构建请求头部
    header = {
        "Content-Type": "application/json",
        "Charset": "UTF-8"
}
#构建请求数据
    tex = message
    message ={
        "msgtype": "text",
        "text": {
            "content": tex
        },
        "at": {
            "isAtAll": False
        }
    }
#对请求的数据进行json封装
    message_json = json.dumps(message)
#发送请求
    info = requests.post(url=webhook,data=message_json,headers=header)
#打印返回的结果
    print(info.text)

参考文档

https://blog.csdn.net/qq_33285112/article/details/90485360