#!/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() |
将代码上传至系统,赋值权限,在zabbix的mysql配置文中加入:
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() |
上述脚本不适合mysql 5.6 以上版本,所以要用MySQLdb模块去写:
#!/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 |
这样的if else让人有点蛋疼,继续优化代码:
#!/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() |
进一步优化代码,让代码可以根据不同的端口取值,取出的值先存入一个元组,然后遍历元组,取出相应key的值,这样就可以减少对数据库查询:
#!/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() |
相关推荐
zabbix监控mysql pecona模板 zabbix 3.0版本可用 zabbix 3.0版本可用
zabbix监控mysql表空间
zabbix监控mysql数据库详细配置,含监控mysql脚本;
Zabbix监控MySQL配置攻略,非常不错的,可以参考配置的
利用zabbix监控mysql,方便好用,不需要在被监控端安装或执行任何脚本
Zabbix监控MySQL的模板
Zabbix 监控 Mysql 的安装,此文档对于安装zabix 监控 Mysql 有非常大的作用,里面配置了 监控启动脚本,脚本内容非常完善,对于安装 zabbix 有非常大作用。
zabbix4.0监控mysql的xml文件
zabbix监控mysql数据库参数,zabbix监控mysql模板
https://blog.csdn.net/abel_dwh #zabbix监控mysql模版,已修改下载可直接导入监控
山石系列zabbix监控模板
zabbix监控系统
zabbix监控redis(python方法)
详细的讲述了在Linux下配置zabbix 监控mysql 的步骤,包括从安装开始,一步一步带着你做,每一个命令都有,很具体
zabbix 3.0 监控群晖模板,估计3.0 版本之后的版本都适用吧。具体监控项可以看我的博客。有一篇写的监控教程。关注我成为我的粉丝可以免费下载哦
使用Zabbix监控nsfocus防火墙设备已运行时间,业务端口带宽、CPU、内存以及业务端口状态和会话数,并设置告警触发器、图形。 注意:需更改设备地址、zabbix代理地址、SNMPv3相关账号密码信息后再导入。
整理的zabbix监控Oracle,Python Pyora
《Zabbix监控系统深度实践(第1版)》由浅入深,全面讲解Zabbix应用与原理,是作者多年实战经验的总结和浓缩。在概念篇,从一个简单但完整的入门案例讲起,案例中有最基本的概念介绍,通过案例帮助那些只需将服务器...