对mysql表字段增减的监控报警

大数据同事抽数的程序有点low，没有自动适配表的字段变化，需要写个脚本自动监控字段变化，并报警到钉钉群。

思路很简单：

python，定时获取目标表的字段，持久化到文件，作为下一次比对的依据；

下一次获取当前字段，与上次持久化的字段对比，有新增或删除的字段报警；

直接上脚本：rds_col_monitor.py

#!/usr/bin/python3
# -*- coding: utf-8 -*-
# @Time    : 2021-12
# @Author  : meishidong

import pymysql
import pickle
import os.path,sys
import urllib.request
import urllib.parse
import json

## 配置文件,要监控的表
tablist_file = '/home/bigdata/table_list.file'
## 程序获取到的表和字段信息持久化的文件，不需修改
tabcol_dic_file = sys.path[0] + '/tabcol_dic.file'
## 告警钉钉群
ding_url = "https://oapi.dingtalk.com/robot/send?access_token=<<your-token>>"

def get_tablelist():
    tablist_dic = {}
    f = open(tablist_file, 'r')
    tablist = f.read().splitlines()
    for t in tablist:
        schema = t.split('.')[0]
        tabname = t.split('.')[1]
        if schema in tablist_dic:
            tablist_dic[schema].append("'" + tabname + "'")
        else:
            tablist_dic[schema]=["'" + tabname + "'"]

    wherelist = []
    for (k,v) in tablist_dic.items():
        wherelist.append("(table_schema='%s' and table_name in (%s))" % (k,','.join(v)))

    wherestr = ' or '.join(wherelist)
    wherestr = "(" + wherestr + ")"
    return wherestr


def get_tabcol_before():
    dic_bef={}
    if os.path.isfile(tabcol_dic_file):
        with open(tabcol_dic_file, "rb") as f:
            dic_bef = pickle.load(f)
        return dic_bef
    return dic_bef

def get_tabcol_current():
    tabcol_dic = {}
    ## 目标库的信息
    db = pymysql.connect(host='<<ip>>',
                         user='<<username>>',
                         password='<<password>>',
                         database='information_schema')
    cursor = db.cursor()

    wherestr = get_tablelist() 
    sql = "select concat(table_schema,'.',table_name) tabname,column_name from information_schema.columns \
           where %s" % (wherestr)

    cursor.execute(sql)
    results = cursor.fetchall()
    for row in results:
      if row[0] in tabcol_dic:
          tabcol_dic[row[0]].append(row[1])
      else:
          tabcol_dic[row[0]]=[row[1]]
    db.close()

    with open(tabcol_dic_file, "wb") as f:
        pickle.dump(tabcol_dic, f)

    return tabcol_dic

def get_col_change():    
    add_col_dic={}
    del_col_dic={}

    tabcol_before = get_tabcol_before()
    tabcol_current = get_tabcol_current()
 
    for (tab,cols) in tabcol_before.items():
        cols_bef = cols
        cols_cur = tabcol_current[tab]
        add_cols = set(cols_cur) - set(cols_bef)
        del_cols = set(cols_bef) - set(cols_cur)
        if add_cols:
            add_col_dic[tab] = list(add_cols)
        if del_cols:
            del_col_dic[tab] = list(del_cols)
    return add_col_dic,del_col_dic

def send_msg_by_ding(message):
    header = {
        "Content-Type": "application/json"
    }
    data = {
        "msgtype": "text", 
        "text": {"content": message}
    }
    send_data = json.dumps(data).encode('utf-8')
    req = urllib.request.Request(ding_url, data=send_data, headers=header, method='POST')
    ret = urllib.request.urlopen(req)
    if ret.status != 200:
        print("send message error!")

def main():    
    ret = get_col_change()
    add_dic = ret[0]
    del_dic = ret[1]
    message = "字段变更报警：\n"
    if add_dic:
        for (tab,cols) in add_dic.items():
            cols.sort()
            message = message + tab + " 增加字段: " + ', '.join(cols) + "\n"
    if del_dic:
        for (tab,cols) in del_dic.items():
            cols.sort()
            message = message + tab + " 删除字段: " + ', '.join(cols) + "\n"
    if add_dic or del_dic:
        send_msg_by_ding(message)

if __name__ == '__main__':
    main()

配置文件格式：

schema1.table_a
schema2.table_b
schema2.table_c

定期执行：

*/5 * * * * /usr/bin/python3 /root/script/rds-col-monitor/rds_col_monitor.py

报警效果：