MySQL 5.6大查询和大事务监控脚本(Python 2)

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

#!/usr/bin/env python#import MySQLdb,MySQLdb.cursorsimport sys,timefrom datetime import datetimeinnodb_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 = ''# print sys.argvif len(sys.argv) <> 2:    print "Usage: %s current_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 = 'select now() as now_time;'conn.execute(now_time_sql)current_time = conn.fetchall()[0]['now_time']result += str(current_time)result += '\n'lock_sql = '''    SELECT * FROM INNODB_TRX where TIMESTAMPDIFF(SECOND, trx_started, now()) > 1 ORDER BY trx_started LIMIT 1    '''running_sql = '''select user,host,db,time,State,info  from PROCESSLIST where TIME > 30 and  COMMAND  <> 'Sleep' and COMMAND <> 'Binlog Dump' and user <> 'system user' and lower(info) not like '%alter%table%' order by TIME DESC LIMIT 1 '''if sys.argv[1] == 'current_lock':    conn.execute(lock_sql)    query_result = conn.fetchall()    locks = conn.rowcount    if locks > 0:        cur_time = datetime.now()        print (cur_time - query_result[0]['trx_started']).seconds    else:        print 0    # print result    for item in query_result:        for each in item:            # print each            result +=  str(each)            result += '\t'            result += ':==>>>>\t'            result += str(item[each])            result += '\n'        result += '\n'    result += '\n'    # print result    if locks > 0:        f.write(result)elif sys.argv[1] == 'current_running':    conn.execute(running_sql)    query_result = conn.fetchall()    thread_count = conn.rowcount     if thread_count > 0 :        f.write(result)        for item in conn.fetchall():            f.write(str(item) + '\n')        f.write('\n\n\n\n')        print query_result[0]['time']    else:        print 0else:    print "Usage: %s current_lock | current_running" % sys.argv[0]conn.close()db.close()f.close()

执行脚本

# python innodb_lock_monitor.py current_running# python innodb_lock_monitor.py current_lock

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

]# tail -300 /tmp/innodb_lock_output.logblocking_trx_state      :==>>>>     RUNNINGrequesting_SQL  :==>>>>     delete who_cart,who_cart_ext from who_cart left join who_cart_ext on who_cart.rec_id = who_cart_ext.cart_id         where  who_cart.rec_id=1469638027