Go to: Python monitoring script for MySQL master-slave, dictionary deadlock, connection number

#! / usr/bin/python # MySQL master / slave centralized monitoring script # Please execute the following statement on MySQL to authorize a db_monitor account on the machine running the script. # grant SHOW VIEW,REPLICATION CLIENT,SHOW DATABASES,PROCESS on *. * to db_monitor@'192.168.1.10' Import MySQLdb import os import datetime # sends a string alarm, which is unique to the company and can be changed to other monitoring methods, such as SMS or email def report_alarm (attr, err_msg): os.system ('/ usr/local/agenttools/agent/agentRepStr% s "% s" > / dev/null'% (attr, err_msg)) # report a numerical alarm It is also a unique alarm system def send_repnum (attr, num): os.system ('/ usr/local/agenttools/agent/agentRepNum% s "% s" > / dev/null'% (attr, num)) def monitor (db_host): try: cxn=MySQLdb.connect (db_host, 'db_monitor',', db='mysql') except MySQLdb.Error, e: try: sqlError = "Error% dvv% s"% (e.args [0]) E.args [1]) except IndexError: sqlError = "MySQL Error:%s"% str (e) report_alarm (36320, sqlError) # get mysql version cur=cxn.cursor () cur.execute ('select version ()') for item in cur.fetchall (): if '5.0.' In item [0]: mysql_version = 'low' else: mysql_version =' high' # show slave status cur=cxn.cursor () cur.execute ('show slave status') for item in cur.fetchall (): replicate_error = item [19] if mysql_version =' low': seconds_behind_master = item [- 1] else: seconds_behind_master = item [32] # show processlist cur=cxn.cursor () cur.execute ('show processlist') connect_num = len (cur.fetchall () ) meta_lock_num = cur.fetchall () .count ("meta lock") cxn.close () return replicate_error Seconds_behind_master,connect_num,meta_lock_num print datetime.datetime.now () for host in open ('% hosts.listings% os.path.dirname (os.path.realpath (_ _ file__): host = host.strip ('\ n') replicate_error =''seconds_behind_master = 0 connect_num = 0 meta_lock_num = 0 replicate_error,seconds_behind_master,connect_num Meta_lock_num = monitor (host) try: Seconds_behind_master = int (seconds_behind_master) except: Seconds_behind_master = seconds_behind_master print'= 'print' Infomation of% s'% host print "The Num of metalock:% s"% meta_lock_num print "The Num of connection:% s"% connect_num # report current connections send = send_repnum (36713, connect_num) # number of reported dictionary locks send = send_repnum (36715 Meta_lock_num) if len (replicate_error) > 0: print replicate_error # if there is a problem with master-slave synchronization Send alarm report_alarm (36320,'% s: Replicate error'% host) elif Seconds_behind_master > 600: print "Seconds behid master:% s"% Seconds_behind_master # if the master-slave delay exceeds 600s, send alarm report_alarm (36320,'% s: Seconds behind master ge 600'% host) # else: # print host + "is OK!" Print'= ='