zabbix监控mysql的QPS跟TPS的python写法_mysql

#!/usr/bin/env python #coding=utf-8   import sys import os import commands   class QpsTps(object):     def __init__(self):         self.QPS = ''         self.TPS = ''     def getQps(self):         (Queries,QPS_result) = commands.getstatusoutput("mysqladmin -uroot -p123456 extended-status | grep 'Queries' | cut -d'|' -f3")         self.QPS = int(QPS_result)         return self.QPS     def getTps(self):         (Com_commit,cm_result) = commands.getstatusoutput("mysqladmin -uroot -p123456 extended-status | grep 'Com_commit' | cut -d'|' -f3 ")         (Com_rollback,rb_result) = commands.getstatusoutput("mysqladmin -uroot -p123456 extended-status | grep 'Com_rollback' | cut -d'|' -f3 | awk 'NR==1'")         self.TPS = int(cm_result) + int(rb_result)         return self.TPS   class error_out(object):     def error_print(self):         '''代入值少输，输出错误'''         print         print 'Usage : ' + sys.argv[0] + ' MysqlStatusKey '         print         sys.exit(1)   class Main(object):     def main(self):         if len(sys.argv) == 1:             error = error_out()             error.error_print()         elif sys.argv[1] == 'QPS':             a = QpsTps()             print a.getQps()         elif sys.argv[1] == 'TPS':             a = QpsTps()             print a.getTps()   if __name__ == '__main__':     main_obj = Main()     main_obj.main()

UserParameter=mysql.QPS,python /usr/local/zabbix/scripts/get_qps_tps.py QPS UserParameter=mysql.TPS,python /usr/local/zabbix/scripts/get_qps_tps.py TPS

# /usr/local/zabbix/bin/zabbix_get -s 10.16.1.68 -p 10050 -k"mysql.QPS" 1783724 # /usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -p 10050 -k"mysql.QPS"     3695982 # /usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -p 10050 -k"mysql.TPS" 278279

#!/usr/bin/env python #coding=utf-8   import sys import os import time import commands from db_init import InitDb   class MysqlPeerStatus(object):     def __init__(self):         a = InitDb()         a.readconfigfile()         self.user = a.GetUser()         self.passwd = a.GetPasswd()         self.value = 0     def GetValue(self, key):         (temp,last) = commands.getstatusoutput("mysqladmin -u%s -p%s extended-status | grep '%s>' | cut -d'|' -f3"%(self.user,self.passwd,key))         last = float(last)         return last   class MysqlQpsTps(object):     def __init__(self):         """init"""         self.a = MysqlPeerStatus()         for key in ('Com_insert','Com_update', 'Com_delete', 'Com_select'):             if key == 'Com_insert':                 self.com_insert = self.a.GetValue(key)             elif key == 'Com_update':                 self.com_update = self.a.GetValue(key)             elif key == 'Com_delete':                 self.com_delete = self.a.GetValue(key)             else:                 self.com_select = self.a.GetValue(key)       def Tps(self):         Tps = self.com_insert + self.com_update + self.com_delete         return Tps       def Qps(self):         Qps = self.com_insert + self.com_update + self.com_delete + self.com_select         return Qps   class InnodbBufferStatus(object):     def __init__(self):         """init"""         self.a = MysqlPeerStatus()         for key in ('Innodb_buffer_pool_pages_total','Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads',                      'Innodb_buffer_pool_pages_free','Innodb_buffer_pool_pages_dirty'):             if key == 'Innodb_buffer_pool_pages_total':                 self.pages_total = self.a.GetValue(key)             elif key == 'Innodb_buffer_pool_read_requests':                 self.cache_read = self.a.GetValue(key)             elif key == 'Innodb_buffer_pool_reads':                 self.disk_read = self.a.GetValue(key)             elif key == 'Innodb_buffer_pool_pages_free':                 self.free_pages = self.a.GetValue(key)             else:                 self.pages_dirty = self.a.GetValue(key)       def InnodbBufferReadHitRate(self):         result = (1 - self.disk_read/self.cache_read) * 100         return result       def InnodbBufferUsage(self):         result = (1 - self.free_pages/self.pages_total) * 100         return result       def InnodbBufferPoolDirtyPercentage(self):         result = self.pages_dirty/self.pages_total * 100         return result   class error_out(object):     def error_print(self):         '''输出错误信息'''         print         print 'Usage : ' + sys.argv[0] + ' time ' + ' MysqlStatusKey '         print 'MysqlStatusKey include (Qps, Tps, innodb_buffer_read_hit_ratio, innodb_buffer_usage, Queries Etc!)'         print         sys.exit(1)   class Main(object):     def main(self):         if len(sys.argv) == 1:             error = error_out()             error.error_print()         elif len(sys.argv) == 2:             #times = float(sys.argv[1])             key = sys.argv[1]             if key == 'innodb_buffer_read_hit_ratio':                 b = InnodbBufferStatus()                 print b.InnodbBufferReadHitRate()             elif key == 'innodb_buffer_usage':                 b = InnodbBufferStatus()                 print b.InnodbBufferUsage()             elif key == 'innodb_pages_dirty_percentage':                 b = InnodbBufferStatus()                 print b.InnodbBufferPoolDirtyPercentage()             elif key == 'Qps':                 b = MysqlQpsTps()                 print b.Qps()             elif key == 'Tps':                 b = MysqlQpsTps()                 print b.Tps()             else:                b = MysqlPeerStatus()                print b.GetValue(key)             #print last             #time.sleep(times)             #print (b.GetValue(key) - last) / times   if __name__ == '__main__':     main_obj = Main()     main_obj.main()

