MySQL 5.6大查询和大事务监控脚本 Python 2
发布时间：2022-03-29 11:18:50 所属栏目：MySql教程 来源：互联网
导读：可以配置在Zabbix里面，作为监控的模版 #!/usr/bin/env python # import MySQLdb,MySQLdb.cursors import sys,time from datetime import datetime innodb_lock_output_file = /tmp/innodb_lock_output.log # socket_dir = /var/lib/mysql/mysql.sock time
 可以配置在Zabbix里面，作为监控的模版 #!/usr/bin/env python # import MySQLdb,MySQLdb.cursors import sys,time from datetime import datetime innodb_lock_output_file = '/tmp/innodb_lock_output.log' # socket_dir = '/var/lib/mysql/mysql.sock' time_step = 1 db_host = '127.0.0.1' db_port = 23306 db_user = 'zabbix' db_pass = 'l8ka65' f = open(innodb_lock_output_file,'a') current_time_stamp = int(time.time()) - time_step current_time = time.ctime() result = '' # print sys.argv if 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('nnnn') print query_result[0]['time'] else: print 0 else: 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.log blocking_trx_state :==>>>> RUNNING requesting_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 （编辑：包头站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