mysql5.7.20巡检脚本_详细介绍mysql巡检脚本的案例代码

下面小编就为大家带来一篇mysql巡检脚本(必看篇)。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧

如下所示：#!/usr/bin/env python3.5

import psutil

import mysql.connector

import argparse

import json

import datetime

def get_cpu_info(verbose):

cpu_info={}

if verbose >0:

print("[cpu] start collect cpu info ...")

data=psutil.cpu_times_percent(3)

cpu_info['user']=data[0]

cpu_info['system']=data[2]

cpu_info['idle']=data[3]

cpu_info['iowait']=data[4]

cpu_info['hardirq']=data[5]

cpu_info['softirq']=data[6]

cpu_info['cpu_cores']=psutil.cpu_count()

if verbose >0:

print("{0}".format(json.dumps(cpu_info,ensure_ascii=False,indent=4)))

print("[cpu] collection compeleted ...")

return cpu_info

def get_mem_info(verbose):

mem_info={}

if verbose >0:

print("[mem] start collect mem info ...")

data=psutil.virtual_memory()

mem_info['total']=data[0]/1024/1024/1024

mem_info['avariable']=data[1]/1024/1024/1024

if verbose>0:

print("{0}".format(json.dumps(mem_info,ensure_ascii=False,indent=4)))

print("[mem] collection compeletd ...")

return mem_info

def get_disk_info(verbose):

disk_info={}

if verbose >0:

print("[disk] start collect disk info ...")

partitions=psutil.disk_partitions()

partitions=[(partition[1],partition[2])for partition in partitions if partition[2]!='iso9660']

disk_info={}

for partition in partitions:

disk_info[partition[0]]={}

disk_info[partition[0]]['fstype']=partition[1]

for mount_point in disk_info.keys():

data=psutil.disk_usage(mount_point)

disk_info[mount_point]['total']=data[0]/1024/1024/1024

disk_info[mount_point]['used_percent']=data[3]

if verbose >0:

print("{0}".format(json.dumps(disk_info,ensure_ascii=False,indent=4)))

print("[disk] collection compeleted ....")

return disk_info

def get_mysql_info(cnx_args,status_list):

config={

'user':cnx_args.user,

'password':cnx_args.password,

'host':cnx_args.host,

'port':cnx_args.port}

cnx=None

cursor=None

mysql_info={}

try:

cnx=mysql.connector.connect(**config)

cursor=cnx.cursor(prepared=True)

for index in range(len(status_list)):

status_list[index].get_status(cursor)

status=status_list[index]

mysql_info[status.name]=status.value

mysql_info['port']=config['port']

except mysql.connector.Error as err:

print(err)

finally:

if cursor != None:

cursor.close()

if cnx != None:

cnx.close()

return mysql_info

class Status(object):

def init(self,name):

self.name=name

self._value=None

def get_status(self,cursor):

stmt="show global status like '{0}';".format(self.name)

cursor.execute(stmt)

value=cursor.fetchone()[1].decode('utf8')

self._value=int(value)

@property

def value(self):

if self._value==None:

raise Exception("cant get value befor execute the get_status function")

else:

return self._value

IntStatus=Status

class diskResource(object):

def init(self,mount_point,status):

self.mount_point=mount_point

self.status=status

def str(self):

result='''

{0}

区分格式

{1}

总空间大小

{2:8.2f}G

空闲空间(%)

{3:8.2f}

return result

class diskResources(object):

def init(self,status):

self.disks=[]

for mount_point in status.keys():

self.disks.append(diskResource(mount_point,status[mount_point]))

def str(self):

result='''

磁盘

\n'''

for index in range(len(self.disks)):

result=result+self.disks[index].str()

result=result+'''

return result

class cpuResources(object):

def init(self,status):

self.status=status

def str(self):

result='''

CPU

global

用户空间使用(%)

{0}

内核空间使用(%)

{1}

空闲(%)

{2}

硬中断(%)

{3}

软中断(%)

{4}

io等待(%)

{5}

return result

class memResources(object):

def init(self,status):

self.status=status

def str(self):

result='''

MEM

global

总大小

{0:8.2f}G

空闲大小

{1:8.2f}G

return result

class mysqlResources(object):

def init(self,status):

self.status=status

def str(self):

