Mysql主从监控python脚本

脚本通过监控mysql slave status中的Slave_IO_Running,Slave_SQL_Running,Seconds_Behind_Master三个状态参数来判断replication的健康状况,通过邮件发送报警信息 –2015.7.24优化– 实际发现每天都有几次Seconds_Behind_Master的值大于0引发报警的情况,所以将脚本修改为如果连续两次Seconds_Behind_Master大于0才报警,如下:

#!/usr/bin/python
# -*- coding: cp936 -*-

import time
import datetime
import string
import MySQLdb
import smtplib
from email.Header import Header
from email.MIMEText import MIMEText
import urllib


def sendMail(ip, ioStatus, sqlStatus, lastErrNo, lastError, masterLogFile, masterLogFilePos, relayLogFile, relayLogFilePos, relayMasterLogFile, secondsBehindMaster):

    mailto_list = ["abc@xyz.com"]
    mail_host = "mail.xyz.com"
    mail_user = "mon@xyz.com"
    mail_pass = "passwd"
    mail_from = "<mon@xyz.com>"
    mail_postfix = "xyz.com"
    body = ip + " mysql slave status:\n\n IO Thread Status: " + ioStatus + "\n SQL Thread Status: " + sqlStatus + "\n LastErrorNo: " + str(lastErrNo)
    body = body + "\n LastError: " + str(lastError) + "\n Master Log File: " + str(masterLogFile) + "\n Read Master Log File Pos: " + str(masterLogFilePos)
    body = body + "\n Relay Log File: " + relayLogFile + "\n Relay Log File Pos: " + str(relayLogFilePos) +  "\n Relay Master Log File: " + relayMasterLogFile
    body = body + "\n Seconds_Behind_Master: " + str(secondsBehindMaster)

    mail_subject = "(" +ip + ") MYSQL REPLICATION ERROR!!!"
    me = mail_from+"<"+mail_user+">"
    msg = MIMEText(body,_subtype='plain',_charset='gb2312')
    msg['Subject'] = mail_subject
    msg['From'] = mail_from
    msg['To'] = ";".join(mailto_list)
    try:
        s = smtplib.SMTP()
        s.connect(mail_host)
        s.login(mail_user,mail_pass)
        s.sendmail(me, mailto_list, msg.as_string())
        s.close()
        return True
    except Exception, e:
        print str(e)
        return False

def check(ip, username, pwd):
    conn = MySQLdb.connect(host=ip, user=username, passwd=pwd,use_unicode=True,charset='utf8')
    cur = conn.cursor()
    cur.execute("show slave status")
    conn.commit()
    rows = cur.fetchall()

    ioStatus = ""
    sqlStatus = ""
    lastErrNo = 0
    secondsBehindMaster = 0

    logfile = "/var/log/mysql_db_replication_mon_" + str(ip) + ".log"
    if os.path.isfile(logfile):
        #print logfile + ' is exists.'
        cmd = "sed -n '$p' " + logfile + "|awk -F ':' '{print $6}'"
        secondsBehindMaster_last = int(os.popen(cmd).read())
    else:
        print logfile + ' is not exists, I created it.'
        initf = open(logfile,'w')
        initf.close()
        secondsBehindMaster_last = 0

    if len(rows) > 0:
        ioStatus = rows[0][10]
        sqlStatus = rows[0][11]
        lastErrNo = rows[0][18]
        secondsBehindMaster = rows[0][32]
        today = (datetime.date.today().isoformat())
        timestamp = datetime.datetime.strftime(datetime.datetime.today(),"%y%m%d%H%M%S")

    if "Yes" == ioStatus and "Yes" == sqlStatus:
        print >> open(logfile,'a+'), timestamp, ip + ": ioStatus:" + ioStatus + "; sqlStatus:" + sqlStatus + "; lastErrNo:" + str(lastErrNo) + "; Seconds_Behind_Master:" + str(secondsBehindMaster)
        open(logfile,'a+').close()
        if 0 == secondsBehindMaster or 0 == secondsBehindMaster_last:
            cur.close()
            conn.close()
            return

    if "No" == ioStatus and lastErrNo == 23:
        cur.execute("stop slave")
        conn.commit()
        cur.execute("start slave")
        conn.commit()
    cur.close()
    conn.close()
    lastError=rows[0][19]
    masterLogFile=rows[0][5]
    masterLogFilePos=rows[0][6]
    relayLogFile=rows[0][7]
    relayLogFilePos=rows[0][8]
    relayMasterLogFile=rows[0][9]

    try:
        sendMail(ip, ioStatus, sqlStatus, lastErrNo, lastError, masterLogFile, masterLogFilePos, relayLogFile, relayLogFilePos, relayMasterLogFile, secondsBehindMaster)
    except Exception, e:
        print str(e)
        pass

def main():
    SLAVE_ARRAY = ["192.168.0.1", "192.168.0.2", "192.168.0.3"]
    for slave in SLAVE_ARRAY:
        check(slave, "repuser", "reppasswd")

if __name__ == "__main__":
    main()

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注