#!/usr/bin/env python #coding=utf8   import sys import os   class GetMysqlStatus():     def __init__(self):         self.val = {}         self.result = {}       def check(self):         import MySQLdb         import MySQLdb.cursors         try:             self.db = MySQLdb.connect(user="root", passwd="123456",                                       host="192.168.1.62", port=3306,                                       cursorclass=MySQLdb.cursors.DictCursor)         except Exception, e:             raise Exception, 'Cannot interface with MySQL server, %s' % e       def extract(self, key):         try:             c = self.db.cursor()             c.execute("""show global status like '%s';""" % key)             self.val = c.fetchone()             #print self.val             return float(self.val['Value'])             c.close()             self.db.close()         except Exception, e:             print e.message       def init(self):         for key in ('Com_insert', 'Com_delete', 'Com_select', 'Com_rollback',                     'Com_update', 'Com_commit', 'Innodb_buffer_pool_pages_total',                     'Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads',                     'Innodb_buffer_pool_pages_free', 'Innodb_buffer_pool_pages_dirty',                     'Key_blocks_used', 'Key_blocks_unused', 'Key_reads',                     'Key_read_requests', 'Key_writes', 'Key_write_requests'):             self.result[key] = self.extract(key)       def get_tps(self):         TPS = self.result['Com_commit'] + self.result['Com_rollback']         return TPS       def get_qps(self):         QPS = self.result['Com_insert'] + self.result['Com_delete'] +                self.result['Com_select'] + self.result['Com_update']         return QPS       def GetKeyReadHitRatio(self):         if self.result['Key_read_requests'] == 0:             Key_read_hit_ratio = 0         else:             Key_read_hit_ratio = (1 - self.result['Key_reads'] /                                   self.result['Key_read_requests']) * 100         return Key_read_hit_ratio       def GetKeyUsageRatio(self):         Key_usage_ratio = self.result['Key_blocks_used'] /                            (self.result['Key_blocks_used'] + self.result['Key_blocks_unused']) * 100         return Key_usage_ratio       def GetKeyWriteHitRatio(self):         if self.result['Key_write_requests'] == 0:             Key_write_hit_ratio = 0         else:             Key_write_hit_ratio = (1 - self.result['Key_writes'] /                                    self.result['Key_write_requests']) * 100         return Key_write_hit_ratio       def GetInnodbBufferReadHitRatio(self):         Innodb_buffer_read_hit_ratio = (1 - self.result['Innodb_buffer_pool_reads'] /                                         self.result['Innodb_buffer_pool_read_requests']) * 100         return Innodb_buffer_read_hit_ratio       def GetInnodbBufferPoolUsage(self):         Innodb_buffer_usage = (1 - self.result['Innodb_buffer_pool_pages_free'] /                                self.result['Innodb_buffer_pool_pages_total']) * 100         return Innodb_buffer_usage       def GetInnodbBufferPoolDirtyRatio(self):         Innodb_buffer_pool_dirty_ratio = (self.result['Innodb_buffer_pool_pages_dirty'] /                                           self.result['Innodb_buffer_pool_pages_total']) * 100         return Innodb_buffer_pool_dirty_ratio       def get_alive_status(self):         import socket         sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)         sock.settimeout(1)         try:             sock.connect(('192.168.1.62', 3306))             #print 'MySQL is alive!'             result = 1             return result         except Exception:             #print 'MySQL 3306 not connect!'             result = 0             return result         sock.close()   class ErrorOut():     def error_print(self):         """输出错误信息"""         print         print 'Usage: ' + sys.argv[0] + ' ' + ' MySQL_Status_Key '         print         sys.exit(1)   class Main():     def main(self):         if len(sys.argv) == 1:             error = ErrorOut()             error.error_print()         elif len(sys.argv) == 2:             key = sys.argv[1]             a = GetMysqlStatus()             a.check()             a.init()             if key == 'Innodb_buffer_read_hit_ratio':                 print a.GetInnodbBufferReadHitRatio()             elif key == 'Innodb_buffer_usage':                 print a.GetInnodbBufferPoolUsage()             elif key == 'Innodb_buffer_pool_dirty_ratio':                 print a.GetInnodbBufferPoolDirtyRatio()             elif key == 'QPS':                 print a.get_qps()             elif key == 'TPS':                 print a.get_tps()             elif key == 'Key_usage_ratio':                 print a.GetKeyUsageRatio()             elif key == 'Key_read_hit_ratio':                 print a.GetKeyReadHitRatio()             elif key == 'Key_write_hit_ratio':                 print a.GetKeyWriteHitRatio()             elif key == 'MySQL_alive':                 print a.get_alive_status()             else:                 print a.extract(key)   if __name__ == "__main__":      exe = Main()      exe.main()