result='''

MYSQL

{0}

innodb_log_wait

{1}

binlog_cache_use

{2}

create_temp_disk_table

{3}

Slow_querys

{4}

self.status['Created_tmp_disk_tables'],self.status['Slow_queries'])

return result

class hostResources(object):

def init(self,cpu_info,mem_info,disk_info,mysql_info,report_title='MySQL巡检报告'):

self.cpu=cpuResources(cpu_info)

self.mem=memResources(mem_info)

self.disk=diskResources(disk_info)

self.mysql=mysqlResources(mysql_info)

self.report_title=report_title

def str(self):

result='''

巡检报告

*{

margin: 0;

padding: 0;

}

.content{

width:1000px;

height: auto;

margin: 30px auto;

border-bottom:1px solid #b2b2b2;

}

.list-item{

border:1px solid #b2b2b2;

border-bottom: none;

transition: all .35s;

overflow: hidden;

display: flex;

}

.list-item:empty{

display: none;

}

.top-title{

line-height: 32px;

font-size: 16px;

color: #333;

text-indent: 10px;

font-weight: 600;

}

.category{

width:97px;

height: auto;

border-right: 1px solid #b2b2b2;

float: left;

text-align: center;

position: relative;

}

.stage-title>span,

.category>span{

display: block;

height: 20px;

width:100%;

text-align: center;

line-height: 20px;

position: absolute;

top: 50%;

margin-top: -10px;left: 0;

}

.second-stage{

width:900px;

float: left;

}

.stage-list{

border-bottom: 1px solid #b2b2b2;

display: flex;

}

.stage-list:last-child{

border-bottom: 0;

}

.stage-title{

width:99px;

border-right: 1px solid #b2b2b2;

position: relative;

}

.detail{

flex: 1;

}

.detail-list{

border-bottom: 1px solid #b2b2b2;

height: 40px;

display: flex;

transition: all .35s;

}

.detail-title{

padding: 10px;

height: 20px;

line-height: 20px;

border-right: 1px solid #b2b2b2;

width:200px;

}

.detail-describe{

flex: 1;

padding: 10px;line-height: 20px;

}

.detail-list:last-child{

border-bottom: 0;

}

.list-item:hover{

background-color: #eee;

}

.detail-list:hover{

background-color: #d1d1d1;

}

report_title

result=result.replace('report_title',self.report_title)

result=result+self.cpu.str()

result=result+self.mem.str()

result=result+self.disk.str()

result=result+self.mysql.str()

result=result+'''

'''

return result

if name=="main":

parser=argparse.ArgumentParser()

parser.add_argument('--verbose',type=int,default=1,help='verbose for output')

parser.add_argument('--user',default='chkuser',help='user name for connect to mysql')

parser.add_argument('--password',default='123456',help='user password for connect to mysql')

parser.add_argument('--host',default='127.0.0.1',help='mysql host ip')

parser.add_argument('--port',default=3306,type=int,help='mysql port')

parser.add_argument('--int-status',default=('Com_select,Com_insert,Com_update,Com_delete,Innodb_log_waits,'

'Binlog_cache_disk_use,Binlog_cache_use,Created_tmp_disk_tables,'

'Slow_queries')

,help='mysql status its value like int')

parser.add_argument('--report-title',default='MySQL巡检报告',help='report title')

parser.add_argument('--output-dir',default='/tmp/',help='default report file output path')

args=parser.parse_args()

cpu_info=get_cpu_info(args.verbose)

mem_info=get_mem_info(args.verbose)

disk_info=get_disk_info(args.verbose)

status_list=[ IntStatus(name=item) for item in args.int_status.split(',')]

mysql_info=get_mysql_info(args,status_list)

#dr=diskResources(disk_info)

#cr=cpuResources(cpu_info)

#mr=memResources(mem_info)

#msr=mysqlResources(mysql_info)

hr=hostResources(cpu_info,mem_info,disk_info,mysql_info,args.report_title)

now=str(datetime.datetime.now()).replace(' ','^')

if args.output_dir.endswith('/') != True:

args.output_dir=args.output_dir+'/'

filename=args.output_dir+'mysql_inspection_{0}.html'.format(now)

with open(filename,'w') as output:

output.write(hr.str())

print('[report] the report been saved to {0} ok.... ....'.format(filename))