需求
今天寫了個exporter監(jiān)控下剛投入使用的mysql主從的主機(jī)每窖。主要用到了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，寫了一個探測函數(shù)洼滚，如果不能連接到mysql囊颅，返回0.說明mysql可能出現(xiàn)問題了湾戳。
###參數(shù)字典
config = {
'host':'ip',
'port':port,
'user':'user',
'password':'pwd',
}
###獲取連接狀態(tài)
def connection_status():
try:
connection = pymysql.connect(**config)
connection_status = 1
connection.close()
return connection_status
except:
connection_status = 0
return connection_status
mysql的監(jiān)控參數(shù)主要通過show status獲得弟劲。利用pymysql執(zhí)行sql語句蟋软。取到參數(shù)值镶摘，并轉(zhuǎn)換成可讀性高的字典
###獲取mysql的狀態(tài)參數(shù)嗽桩，返回元組.如果連接不上，獲取的參數(shù)為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
###將獲取的參數(shù)元組轉(zhuǎn)換成可讀性高的字典
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的方式獲取參數(shù)的碌冶。所以用flask起了一個接口，返回gauge參數(shù)贡未。抓取了一些自己認(rèn)為重要的參數(shù)种樱，有待完善。
###起個flask接口,返回mysql的狀態(tài)參數(shù)查詢
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'])
###慢查詢的個數(shù)
mysqlSlow_queries.set(MS['Slow_queries'])
#連接
###客戶端連接數(shù)俊卤，當(dāng)前開放的連接
mysqlThreads_connected.set(MS['Threads_connected'])
###當(dāng)前運(yùn)行的連接
mysqlThreads_running.set(MS['Threads_running'])
###服務(wù)器醋無導(dǎo)致的失敗的連接數(shù)
mysqlConnection_errors_internal.set(MS['Connection_errors_internal'])
###嘗試與服務(wù)器連接失敗的個數(shù)
mysqlAborted_connects.set(MS['Aborted_connects'])
#緩沖池使用情況
###緩沖池中的總頁數(shù)
mysqlInnodb_buffer_pool_pages_data.set(MS['Innodb_buffer_pool_pages_data'])
###資源池?zé)o法滿足的請求
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展示抓取到的數(shù)據(jù)嫩挤，并設(shè)置告警
grafana展示
