使用python监控阿里云rds mysql运行情况

主要功能： 监控rds运行情况，并发送到钉钉群里
监控内容包括：

• rds 磁盘、内存、CPU等信息
• 慢sql查询统计情况

# -*- coding: utf-8 -*-
# title        :  rdsInfo
# description  :  获取rds相关信息
# author       :  qianyulin
# email        :  qianyulin777@163.com
# date         :  2021/12/17 9:59 上午
# version      :  1.0
# usage        :  python3 RdsControlInfo.py
# python_version: 3.7.2
# ======================================================================================================================================================================================

from alibabacloud_rds20140815.client import Client as Rds20140815Client
from alibabacloud_tea_openapi import models as open_api_models
from alibabacloud_rds20140815 import models as rds_20140815_models
import datetime
import requests
import json
import pandas as pd

class RdsControlInfo():
    def __init__(self):
        self.access_key_id = ''
        self.access_key_secret = ''
        self.dbinstance_id = ''  # 实例id
        self.start_time = (datetime.datetime.utcnow()-datetime.timedelta(hours=1)).strftime("%Y-%m-%dT%H:%MZ")
        self.end_time = datetime.datetime.utcnow().strftime("%Y-%m-%dT%H:%MZ")
        self.access_token = '' #钉钉群token

    @staticmethod
    def get_rds_connect(self):
        """
        获取rds的连接
        :param self:
        :return:
        """
        config = open_api_models.Config(access_key_id=self.access_key_id, access_key_secret=self.access_key_secret)
        config.endpoint = 'rds.aliyuncs.com'
        return  Rds20140815Client(config)

    def get_rds_information(self):
        """
        获取rds 磁盘、内存、CPU等信息
        :return:
        """
        client = self.get_rds_connect(self)
        describe_dbinstance_attribute_request = rds_20140815_models.DescribeDBInstanceAttributeRequest(dbinstance_id=self.dbinstance_id)
        rdsinfoDict = eval(str(client.describe_dbinstance_attribute(describe_dbinstance_attribute_request).body))
        rdsJson = rdsinfoDict['Items']['DBInstanceAttribute'][0]
        return {'DBInstanceStatus':rdsJson['DBInstanceStatus'],'DBInstanceMemory':rdsJson['DBInstanceMemory'],'DBInstanceStorage':rdsJson['DBInstanceStorage'],'DBInstanceCPU':rdsJson['DBInstanceCPU']}


    def get_rds_diskAndcpurate(self):
        """
        MySQL_MemCpuUsage  # 实例CPU使用率（占操作系统总数）和MySQL实例内存使用率（占操作系统总数）。
        MySQL_DetailedSpaceUsage  # 实例总空间使用量、数据空间、日志空间、临时空间和系统空间。
        MySQL_Sessions 当前活跃连接数，当前总连接数。
        MySQL_QPSTPS 平均每秒SQL语句执行次数，平均每秒事务数
        """
        useinfolist = []
        client = self.get_rds_connect(self)
        describe_dbinstance_performance_request = rds_20140815_models.DescribeDBInstancePerformanceRequest(dbinstance_id=self.dbinstance_id,
            key='MySQL_MemCpuUsage,MySQL_DetailedSpaceUsage,MySQL_Sessions,MySQL_QPSTPS',
            start_time=self.start_time,end_time=self.end_time
        )
        rdsInfoList = eval(str(client.describe_dbinstance_performance(describe_dbinstance_performance_request).body))['PerformanceKeys']['PerformanceKey']
        for baseinfo in rdsInfoList :
            useinfolist.append({'key':baseinfo['Key'],'ValueFormat':baseinfo['ValueFormat'],'Value':list(baseinfo['Values']['PerformanceValue'])[-1]['Value']})
        return useinfolist


    # TODO 慢sql查询统计情况
    def get_rds_slowsql(self):
        """
        web: https://next.api.aliyun.com/api/Rds/2014-08-15/DescribeSlowLogs?params={}
        :return: 慢sql top 10
        """
        start_time = str(datetime.datetime.now().strftime('%Y-%m-%d')) + 'Z'
        client = self.get_rds_connect(self)
        describe_slow_logs_request = rds_20140815_models.DescribeSlowLogsRequest(
            start_time= start_time,
            end_time= start_time ,
            dbinstance_id=self.dbinstance_id,
            page_size=100
        )
        result = eval(str(client.describe_slow_logs(describe_slow_logs_request)))
        sqlTextList = result['body']['Items']['SQLSlowLog']
        sqlDF = pd.DataFrame(sqlTextList)
        sqlDF = sqlDF.sort_values(by = ['MaxExecutionTime'],ascending=False)
        sqlDF = sqlDF.head(10)
        sqlcontent = ''
        for MaxExecutionTime,SQLText in zip(sqlDF['MaxExecutionTime'],sqlDF['SQLText']):
            sqlcontent = sqlcontent + '运行时长：'+ str(MaxExecutionTime) +'s  SQL:' +  SQLText + '\n'

        return sqlcontent

    def send_ding_msg(self,content):
        """
        发送信息到钉钉群里
        :param content:
        :return:
        """
        headers = {'Content-Type': 'application/json'}
        webhook = "https://oapi.dingtalk.com/robot/send?access_token=" + self.access_token
        data = {
            "msgtype": "text",
             "text": {"content": content + '\n'},
             "isAtAll": False
        }
        x = requests.post(url=webhook, data=json.dumps(data), headers=headers)
        if x.status_code == 200:
            print('发送消息成功')
        else:
            print('发送消息失败')


    def rdsBaseInfo(self):
        """
        :return:  rdsbaseinfostr:rds的基本信息
        """
        result_list = []
        rdsinfo = self.get_rds_information()
        otherinfo = self.get_rds_diskAndcpurate()
        result_list.append({'key': 'rds实例状态：', 'value': rdsinfo['DBInstanceStatus']})
        for x in otherinfo:
            if x['key'] == 'MySQL_DetailedSpaceUsage':
                useDisk = float(str(x['Value']).split('&')[0])
                allDisk = float(rdsinfo['DBInstanceStorage']) * 1024
                result_list.append({'key': '实例磁盘使用量：', 'value': str(useDisk) + 'MB'})
                result_list.append({'key': '实例磁盘使用率：', 'value': str(round((useDisk / allDisk) * 100, 2)) + '%'})
            elif x['key'] == 'MySQL_MemCpuUsage':
                cpuRate = str(x['Value']).split('&')[0]
                menRate = str(x['Value']).split('&')[1]
                result_list.append({'key': '实例CPU使用率：', 'value': cpuRate + '%'})
                result_list.append({'key': '实例内存使用率：', 'value': menRate + '%'})
            elif x['key'] == 'MySQL_QPSTPS':
                qps = str(x['Value']).split('&')[0]
                result_list.append({'key': '平均每秒SQL语句执行次数：', 'value': qps})
            else:
                activate_session = str(x['Value']).split('&')[0]
                result_list.append({'key': '实例活跃连接数：', 'value': activate_session})
        df = pd.DataFrame(result_list)
        curtime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

        rdsbaseinfostr = '监控时间:' + curtime + '\n' + '监控数据库:' + 'sour_db' + '\n'
        for y in range(len(df['key'])):
            rdsbaseinfostr = rdsbaseinfostr + '\n' + df['key'][y] + df['value'][y]

        return rdsbaseinfostr + '\n' + '慢SQl前10：'+ '\n'

    def run(self):
        """
        运行函数
        :return:
        """
        rdsbasecontent = self.rdsBaseInfo()
        sqlcontent = self.get_rds_slowsql()
        content = rdsbasecontent +  sqlcontent
        self.send_ding_msg(content)

if __name__ == '__main__':
    rdsclass = RdsControlInfo()
    rdsclass.run()