一种监控mysql执行语句的方法

我这里方法是定时读取processlist列表，执行时间超过5s的，推送钉钉消息，每10s轮询一次。

#!/usr/bin/env python

import mysql.connector
import pytz
import time,hashlib,hmac,urllib,base64,datetime
import requests,json
from urllib import parse
from apscheduler.schedulers.blocking import BlockingScheduler
tz = pytz.timezone('Asia/Shanghai')

URL="https://oapi.dingtalk.com/robot/send?access_token=77f297eac76d6497f9825d458d065a7e3e7daf22204fe12b05569b6a0697ccxxx"
secret = "xxxxx"

def get_timestamp_sign():
    timestamp = str(round(time.time() * 1000))
    secret_enc = secret.encode('utf-8')
    string_to_sign = '{}\n{}'.format(timestamp, secret)
    string_to_sign_enc = string_to_sign.encode('utf-8')
    hmac_code = hmac.new(secret_enc, string_to_sign_enc,
                         digestmod=hashlib.sha256).digest()
    sign = urllib.parse.quote_plus(base64.b64encode(hmac_code))
    return (timestamp, sign)

def get_signed_url():
    timestamp, sign = get_timestamp_sign()
    webhook = URL + "&timestamp="+timestamp+"&sign="+sign
    return webhook

def get_webhook(mode):
    if mode == 0: # only 敏感字
       webhook = URL
    elif mode == 1 or  mode ==2 : # 敏感字和加签 或 # 敏感字+加签+ip
        webhook = get_signed_url()
    else:
        webhook = ""
        print("error! mode:   ",mode,"  webhook :  ",webhook)
    return webhook
def run(username, password, host,env):
    cnx = mysql.connector.connect(user=username,password=password,host=host)
    cursor = cnx.cursor(buffered=True)
    cursor.execute("SELECT SUBSTRING_INDEX(concat(user,'@',host),':',1)state,time,lower(INFO)e_sql FROM INFORMATION_SCHEMA.PROCESSLIST \
            WHERE STATE in ('executing','Sending data')  and time>5 and info not like '%xxx%' ;")

    webhook = get_webhook(1)
    headers = {'Content-Type': 'application/json'}
    kinfo=cursor.fetchall()
    for ip,i_time,info in kinfo:
        print(kinfo)
        data = {
        "msgtype": "markdown",
        "markdown": {
            "title": "rds slow logs" + "....",
            "text": "<font color=#FF0000 size=3>slow query detail：</font>" +
                    "\n\n>log_time " + str(datetime.datetime.now(tz).strftime('%Y-%m-%d %H:%M:%S')) +
                    "\n\n>env : " + str(env) +
                    "\n\n>host_ip : " + str(ip) +
                    "\n\n>elapsed_time is above : " + str(i_time) + 's' +
                    "\n\n>sql detail : " + str(info)
        },
        "at": {"atMobiles": ["123456"]},"isAtAll": "False"}
        requests.post(url=webhook, data=json.dumps(data), headers=headers)
    cursor.close()
    cnx.close()

def aa_slow_sql():
    now = datetime.datetime.now()
    ts = now.strftime('%Y-%m-%d %H:%M:%S')
    run(username='xxx',password='xxxx',host='10.x.x.x',env='xxx')
    print('do slowlog  time :',ts)

def bb_slow_sql():
    now = datetime.datetime.now()
    ts = now.strftime('%Y-%m-%d %H:%M:%S')
    run(username='xxx',password='xxx',host='10.x.x.x',env='xxx)')
    print('do slowlog  time :',ts)

def cc_slow_sql():
    now = datetime.datetime.now()
    ts = now.strftime('%Y-%m-%d %H:%M:%S')
    run(username='xxx',password='xxx',host='10.x.x.x',env='xxx)')
    print('do lowlog  time :',ts)

def tasklist():
    scheduler = BlockingScheduler()
    scheduler.add_job(aa_slow_sql, 'interval', seconds=10, id='slow1_sql',timezone=pytz.timezone('Asia/Shanghai'))
    scheduler.add_job(bb_slow_sql, 'interval', seconds=10, id='slow2_sql',timezone=pytz.timezone('Asia/Shanghai'))
    scheduler.add_job(cc_slow_sql, 'interval', seconds=10, id='slow3_sql',timezone=pytz.timezone('Asia/Shanghai'))
    scheduler.start()
tasklist()

Dockerfile(类似):

FROM python:3.8-alpine
RUN addgroup -S zwgroup && adduser -S xxx -G zwgroup
#creates work dir
WORKDIR /app
#copy python script to the container folder app
COPY slow_alert.py /app/slow_alert.py
COPY ./requirements.txt /app/requirements.txt
RUN apk add gcc libc-dev linux-headers libffi-dev

RUN chmod +x /app/slow_alert.py
RUN pip3 install --upgrade pip && pip3 install --no-cache-dir -r requirements.txt
#user is appuser
ENTRYPOINT ["python", "/app/slow_alert.py"]

然后做成pod，运行于k8s.