D:flask>python get_mysql_status.py   Usage: get_mysql_status.py  MySQL_Status_Key     D:flask>python get_mysql_status.py Innodb_buffer_pool_reads 144.0   D:flask>python get_mysql_status.py MySQL_alive 1   D:flask>python get_mysql_status.py Innodb_buffer_read_hit_ratio 68.6274509804

#!/usr/bin/env python #coding=utf8   import sys import os import inspect   class GetMysqlStatus():     def __init__(self):         self.val = {}         self.result = {}       def check(self):         import MySQLdb         import MySQLdb.cursors         try:             self.db = MySQLdb.connect(user="root", passwd="LVS@071103",                                       host="192.168.1.62", port=3306,                                       cursorclass=MySQLdb.cursors.DictCursor)         except Exception, e:             raise Exception, 'Cannot interface with MySQL server, %s' % e       def extract(self, key):         try:             c = self.db.cursor()             c.execute("""show global status like '%s';""" % key)             self.val = c.fetchone()             return float(self.val['Value'])             c.close()             self.db.close()         except Exception, e:             print e.message       def init(self):         for key in ('Com_insert', 'Com_delete', 'Com_select', 'Com_rollback',                     'Com_update', 'Com_commit', 'Innodb_buffer_pool_pages_total',                     'Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads',                     'Innodb_buffer_pool_pages_free', 'Innodb_buffer_pool_pages_dirty',                     'Key_blocks_used', 'Key_blocks_unused', 'Key_reads',                     'Key_read_requests', 'Key_writes', 'Key_write_requests'):             self.result[key] = self.extract(key)       def TPS(self):         TPS = self.result['Com_commit'] + self.result['Com_rollback']         return TPS       def QPS(self):         QPS = self.result['Com_insert'] + self.result['Com_delete'] +                self.result['Com_select'] + self.result['Com_update']         return QPS       def Key_read_hit_ratio(self):         if self.result['Key_read_requests'] == 0:             Key_read_hit_ratio = 0         else:             Key_read_hit_ratio = (1 - self.result['Key_reads'] /                                   self.result['Key_read_requests']) * 100         return Key_read_hit_ratio       def Key_usage_ratio(self):         Key_usage_ratio = self.result['Key_blocks_used'] /                            (self.result['Key_blocks_used'] + self.result['Key_blocks_unused']) * 100         return Key_usage_ratio       def Key_write_hit_ratio(self):         if self.result['Key_write_requests'] == 0:             Key_write_hit_ratio = 0         else:             Key_write_hit_ratio = (1 - self.result['Key_writes'] /                                    self.result['Key_write_requests']) * 100         return Key_write_hit_ratio       def Innodb_buffer_read_hit_ratio(self):         Innodb_buffer_read_hit_ratio = (1 - self.result['Innodb_buffer_pool_reads'] /                                         self.result['Innodb_buffer_pool_read_requests']) * 100         return Innodb_buffer_read_hit_ratio       def Innodb_buffer_usage(self):         Innodb_buffer_usage = (1 - self.result['Innodb_buffer_pool_pages_free'] /                                self.result['Innodb_buffer_pool_pages_total']) * 100         return Innodb_buffer_usage       def Innodb_buffer_pool_dirty_ratio(self):         Innodb_buffer_pool_dirty_ratio = (self.result['Innodb_buffer_pool_pages_dirty'] /                                           self.result['Innodb_buffer_pool_pages_total']) * 100         return Innodb_buffer_pool_dirty_ratio       def MySQL_alive(self):         import socket         sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)         sock.settimeout(1)         try:             sock.connect(('192.168.1.62', 3306))             #print 'MySQL is alive!'             result = 1             return result         except Exception:             #print 'MySQL 3306 not connect!'             result = 0             return result         sock.close()   class ErrorOut():     def error_print(self):         """输出错误信息"""         print         print 'Usage: ' + sys.argv[0] + ' ' + ' MySQL_Status_Key '         print         sys.exit(1)   class Main():     def main(self):         if len(sys.argv) == 1:             error = ErrorOut()             error.error_print()         elif len(sys.argv) == 2:             method_name = sys.argv[1]             a = GetMysqlStatus()             a.check()             a.init()             if hasattr(a, method_name):                 print getattr(a, method_name)()             else:                 print a.extract(method_name)   if __name__ == "__main__":      run = Main()      run.main()

