MySQL 5.6大查詢和大事務監控指令碼(Python 2)
可以配置在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('\n\n\n\n') 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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26506993/viewspace-2215359/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysql監控指令碼MySql指令碼
- python監控mysql主從指令碼PythonMySql指令碼
- shell指令碼:監控MySQL服務是否正常指令碼MySql
- shell指令碼:監控MySQL服務是否正常薦指令碼MySql
- mysql 的一個監控指令碼,監控heartbeatMySql指令碼
- mysql mon 的一個監控指令碼,監控heartbeatMySql指令碼
- 指令碼監控MySQL伺服器指令碼MySql伺服器
- 監控mysql索引使用效率的指令碼MySql索引指令碼
- 監控指令碼指令碼
- 實戰模擬監控MySQL服務shell指令碼小結薦MySql指令碼
- Shell指令碼監控MySQL主從狀態指令碼MySql
- DBA監控指令碼指令碼
- session指令碼監控Session指令碼
- 埠監控指令碼指令碼
- oracle 監控指令碼Oracle指令碼
- 用python指令碼監控併發量Python指令碼
- DB2日常監控指令碼DB2指令碼
- 監控DB2資料庫指令碼DB2資料庫指令碼
- listener監聽監控指令碼指令碼
- 【SQL監控】SQL完全監控的指令碼SQL指令碼
- 【MySQL】MHA原始碼之監控檢查(一)MySql原始碼
- 使用表查詢監控DB2的死鎖DB2
- mysql大事務MySql
- 網路卡流量監控指令碼,python實現指令碼Python
- ogg監控指令碼指令碼
- stap監控IO指令碼指令碼
- 【shell】磁碟監控指令碼指令碼
- Oracle查詢回滾大事務所需時間Oracle
- PHP慢指令碼日誌和Mysql的慢查詢日誌PHP指令碼MySql
- 轉:MySQL主從、字典死鎖、連線數的Python監控指令碼MySqlPython指令碼
- shell指令碼監控啟動停止weblogic服務指令碼Web
- 系統監控&JVM監控指標資料查詢JVM指標
- Sqlserver查詢alwayson同步情況指令碼(2)SQLServer指令碼
- PostgreSQL之鎖監控指令碼SQL指令碼
- Oracle DBA常用監控指令碼Oracle指令碼
- memcached程式埠監控指令碼指令碼
- Nagios 監控ESXI指令碼iOS指令碼
- 監控cpu與memory指令碼指令碼