#!/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 QPSUserParameter=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()