python检测mysql状态脚本--参考

ykuj

#!/usr/bin/env python
#encoding:utf-8
import MySQLdb
import paramiko
import sys,os
from email.mime.text import MIMEText
from email.header import Header
import smtplib
db1_ip="172.17.2.51"
db2_ip="172.17.2.52"
num = ''
mmm_info = ''
IoS = ''
SqS = ''
Sec = ''
e = ''

def db_info(ip):
  global IoS
  global SqS
  global Sec
  global e
  try:
    conn = MySQLdb.connect(host=ip,user='test',passwd='123456',port=7000,charset='utf8')
    cur = conn.cursor()
    cur.execute('show slave status')
    for n in cur.fetchall():
      IoS=n[10]
      SqS=n[11]
      Sec=n[32]
    cur.close()
    conn.close()
  except MySQLdb.Error,e:
    print "MySQLdb Error",e
     

def send_mail(INFO):
  to = "494379480@qq.com"
  user = "liyiliangyw@feinno.com"
  passwd = "xxxxxx"
  subject = '辽宁移动企业通信录数据库故障警告'
  mail_host = "smtp.feinno.com"
  port = '587'
  content=INFO
  msg = MIMEText(content,_subtype='plain',_charset='utf-8')
  msg['To'] = to
  msg['from'] = user
  msg['Subject'] = Header(subject, 'utf-8')
  s = smtplib.SMTP()
  s.connect(mail_host,port)
  s.login(user,passwd)
  s.sendmail(user,to,msg.as_string())
  s.close()
   

def mmm_status():
  global num
  global mmm_info
  host=db2_ip
  user='root'
  password='xxxxxx'
  cmd='mmm_control show'
  s = paramiko.SSHClient()
  s.load_system_host_keys()
  s.set_missing_host_key_policy(paramiko.AutoAddPolicy())
  s.connect(host,22,user,password,timeout=1)
  stdin,stdout,stderr = s.exec_command(cmd)
  cmd_result = stdout.read(),stderr.read()
  mmm_info=str(list(cmd_result)[0])
  
def run(IP):
  mmm_status()
  db_info(IP)
  if e == "":
    if IoS != "Yes" or SqS != "Yes":
      DB_info='%s replicaiton error! \nSlave_IO_Running:%s\nSlave_SQL_Running:%s\nSeconds_Behind_Master:%s\n''\n%s' %(IP,IoS,SqS,Sec,mmm_info)  
      send_mail(DB_info)
  else:
    DB_info='%s mysql_db is not ok ,info:%s' %(IP,e)
    send_mail(DB_info)
     
     
run(db1_ip)
run(db2_ip)