Python监控Mysql主从复制

一、DockerFile

FROM python:3.8.6-alpine
ENV LANG=C.UTF-8

COPY requirements.txt /requirements.txt
RUN sed -i 's/dl-cdn.alpinelinux.org/mirrors.aliyun.com/g' /etc/apk/repositories \
    && apk update \
    && apk add  --virtual build-deps gcc python3-dev musl-dev \
    && apk add --no-cache mariadb-dev \ 
    && pip install -i https://mirrors.aliyun.com/pypi/simple --upgrade pip \
    && pip install -i https://mirrors.aliyun.com/pypi/simple --no-cache-dir -r /requirements.txt \
    && rm -f /requirements.txt

COPY mysql_rep_monitor.py /mysql_rep_monitor.py

RUN cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime \
    && apk del tzdata \
    && rm -rf /var/lib/apt/lists/* \
    && rm /var/cache/apk/*

WORKDIR /

ENTRYPOINT ["python", "/mysql_rep_monitor.py"]

二、requirements.txt

requests
ConfigParser
pymysql
mysqlclient   //主要包含该项，python 才能调用MySQLdb模块

三、Mysql主从监控报警[mysql_rep_monitor.py]

#!/usr/bin/env python
#_*_ coding:utf8 _*_
#author:dengyoucheng
#用于监控MySQL主从复制状态

import os,time
import json
import os.path
import requests
import MySQLdb
import logging

DB_HOST = os.environ.get('DB_HOST')
DB_USER = os.environ.get('DB_USER')
DB_PASSWORD = os.environ.get('DB_PASSWORD')
DB_PORT = os.environ.get('DB_PORT')
TOKEN = os.environ.get('TOKEN')


#记录日志
logging.basicConfig(
    filename = '/log/MySQL_replication_monitor.log',
    filemode = 'a',
    format = '%(asctime)s %(filename)s[line:%(lineno)d] %(funcName)s %(levelname)s %(message)s',
    datefmt='%a, %d %b %Y %H:%M:%S',
    level = logging.INFO
)

def monitor_MySQL_replication():
    #用于监控MySQL主从复制状态，异常则告警
    message=''
    try:
        conn = MySQLdb.connect(host=DB_HOST,user=DB_USER,passwd=DB_PASSWORD,port=int(DB_PORT))
        cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
        cursor.execute('SHOW SLAVE STATUS;')
        result = cursor.fetchone()
        if result['Slave_IO_Running'] == "Yes" and result['Slave_SQL_Running'] == "Yes":
            logging.info('MySQL master/slave replication status is successfully')
            if result['Seconds_Behind_Master'] >= 100:
                loggin.Warring('MySQL master/slave replication synchronization delay exceeds 100s')
                message='Mysql主从复制延迟，延迟时间超过100s'
            else:
                logging.info('MySQL master/slave replication status is successfully')
        else:
            logging.error('MySQL Master/Slave replication fail,Please check it')
            message='Mysql主从复制失败，请检查！'
    except Exception as e:
        logging.error(e)
        message='Mysql从库连接异常，请检查！'
    return message

def send_warnings(content,ip):
    #发送RTX告警给业务负责人
    webhook = "https://oapi.dingtalk.com/robot/send?access_token={}".format(TOKEN)
    data = {
            "msgtype": "markdown",
            "markdown": {
                "title": "Mysql主从复制",
                "text": "Mysql主从复制异常\n \n> **IP地址**:   {}\n \n> **content**:   {} \n \n> ".format(ip,content)
            },
            "at": {
                "atMobiles": [
                    ""
                ],
                "isAtAll": True
            }
        }
    print (data)
    headers={'Content-Type': 'application/json;charset=utf-8'}
    req = requests.post(webhook, data=json.dumps(data),headers=headers)
    req.encoding = 'utf-8'
    return req

def clean_log():
    #清理日志文件，当文件的大于100M时，则清理该文件,防止日志占用过多空间
    log_file = '/log/MySQL_replication_monitor.log'
    size = os.path.getsize(log_file) / (1024 * 1024)
    if size >= 100:
        os.remove(log_file)
        logging.info('%s have been remove' % (log_file))

if __name__ == "__main__":
    while True:
        clean_log()
        content=monitor_MySQL_replication()
        if len(content):
            send_warnings(content=content,ip=DB_HOST)
        time.sleep(1800)

四、启动参数【env.env】

DB_HOST=192.168.3.151
DB_USER=root
DB_PASSWORD=
DB_PORT=3306
TOKEN=ac146360b52ffeb717fc0f6b322a2f3194ea3b780fc333747b43445250bcbf89

注意：传入的环境变量，不需要用引号

五、启动脚本

#!/bin/bash
docker run -d --name mysql_rep_monitor --restart=always --env-file=./env.env  -v /data/mysql_repo_logs:/log -v /var/run/docker.sock:/var/run/docker.sock mysql_repo_monitor:latest

如有侵权或技术交流请联系作者！

#Author : mayi

#wchat : a403182580