python定时巡检tidb，用飞书展示巡检结果

import os
import time
import requests
import json

proxies = { "http": "172.31.0.72:13080", "https": "172.31.0.72:13080", }

def send_lark(title, content):
    url = 'https://open.larksuite.com/open-apis/bot/v2/hook/'
    payload_message = {
        "msg_type": "post",
        "content": {"post": {
            "zh_cn": {
                "title": title,
                "content": [[{
                    "tag": "text",
                    "text": content
                }]]
            }
        }}
    }
    response = requests.request("POST", url, data=json.dumps(payload_message),proxies=proxies)
    print(response)

def main():
    while True:
        f=os.popen( "source /home/tidb/.bash_profile; tiup cluster display tidb-cluster")  # 返回的是一个文件对象
        message = (f"msg :  { f.read() }\n"  )
        print(message)
        send_lark("check_tidb",message)
        
        f=os.popen( "source /home/tidb/.bash_profile; cdc cli changefeed list --server=http://192.168.0.1:8300")  # 返回的是一个文件对象
        message = (f"msg :  { f.read() }\n"  )
        print(message)
        send_lark("check_cdc",message)


        time.sleep(3600)

if __name__ == "__main__":
    main()

CloudCanal 数据同步告警

# -*- encoding:utf-8 -*-
import pandas as pd
import time
import json
import pymysql
import requests
import sys
from datetime import datetime

def send_lark(title, content):
    url = 'https://open.larksuite.com/open-apis/bot/v2/hook/'
    payload_message = {
        "msg_type": "post",
        "content": {"post": {
            "zh_cn": {
                "title": title,
                "content": [[{
                    "tag": "text",
                    "text": content
                }]]
            }
        }}
    }
    response = requests.request("POST", url, data=json.dumps(payload_message))
    print(response)


db1 = pymysql.connect(
    host='172.31.26.140',
    port=3366,
    user='cloudcanal',
    password='',
    database='cloudcanal_console',
    charset='utf8'
)

db2 = pymysql.connect(
    host='172.31.24.50',
    port=4000,
    user='cdc',
    password='',
    database='cloudcanal_console',
    charset='utf8'
)

def get_id():
    with db1.cursor() as cursor:
        cursor.execute('select ifnull(max(id),0)   from cloudcanal_console.alert_event_log   ')
        id1 = cursor.fetchone()[0]
        db1.commit()
    with db1.cursor() as cursor:
        cursor.execute('select max(id)   FROM cloudcanal_console.exception_event   ')
        id2 = cursor.fetchone()[0]
        db1.commit()
    with db2.cursor() as cursor:
        cursor.execute('select ifnull(max(id),0)   from cloudcanal_console.alert_event_log      ')
        id3 = cursor.fetchone()[0]
        db2.commit()
    with db2.cursor() as cursor:
        cursor.execute('select max(id)   FROM cloudcanal_console.exception_event      ')
        id4 = cursor.fetchone()[0]
        db2.commit()

    return id1, id2, id3, id4


def select(id1, id2, id3, id4):
    with db1.cursor() as cursor:
        cursor.execute(f'select gmt_create, ip,  status, content  from cloudcanal_console.alert_event_log    where  id>{id1}')
        data1 = list(cursor.fetchall())
        cursor.execute('select ifnull(max(id),0)   from cloudcanal_console.alert_event_log   ')
        id1 = cursor.fetchone()[0]
        db1.commit()
    with db1.cursor() as cursor:
        cursor.execute(f'select gmt_create, ip, alarm_level, exception_stack  from cloudcanal_console.exception_event    where  id>{id2}')
        data2 = list(cursor.fetchall())
        cursor.execute('select ifnull(max(id),0)   from cloudcanal_console.exception_event   ')
        id2 = cursor.fetchone()[0]
        db1.commit()

    with db2.cursor() as cursor:
        cursor.execute(f'select gmt_create, ip,  status, content  from cloudcanal_console.alert_event_log    where  id>{id3}')
        data3 = list(cursor.fetchall())
        cursor.execute('select ifnull(max(id),0)   from cloudcanal_console.alert_event_log   ')
        id3 = cursor.fetchone()[0]
        db2.commit()
    with db2.cursor() as cursor:
        cursor.execute(f'select gmt_create, ip, alarm_level, exception_stack  from cloudcanal_console.exception_event    where  id>{id4}')
        data4 = list(cursor.fetchall())
        cursor.execute('select ifnull(max(id),0)   from cloudcanal_console.exception_event   ')
        id4 = cursor.fetchone()[0]
        db2.commit()

    data = data1 + data2 + data3 + data4

    df = pd.DataFrame(data, columns=['time', 'ip', 'status', 'content'])
    print(df)
    if len(df)>0:
        for i in range(len(df)):
            send_lark('   '  ,  "Alert  : {}\n{}\n".format(df.loc[i].T ,df.iat[i,3]) )

    return id1, id2, id3, id4

id1, id2, id3, id4 = get_id()

while True:
    id5, id6, id7, id8 = select(id1, id2, id3, id4)
    print(id5)
    print(id6)
    print(id7)
    print(id8)
    id1, id2, id3, id4 = id5, id6, id7, id8
    time.sleep(10)

cluster_slow_query告警

# -*- encoding:utf-8 -*-
import pandas as pd
import time
import json
import pymysql
import requests
import sys
from datetime import datetime
proxies = { "http": "172.31.0.72:13080", "https": "172.31.0.72:13080", }

def send_lark(title, content):
    url = 'https://open.larksuite.com/open-apis/bot/v2/hook/'
    payload_message = {
        "msg_type": "post",
        "content": {"post": {
            "zh_cn": {
                "title": title,
                "content": [[{
                    "tag": "text",
                    "text": content
                }]]
            }
        }}
    }
    response = requests.request("POST", url, data=json.dumps(payload_message) )
    print(response)

db1 = pymysql.connect(
    host='192.168.0.50',
    port=4000,
    user='root',
    password='',
    database='db1',
    charset='utf8',
    autocommit=True
)

def select(tm1):
    with db1.cursor() as cursor:
        sql='''
select Time,User,Host,Query_time,Query
from information_schema.cluster_slow_query
where is_internal = false  -- 排除 TiDB 内部的慢查询 SQL
and query_time >120
and time>"{}"
'''.format(tm1)
        cursor.execute(sql)
        data = list(cursor.fetchall())
        db1.commit()

    df = pd.DataFrame(data, columns=['time', 'User', 'Host', 'Query_time','Query'])
    print(df)
    if len(df)>0:
        for i in range(len(df)):
            send_lark('   '  ,  "Alert  : {}\n{}\n".format(df.loc[i].T ,df.iat[i,4]) )
        tm=df.iat[df.index[-1],0]
    else: 
        tm=tm1
    print(tm)
    return tm
    
tm1=datetime.now()

while True:
    tm2= select(tm1)
    tm1=tm2
    time.sleep(10)