用python监控mysql数据库是否可写

监控数据库是否可写，如果你的监控脚本逻辑是，写入数据库成功后显示成功，反之显示不成功然后报警。那么难题来了，数据库真的无法写入了，你的监控脚本的写入命令也会被mysql hang住，一直卡在那里，直到天荒地老，根本无法实现报警。那换个思路，如果设置个超时时间，是不是更好。

#!/usr/bin/env python# -*-coding:utf8-*-import MySQLdbimport reimport smtplibimport jsonfrom email.mime.text import MIMETextimport sysimport timeimport multiprocessingreload(sys)sys.setdefaultencoding('utf8')def mysql_select(sql, pipe):    try:        conn = MySQLdb.connect(host='xxx.xxx.xxx.xxx',user='xxxx',passwd='xxxx',db='xxxx',port=xxxx,charset='utf8',connect_timeout=10)        cursor = conn.cursor()        cursor.execute(sql)        result = cursor.fetchall()        cursor.close()        conn.commit()        conn.close()        pipe.send('successful')         except Exception,e:        pipe.send("zabbix 数据库异常: %s" % e) def query_with_timeout(sql):    pipe_out, pipe_in = multiprocessing.Pipe(False)    subproc = multiprocessing.Process(target=mysql_select,args=(sql, pipe_in))    subproc.start()    subproc.join(timeout=3)        if pipe_out.poll():        ex_c = pipe_out.recv()        else:        ex_c = "zabbix 数据库无法写入"    subproc.terminate()        #raise Exception("Query %r ran for >%r" % (sql, 5))    raise Exception(ex_c)###    def se_mail(mail_result):    now_time = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))    sys.setdefaultencoding('utf-8')    SUBJECT = "数据库监控"    TO = "xxxx@ptthink.com"    FROM = "ooooo@ptmind.com"    msg = MIMEText("""                                                            
                    
                       
中国区zabbix数据库监控
                    
                         
告警区域
                         
中国
                    
                    
                         
主机名称
                         
xxx.xxx.xxx.xxx
                    
                    
                         
告警项目
                         
zabbix数据库监控
                    
                    
                         
告警级别
                         
严重
                    
                    
                         
告警状态
                         
PROBLEM
                    
                    
                         
详细内容
                         
""" + mail_result + """
                    
                         
发生时间
                         
""" + now_time + """
                    
                
                    ""","html","utf-8")    msg['Subject'] = SUBJECT    msg['From']=FROM    msg['To']=TO        try:        server = smtplib.SMTP('localhost')        server.sendmail(FROM, TO, msg.as_string())        server.quit()                print "邮件发送成功！"    except Exception, e:                print "失败："+str(e)        ###    if __name__ == '__main__':        #创建监控数据库连接，与是否可写，的监控表，下面是创建语句    #sql_user_info = """    #CREATE TABLE IF NOT EXISTS db_check_table (    #itemid INT(20),    #applicationid INT(20),    #hostid INT(20),    #name  VARCHAR(255),    #du_name  VARCHAR(255),    #item_name   VARCHAR(255)    #)    #"""    insert_sql = """insert into db_check_table values ('10211','13564','456789','test-172.5.6.7','cpu','cpu ldie')"""       try:        query_with_timeout(insert_sql)        except Exception,e:        mail_result = str(e)                if mail_result != "successful" :            se_mail(mail_result)