可以配置在Zabbix里面，作为监控的模版

#!/usr/bin/envpython#importMySQLdb,MySQLdb.cursorsimportsys,timefromdatetimeimportdatetimeinnodb_lock_output_file='/tmp/innodb_lock_output.log'#socket_dir='/var/lib/mysql/mysql.sock'time_step=1db_host='127.0.0.1'db_port=23306db_user='zabbix'db_pass='l8ka65'f=open(innodb_lock_output_file,'a')current_time_stamp=int(time.time())-time_stepcurrent_time=time.ctime()result=''#printsys.argviflen(sys.argv)<>2:print"Usage:%scurrent_lock|current_running"%sys.argv[0]exit()db=MySQLdb.connect(host=db_host,user=db_user,passwd=db_pass,charset='utf8',port=db_port#unix_socket=socket_dir)conn=db.cursor(MySQLdb.cursors.DictCursor)db.select_db('information_schema')now_time_sql='selectnow()asnow_time;'conn.execute(now_time_sql)current_time=conn.fetchall()[0]['now_time']result+=str(current_time)result+='\n'lock_sql='''SELECT*FROMINNODB_TRXwhereTIMESTAMPDIFF(SECOND,trx_started,now())>1ORDERBYtrx_startedLIMIT1'''running_sql='''selectuser,host,db,time,State,infofromPROCESSLISTwhereTIME>30andCOMMAND<>'Sleep'andCOMMAND<>'BinlogDump'anduser<>'systemuser'andlower(info)notlike'%alter%table%'orderbyTIMEDESCLIMIT1'''ifsys.argv[1]=='current_lock':conn.execute(lock_sql)query_result=conn.fetchall()locks=conn.rowcountiflocks>0:cur_time=datetime.now()print(cur_time-query_result[0]['trx_started']).secondselse:print0#printresultforiteminquery_result:foreachinitem:#printeachresult+=str(each)result+='\t'result+=':==>>>>\t'result+=str(item[each])result+='\n'result+='\n'result+='\n'#printresultiflocks>0:f.write(result)elifsys.argv[1]=='current_running':conn.execute(running_sql)query_result=conn.fetchall()thread_count=conn.rowcountifthread_count>0:f.write(result)foriteminconn.fetchall():f.write(str(item)+'\n')f.write('\n\n\n\n')printquery_result[0]['time']else:print0else:print"Usage:%scurrent_lock|current_running"%sys.argv[0]conn.close()db.close()f.close()

执行脚本

#pythoninnodb_lock_monitor.pycurrent_running#pythoninnodb_lock_monitor.pycurrent_lock

慢查询语句会记录在文本文件中

]#tail-300/tmp/innodb_lock_output.logblocking_trx_state:==>>>>RUNNINGrequesting_SQL:==>>>>deletewho_cart,who_cart_extfromwho_cartleftjoinwho_cart_extonwho_cart.rec_id=who_cart_ext.cart_idwherewho_cart.rec_id=1469638027