#!/usr/bin/env python #coding=utf8   import sys import os import inspect import MySQLdb import MySQLdb.cursors   class GetMysqlStatus():     def __init__(self):         self.result = ''         self.each_result = ''     def check(self, port):         try:             self.db = MySQLdb.connect(user="root", passwd="LVS@071103",                                       host="127.0.0.1", port=port,                                       cursorclass=MySQLdb.cursors.DictCursor)         except Exception, e:             raise Exception, 'Cannot interface with MySQL server, %s' % e       def extract(self):         try:             c = self.db.cursor()             c.execute("""show global status;""")             self.result = c.fetchall()             return self.result             c.close()             self.db.close()         except Exception, e:             print e       def getVal(self, key):         for i in self.result:             if i['Variable_name'] == key:                 self.each_result = i['Value']         return self.each_result       def TPS(self):         TPS = int(self.getVal('Com_commit')) + int(self.getVal('Com_rollback'))         return TPS       def QPS(self):         return int(self.getVal('Com_insert')) + int(self.getVal('Com_delete')) + int(self.getVal('Com_select')) + int(self.getVal('Com_update'))       def Key_read_hit_ratio(self):         try:             Key_read_hit_ratio = (1 - float(self.getVal('Key_reads'))  / float(self.getVal('Key_read_requests'))) * 100         except ZeroDivisionError, e:             print "integer division or modulo by zero", e         return Key_read_hit_ratio       def Key_usage_ratio(self):         try:             Key_usage_ratio = float(self.getVal('Key_blocks_used')) / (float(self.getVal('Key_blocks_used')) + float(self.getVal('Key_blocks_unused')))         except ZeroDivisionError, e:             print "integer division or modulo by zero", e         return Key_usage_ratio       def Key_write_hit_ratio(self):         try:             Key_write_hit_ratio = (1 - float(self.getVal('Key_writes')) / float(self.getVal('Key_write_requests'))) * 100         except ZeroDivisionError, e:             print "integer division or modulo by zero", e         return Key_write_hit_ratio       def Innodb_buffer_read_hit_ratio(self):         try:             Innodb_buffer_read_hit_ratio = (1 - float(self.getVal('Innodb_buffer_pool_reads')) / float(self.getVal('Innodb_buffer_pool_read_requests'))) * 100         except ZeroDivisionError, e:             print "integer division or modulo by zero", e         return Innodb_buffer_read_hit_ratio       def Innodb_buffer_usage(self):         try:             Innodb_buffer_usage = (1 - float(self.getVal('Innodb_buffer_pool_pages_free')) / float(self.getVal('Innodb_buffer_pool_pages_total'))) * 100         except ZeroDivisionError, e:             print "integer division or modulo by zero", e         return Innodb_buffer_usage       def Innodb_buffer_pool_dirty_ratio(self):         try:             Innodb_buffer_pool_dirty_ratio = (float(self.getVal('Innodb_buffer_pool_pages_dirty')) / float(self.getVal('Innodb_buffer_pool_pages_total'))) * 100         except ZeroDivisionError, e:             print "integer division or modulo by zero", e         return Innodb_buffer_pool_dirty_ratio   class ErrorOut():     def error_print(self):         """输出错误信息"""         print         print 'Usage: ' + sys.argv[0] + ' ' + ' MySQL_Status_Key '         print         sys.exit(1)   class Main():     def main(self):         error = ErrorOut()         if len(sys.argv) == 1:             error.error_print()         elif len(sys.argv) == 2:             error.error_print()         elif len(sys.argv) == 3:             port = int(sys.argv[1])             key = sys.argv[2]             a = GetMysqlStatus()             a.check(port)             a.extract()             if hasattr(a, key):                 print getattr(a, key)()             else:                 print a.getVal(key)   if __name__ == "__main__":      run = Main()      run.main()

