grafana mysql监控 无数据 监控mysql服务

任务：应帅气的领导要求，需要监控生产环境mysql和mongo数据库服务。不仅要connect successful还要进行数据交互进一步确认数据库服务正常。

思路：

mysql和mongo 数据库ip、端口、用户名、密码、认证库(mongo)分别写在mysqldb_message.txt和mongodb_message.txt两个文件中。查询脚本db_test.py,邮件脚本sendEmail.py

.

.

格式如下：

mysqldb_message.txt

host:192.168.0.32 user:test passwd:123456 port:3306

host:192.168.0.222 user:test passwd:123456 port:3307

host:192.168.1.101 user:cctest passwd:Yj7netlkj port:3990

.

.

mongodb_message.txt

host:192.168.1.101 user:errorbook passwd:wangyue port:37017 authDB:admin

host:127.0.0.1 user:qingcong passwd:qingcong port:27017 authDB:admin

.

.

查询脚本db_test.py:

需要注意的是：

.

1、由于Python3.6，pymongo模块中的MongoClient函数连接mongo数据库时，超时参数connecttimeOutMS并不会生效(亲测),并且如果仅仅用MongoClient连接mongo数据库而不对数据库内的数据进行交互，那么无论MongoClient返回的是正确的连接对象还是发生了错误，Python3.6均不会报错。所以，必须要对数据库数据进行交互，才能确定数据库服务是否正常

.

2、由于mongo超时时间大概在30多秒左右，因此引入Python提供的超时函数timeout_decorator.timeout()，但此函数对不同的操作系统用法不一样。此脚本在windows环境下运行会报错。

#!/usr/bin/env python

#encoding: utf-8

#注意，需要在Linux环境下Python3以上版本执行，且需要安装pymysql、pymongo、timeout_decorator模块

import re

import time

import pymysql

import timeout_decorator

from pymongo import MongoClient

import sys

sys.path.append('/tools/scripts') # /tools/scripts脚本目录，导入进sys.path中为后续才能调用send_mail函数

from sendEmail import send_mail

#定义host、user、passwd、port、auth_db列表，用与存储数据库信息

host = []

user = []

passwd = []

port = []

auth_db = []

#mysql配置信息文件路径

mysql_file = '/tools/scripts/mysqldb_message.txt'

#data format: host:192.168.1.101 user:errorbook passwd:wangyue port:37017 authDB:admin

mongo_file = '/tools/scripts/mongodb_message.txt'

# data format: host:172.17.0.2 user:root passwd:123456 port:3306

#获取mysql_db的配置信息

def get_message_mysqldb():

print("读取mysql数据库详细信息文件中，请稍后....")

# time.sleep(1)

with open(mysql_file, 'r') as source:

lines = source.read().splitlines() # splitlines 去除空行，即不保留每行结尾的\n，否则读取的每行中末尾均有\n

i = 0

for char in lines:

if char.strip() != '': # 去掉字符串前后的空格

char = re.split('[ :]', lines[i]) # 以空格和分号作为空格符进行分割

num = 1

host.append(char[num])

user.append(char[num + 2])

passwd.append(char[num + 4])

port.append(char[num + 6])

i = i + 1

print("读取完成\n---------------------------")

return host, user, passwd, port

#获取mongodb_db的配置信息

def get_message_mongodb():

with open(mongo_file, 'r') as source:

lines = source.read().splitlines()

i = 0

for char in lines:

if char.strip() != '': # 去掉字符串前后的空格

char = re.split('[ :]', lines[i]) # 以空格和分号作为空格符进行分割

num = 1

host.append(char[num])

user.append(char[num + 2])

passwd.append(char[num + 4])

port.append(char[num + 6])

auth_db.append(char[num + 8])

i = i + 1

return host, user, passwd, port, auth_db

#连接mysql数据库

def mysqldb_connect_and_test(ip, user, passwd, port):

print("连接mysql数据库{0}中，请稍后....".format(ip))

# time.sleep(1)

try:

# print("ip:{0}, user:{1}, passwd:{2}, port:{3}".format(ip, user, passwd, port))

conn = pymysql.connect(host=ip,

user=user,

passwd=passwd,

port=int(port),

charset='utf8',

connect_timeout=3)

print("连接成功，执行测试语句中...")

with conn.cursor() as cur:

sql = 'select 1 from dual'

a = str(cur.execute(sql))

