ITPub博客

首页 > Linux操作系统 > Linux操作系统 > mysql巡检脚本

mysql巡检脚本

Linux操作系统 作者:xz43 时间:2021-06-02 17:52:49 1 删除 编辑
#!/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='''        < div class = "stage-list" >
           < div class = "stage-title" >< span >{0}</ span ></ div >
           < div class = "detail" >
             < p class = "detail-list" >
               < span class = "detail-title" >区分格式</ span >
               < span class = "detail-describe" >{1}</ span >
             </ p >
             < p class = "detail-list" >
               < span class = "detail-title" >总空间大小</ span >
               < span class = "detail-describe" >{2:8.2f}G</ span >
             </ p >
             < p class = "detail-list" >
               < span class = "detail-title" >空闲空间(%)</ span >
               < span class = "detail-describe" >{3:8.2f}</ span >
             </ p >
             < p class = "detail-list" >
               
             </ p >
           </ div >
         </ div >\n'''.format(self.mount_point,self.status['fstype'],self.status['total'],self.status['used_percent'])
     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='''    < div class = "list-item" >
       < div class = "category" >
         < span >磁盘</ span >
       </ div >
       < div class = "second-stage" >\n'''
     for index in range(len(self.disks)):
       result=result+self.disks[index].__str__()
     result=result+'''      </ div >
     </ div >\n'''
     return result
 
class cpuResources(object):
   def __init__(self,status):
     self.status=status
   def __str__(self):
     result='''    < div class = "list-item" >
       < div class = "category" >
         < span >CPU</ span >
       </ div >
       < div class = "second-stage" >
         < div class = "stage-list" >
           < div class = "stage-title" >< span >global</ span ></ div >
           < div class = "detail" >
             < p class = "detail-list" >
               < span class = "detail-title" >用户空间使用(%)</ span >
               < span class = "detail-describe" >{0}</ span >
             </ p >
             < p class = "detail-list" >
               < span class = "detail-title" >内核空间使用(%)</ span >
               < span class = "detail-describe" >{1}</ span >
             </ p >
             < p class = "detail-list" >
               < span class = "detail-title" >空闲(%)</ span >
               < span class = "detail-describe" >{2}</ span >
             </ p >
             < p class = "detail-list" >
               < span class = "detail-title" >硬中断(%)</ span >
               < span class = "detail-describe" >{3}</ span >
             </ p >
             < p class = "detail-list" >
               < span class = "detail-title" >软中断(%)</ span >
               < span class = "detail-describe" >{4}</ span >
             </ p >
             < p class = "detail-list" >
               < span class = "detail-title" >io等待(%)</ span >
               < span class = "detail-describe" >{5}</ span >
             </ p >
             < p class = "detail-list" >
 
             </ p >
           </ div >
         </ div >
       </ div >
     </ div >\n'''.format(self.status['user'],self.status['system'],self.status['idle'],self.status['hardirq'],self.status['softirq'],self.status['iowait'])
     return result
 
class memResources(object):
   def __init__(self,status):
     self.status=status
 
   def __str__(self):
     result='''    < div class = "list-item" >
       < div class = "category" >
         < span >MEM</ span >
       </ div >
       < div class = "second-stage" >
         < div class = "stage-list" >
           < div class = "stage-title" >< span >global</ span ></ div >
           < div class = "detail" >
             < p class = "detail-list" >
               < span class = "detail-title" >总大小</ span >
               < span class = "detail-describe" >{0:8.2f}G</ span >
             </ p >
             < p class = "detail-list" >
               < span class = "detail-title" >空闲大小</ span >
               < span class = "detail-describe" >{1:8.2f}G</ span >
             </ p >
             
             < p class = "detail-list" >
               
             </ p >
           </ div >
         </ div >
       </ div >
     </ div >'''.format(self.status['total'],self.status['avariable'])
     return result
 
 
class mysqlResources(object):
   def __init__(self,status):
     self.status=status
   def __str__(self):
     result='''    < div class = "list-item" >
       < div class = "category" >
         < span >MYSQL</ span >
       </ div >
       < div class = "second-stage" >
         < div class = "stage-list" >
           < div class = "stage-title" >< span >{0}</ span ></ div >
           < div class = "detail" >
             < p class = "detail-list" >
               < span class = "detail-title" >innodb_log_wait</ span >
               < span class = "detail-describe" >{1}</ span >
             </ p >
             < p class = "detail-list" >
               < span class = "detail-title" >binlog_cache_use</ span >
               < span class = "detail-describe" >{2}</ span >
             </ p >
             < p class = "detail-list" >