#!/usr/bin/env python #coding=utf8   import sys import os import inspect import MySQLdb import MySQLdb.cursors   class GetMysqlStatus():     def __init__(self):         self.result = ''         self.dict = {}     def check(self, port):         try:             self.db = MySQLdb.connect(user="root", passwd="LVS@071103",                                       host="127.0.0.1", port=port,                                       cursorclass=MySQLdb.cursors.DictCursor)         except Exception, e:             raise Exception, 'Cannot interface with MySQL server, %s' % e       def extract(self):         try:             c = self.db.cursor()             c.execute("""show global status;""")             self.result = c.fetchall()             for i in self.result:                 self.dict[i['Variable_name']] = i['Value']             return self.dict             c.close()             self.db.close()         except Exception, e:             print e       def get_val(self, key):         return self.dict[key]       def TPS(self):         TPS = int(self.dict['Com_commit']) + int(self.dict['Com_rollback'])         return TPS       def QPS(self):         return int(self.dict['Com_insert']) + int(self.dict['Com_delete']) + int(self.dict['Com_select']) + int(self.dict['Com_update'])       def Key_read_hit_ratio(self):         try:             Key_read_hit_ratio = (1 - float(self.dict['Key_reads'])  / float(self.dict['Key_read_requests'])) * 100         except ZeroDivisionError, e:             print "integer division or modulo by zero", e         return Key_read_hit_ratio       def Key_usage_ratio(self):         try:             Key_usage_ratio = float(self.dict['Key_blocks_used']) / (float(self.dict['Key_blocks_used']) + float(self.dict['Key_blocks_unused']))         except ZeroDivisionError, e:             print "integer division or modulo by zero", e         return Key_usage_ratio       def Key_write_hit_ratio(self):         try:             Key_write_hit_ratio = (1 - float(self.dict['Key_writes']) / float(self.dict['Key_write_requests'])) * 100         except ZeroDivisionError, e:             print "integer division or modulo by zero", e         return Key_write_hit_ratio       def Innodb_buffer_read_hit_ratio(self):         try:             Innodb_buffer_read_hit_ratio = (1 - float(self.dict['Innodb_buffer_pool_reads']) / float(self.dict['Innodb_buffer_pool_read_requests'])) * 100         except ZeroDivisionError, e:             print "integer division or modulo by zero", e         return Innodb_buffer_read_hit_ratio       def Innodb_buffer_usage(self):         try:             Innodb_buffer_usage = (1 - float(self.dict['Innodb_buffer_pool_pages_free']) / float(self.dict['Innodb_buffer_pool_pages_total'])) * 100         except ZeroDivisionError, e:             print "integer division or modulo by zero", e         return Innodb_buffer_usage       def Innodb_buffer_pool_dirty_ratio(self):         try:             Innodb_buffer_pool_dirty_ratio = (float(self.dict['Innodb_buffer_pool_pages_dirty']) / float(self.dict['Innodb_buffer_pool_pages_total'])) * 100         except ZeroDivisionError, e:             print "integer division or modulo by zero", e         return Innodb_buffer_pool_dirty_ratio   class ErrorOut():     def error_print(self):         """输出错误信息"""         print         print 'Usage: ' + sys.argv[0] + ' ' + ' MySQL_Status_Key '         print         sys.exit(1)   class Main():     def main(self):         error = ErrorOut()         if len(sys.argv) == 1:             error.error_print()         elif len(sys.argv) == 2:             error.error_print()         elif len(sys.argv) == 3:             port = int(sys.argv[1])             key = sys.argv[2]             a = GetMysqlStatus()             a.check(port)             a.extract()             if hasattr(a, key):                 print getattr(a, key)()             else:                 print a.get_val(key)   if __name__ == "__main__":      run = Main()      run.main()