Python监控 MySQL同步

• mysql监控双主同步，异常则通过钉钉告警

脚本内容

#!/usr/bin/env python 
# -*- coding: utf-8 -*-
# @Time    : 2023/6/26 17:30
# @Author  : 沙河小王子
# @File    : mysql_monit.py
# @Software: PyCharm
import pymysql
import socket
import datetime
import hashlib
import base64
import hmac
import urllib
import time
import requests
import configparser


class MysqlConfig:
    def __init__(self, config_file):
        self.config_file = config_file

    def get_config(self):
        config = configparser.ConfigParser()
        config.read(self.config_file)
        return config['MySQL']


class DingTalkConfig:
    def __init__(self, config_file):
        self.config_file = config_file

    def get_config(self):
        config = configparser.ConfigParser()
        config.read(self.config_file)
        return config['DingTalk']


def get_digest(secret, timestamp):
    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 f"&timestamp={timestamp}&sign={sign}"


def get_system_info():
    dt = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    ip = requests.get('http://ifconfig.me').text.strip()
    s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    s.connect(('8.8.8.8', 80))
    local_ip = s.getsockname()[0]
    s.close()
    host_name = socket.gethostname()
    return dt, ip, local_ip, host_name


def send_dingtalk_notification(webhook_url, secret, mobile_number):
    timestamp = str(round(time.time() * 1000))
    msg = "**告警主题**:" + "\n" + 'HK-集群环境-MySQL同步监控' + "\n\n" \
          ">当前时间:" + "\n" + get_system_info()[0] + "\n\n" \
          " >当前主机名:" + "\n" + get_system_info()[3] + "\n\n" \
          " >当前服务器IP:" + "\n" + get_system_info()[1] + "\n" + get_system_info()[2] + "\n\n" \
          "当前状态: SQL线程，IO线程同步失败，请检查！"

    data = {
        "msgtype": "markdown",
        "markdown": {
            "title": 'H',
            "text": msg,
        },
        "at": {
            "atMobiles": [
                mobile_number
            ],
            "isAtAll": False
        }
    }

    rec = requests.post(webhook_url + get_digest(secret, timestamp), json=data)


class MySQLReplicationChecker:
    def __init__(self, master_host, master_port, master_user, master_password, slave_host):
        self.master_host = master_host
        self.master_port = master_port
        self.master_user = master_user
        self.master_password = master_password
        self.slave_host = slave_host

    def check_replication(self):
        with pymysql.connect(
                host=self.master_host,
                port=self.master_port,
                user=self.master_user,
                password=self.master_password
        ) as master_conn:
            master_cursor = master_conn.cursor()
            master_cursor.execute('SHOW MASTER STATUS')
            master_status = master_cursor.fetchone()
            master_cursor.execute('SHOW SLAVE STATUS')
            master_replication_status = master_cursor.fetchone()
            master_dict = {
                "IO线程状态": master_replication_status[10],
                "SQL线程状态": master_replication_status[11]
            }
            return master_dict


class MySQLReplicationNotifier:
    def __init__(self, checker, dingtalk_config):
        self.checker = checker
        self.dingtalk_config = dingtalk_config

    def notify_if_replication_failed(self):
        master_dict = self.checker.check_replication()
        for v in master_dict.values():
            if v != "Yes":
                webhook_url = self.dingtalk_config['prod_webhook_url']
                secret = self.dingtalk_config['prod_secret']
                mobile_number = self.dingtalk_config['mobile_number']
                send_dingtalk_notification(webhook_url, secret, mobile_number)
                break
            else:
                print("同步正常")


if __name__ == '__main__':
    mysql_config = MysqlConfig('config.ini').get_config()
    dingtalk_config = DingTalkConfig('config.ini').get_config()

    checker = MySQLReplicationChecker(
        mysql_config['master_host'],
        int(mysql_config['master_port']),
        mysql_config['master_user'],
        mysql_config['master_password'],
        mysql_config['slave_host']
    )

    notifier = MySQLReplicationNotifier(checker, dingtalk_config)
    notifier.notify_if_replication_failed()

配置文件

[MySQL]
master_host=
master_port=3306
master_user=root
master_password=
slave_host=


[DingTalk]
#生产
prod_webhook_url = https://oapi..com/robot/send?access_token=
prod_secret=
#测试
dev_webhook_url= https://oapi.dingtalk.com/robot/send?access_token=
dev_secret=
mobile_number=

[Redis]
redis_master_host=
redis_master_port= 6379
redis_master_password =
redis_slave_host=

[Code]
mobile_phone_number =
signature_name = 
template_code =
master_monitor_address= http://:6666/user/getNewPublicKey?loginType=1
slave_monitor_address= http://:6666/user/getNewPublicKey?loginType=1

[sms]
phonenumbers =
signname = 
templatecode =