python + promethues 监控mysql

需求

今天写了个exporter监控下刚投入使用的mysql主从的主机。主要用到了python的flask和pymysql模块。

程序

引入模块。没有的需要自己pip安装

# coding=utf-8
#author:OrangeLoveMlian
#!/bin/python
#coding=utf-8
import pymysql
import pymysql.cursors
import sys
import prometheus_client
from prometheus_client.core import CollectorRegistry
from prometheus_client import Gauge
from flask import Response,Flask

登陆mysql，写了一个探测函数，如果不能连接到mysql，返回0.说明mysql可能出现问题了。

###参数字典
config = {
    'host':'ip',
    'port':port,
    'user':'user',
    'password':'pwd',
    }

###获取连接状态

def connection_status():
    try:
        connection = pymysql.connect(**config)
        connection_status = 1
                connection.close()
        return connection_status
        
    except:
        connection_status = 0
        return connection_status

mysql的监控参数主要通过show status获得。利用pymysql执行sql语句。取到参数值，并转换成可读性高的字典

###获取mysql的状态参数，返回元组.如果连接不上，获取的参数为0，程序退出
def getPara():
    i = connection_status()
    if i == 1:
        connection = pymysql.connect(**config)
        cursor = connection.cursor()
        sql = "show status"
        try:
            
            cursor.execute(sql)
            statusPara =  cursor.fetchall()
            return statusPara

        except Exception,e:

            print 'execute failed!'
            sys.exit()
                        return e
    else:
        
        print 'connection failed!'
        sys.exit()
                return 0

###将获取的参数元组转换成可读性高的字典
def getMysqlStatusDic():

    tup = ()
    tup = getPara()
    tup = list(tup)
    for i in range(len(tup)):
        tup[i] = list(tup[i])
    statusDic = dict(tup)
    return statusDic

promethues使用pull的方式获取参数的。所以用flask起了一个接口，返回gauge参数。抓取了一些自己认为重要的参数，有待完善。

###起个flask接口,返回mysql的状态参数查询
app = Flask(__name__)
REGISTRY = CollectorRegistry(auto_describe=False)
##注册的值
mysqlConnect = Gauge("mysqlConnect","Value is:",registry=REGISTRY)
mysqlUptime = Gauge("mysqlUptime","Value is:",registry=REGISTRY)
mysqlSlow_queries = Gauge("mysqlSlow_queries","Value is:",registry=REGISTRY)
mysqlThreads_connected = Gauge("mysqlThreads_connected","Value is:",registry=REGISTRY)
mysqlThreads_running = Gauge("mysqlThreads_running","Value is:",registry=REGISTRY)
mysqlConnection_errors_internal = Gauge("mysqlConnection_errors_internal","Value is:",registry=REGISTRY)
mysqlAborted_connects = Gauge("mysqlAborted_connects","Value is:",registry=REGISTRY)
mysqlInnodb_buffer_pool_pages_data = Gauge("mysqlInnodb_buffer_pool_pages_data","Value is:",registry=REGISTRY)
@app.route("/metrics")
def mysqlStatus():
    MS = getMysqlStatusDic()
    ###mysql是否可以连接
    
    mysqlConnect.set(connection_status())
    
    ###mysql启动时间
    
    mysqlUptime.set(MS['Uptime'])
    
    ###慢查询的个数
        
    mysqlSlow_queries.set(MS['Slow_queries'])

    #连接
    ###客户端连接数，当前开放的连接
        
    mysqlThreads_connected.set(MS['Threads_connected'])
    ###当前运行的连接
        
    mysqlThreads_running.set(MS['Threads_running'])
    ###服务器醋无导致的失败的连接数
        
    mysqlConnection_errors_internal.set(MS['Connection_errors_internal'])
    ###尝试与服务器连接失败的个数
        
    mysqlAborted_connects.set(MS['Aborted_connects'])

    #缓冲池使用情况
    ###缓冲池中的总页数
    
    mysqlInnodb_buffer_pool_pages_data.set(MS['Innodb_buffer_pool_pages_data'])
    ###资源池无法满足的请求
        
    mysqlInnodb_buffer_pool_reads.set(MS['Innodb_buffer_pool_reads'])

    return Response(prometheus_client.generate_latest(REGISTRY),mimetype="text/plain")
    

if __name__ == "__main__":
    app.run(host='0.0.0.0',port=3534)

利用grafana展示抓取到的数据，并设置告警

grafana展示