print("successful! 进一步确认数据库服务正常。执行结果-->a: {0}".format(a))

except Exception:

print("发生异常,数据库连接失败，服务器ip：{0}".format(ip), Exception)

send_mail("监控中心", ["吴青聪"], [], "测试email", "(测试)邮件内容：python检测到mysql数据库异常，服务器为{0}".format(host), "")

else:

print("连接成功：{0}\n".format(ip))

#连接mongodb数据库

@timeout_decorator.timeout(3)

def mongodb_connect_and_test(ip, user, passwd, port, auth_db):

print("请稍等，连接mongodb中...")

try:

client = MongoClient(ip, int(port))

# 利用server_info()判断mongodb状态

dbnames = client.server_info() #

print(dbnames)

#利用authenticate判断mongodb状态

db = client[auth_db]

result = db.authenticate(user, passwd)

except Exception as es:

print("Error！连接失败，服务器ip：{0}".format(ip), es)

send_mail("监控中心", ["吴青聪"], [], "测试email", "(测试)邮件内容：python检测到mongo数据库异常，服务器为{0}".format(host), "")

else:

print("Successful！连接mongodb成功，测试语句执行成功")

client.close()

#清空列表变量

def clear_list():

host.clear()

user.clear()

passwd.clear()

port.clear()

def main():

# 检查mysql

print("检查mysql数据库：")

get_message_mysqldb()

index = 0

for ip in host:

print("------------------------")

mysqldb_connect_and_test(ip, user[index], passwd[index], port[index])

index = index + 1

clear_list() # 清空变量列表

#检查mongodb

print("\n检查mysql数据库：")

get_message_mongodb()

index = 0

for ip in host:

print("------------------------")

# print("ip:{0}, user:{1}, passwd:{2}, port:{3}".format(ip, user[index], passwd[index], port[index]))

mongodb_connect_and_test(ip, user[index], passwd[index], port[index], auth_db[index])

index = index + 1

if __name__ == '__main__':

main()

发送邮件脚本sendEmail.py:

#!/usr/bin/env python

#encoding: utf-8

import smtplib # 加载smtplib模块

import traceback

from email.header import Header

from email.mime.application import MIMEApplication

from email.mime.multipart import MIMEMultipart

from email.mime.text import MIMEText

from email.utils import parseaddr, formataddr

login_name = '[e-mail]' # 发件人邮箱账号，为了后面易于维护，所以写成了变量

login_pass = '******' # 邮箱密码，此处隐藏^_^

smtp_port = 465

def _format_addr(s):

name, addr = parseaddr(s)

return formataddr(( \

Header(name, 'utf-8').encode(), \

addr))

# addr.encode('utf-8') if isinstance(addr, unicode) else addr))

def send_mail(sender, recps, Ccs, subject, htmlmsg, fileAttachment):

# 参数分别是：发送人邮箱、收件人邮箱、抄送人邮箱、主题、内容、附件，如果看不懂此处代码，知道如何使用即可

smtpserver = 'smtp.exmail.qq.com'

receivers = recps + Ccs

try:

# msg = MIMEText(htmlmsg, 'html', 'utf-8')

msg = MIMEMultipart()

msg.attach(MIMEText(htmlmsg, 'html', 'utf-8'))

# msg['Subject'] = subject

msg['Subject'] = Header(subject, 'utf-8').encode()

# msg['From'] = sender

msg['From'] = _format_addr(sender)

Recp = []

for recp in recps:

Recp.append(_format_addr(recp))

ccs = []

for cc in Ccs:

ccs.append(_format_addr(cc))

msg['To'] = ','.join(Recp)

msg['Cc'] = ','.join(ccs)

# if fileAttachment!='' :

# # 附件

for file in fileAttachment:

part = MIMEApplication(open(file, 'rb').read())

attFileName = file.split('/')[-1]

part.add_header('Content-Disposition', 'attachment', filename=attFileName)

msg.attach(part)

# part = MIMEApplication(open(fileAttachment, 'rb').read())

# part.add_header('Content-Disposition', 'attachment', filename=fileAttachment)

# msg.attach(part)

smtp = smtplib.SMTP_SSL()

smtp.connect(smtpserver, smtp_port)

smtp.login(login_name, login_pass)

# smtp.login(username, password)

smtp.sendmail(sender, receivers, msg.as_string())

smtp.quit()

print('SendEmail success')

except:

traceback.print_exc()