监控mysql和mongo数据库服务脚本

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

代码如下：

#!/bin/bash

#author:吴青聪

#email：wuqingcong@aliyun.com

#encoding: utf-8

#声明四个数据，存放数据库信息 ip、用户、密码、端口

declare -a host

declare -a user

declare -a passwd

declare -a port

#定义一个计数变量，初始值 0

n=0

#指明收件邮箱

mail=wuqingcong@aliyun.com

#按行读取记录了数据地址端口用户名密码信息的文件，存放进对应数组，此处文件为mysqldb_message.txt，其格式如下：

#host:192.168.0.32 user:test passwd:123456 port:3306 注意行首无空格

while read line

do

eval $(echo $line | awk -F"[: ]" '{printf("host[$n]=%s; user[$n]=%s; passwd[$n]=%s; port[$n]=%s",$2,$4,$6,$8)}')

let n++

done < mysqldb_message.txt

#检测数据库服务

let n–

for i in $(seq 0 $n);do

# 检测命令

mysql -h${host[$i]} -u${user[$i]} -p${passwd[$i]} -P${port[$i]} -e "select 1 from dual;" –connect-timeout=5 &> /dev/null

#判断执行结果$?,为0执行成功，为1出现错误异常

if [ $? -ne 0 ]

then

python3 mail.py $mail "mysqldb down " "please check mysql-service on ${host[$i]}"

echo "mysql$i is down"

else

# python3 mail.py $mail "mysqldb is fine " "Do not need check mysql-service on ${host[$i]} "

echo "mysql$i is fine"

fi

done

#检测mongodb

#清空计数变量

n=0

#按行读取记录了数据地址端口用户名密码信息的文件，存放进对应数组，此处文件为mongodb_message.txt，其格式如下：

#host:127.0.0.1 user:root passwd:root port:27017 authDB:admin 注意行首无空格

while read line

do

eval $(echo $line | awk -F"[: ]" '{printf("host[$n]=%s; user[$n]=%s; passwd[$n]=%s; port[$n]=%s; db[$n]=%s",$2,$4,$6,$8,$10)}')

let n++

done < mongodb_message.txt

let n–

for i in $(seq 0 $n);do

echo "show tables maxTimeMS(5000)" | mongo ${host[$i]}:${port[$i]}/${db[$i]} -u ${user[$i]} -p ${passwd[$i]} &> /dev/null

if [ $? -ne 0 ]

then

server=${host[$i]}

python3 mail.py $mail "mongodb down " "please check mysql-service on $server"

echo "mongodb$i is down"

else

# python3 mail.py $mail "mongodb is fine " "Do not need check mysql-service on ${host[$i]}"

echo "mongodb$i is fine"

fi

done

邮件脚本为参考他人脚本，用Python编写，存放上述代码脚本同目录即可，邮件脚本代码如下：

#!/usr/bin/env python

#

# encoding: utf-8

import sys

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 = 'monitor@7net.cc' # 发件人邮箱账号，为了后面易于维护，所以写成了变量

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()

def main():

to=sys.argv[1]

subject=sys.argv[2]

content=sys.argv[3]

# send_mail("监控中心", ["吴青聪"], [], subject, "邮件内容2", "")

send_mail("监控中心", [to], [], subject, content, "")

if __name__ == "__main__":

main()