Introduction détaillée au code de cas du script d'inspection mysql

L'éditeur ci-dessous vous apportera un script d'inspection mysql (un article à lire absolument). L'éditeur le trouve plutôt bon, je vais donc le partager avec vous maintenant et le donner comme référence pour tout le monde. Suivons l'éditeur pour venir jeter un oeil

comme suit :

#!/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=&#39;&#39;&#39;        <p class="stage-list">
          <p class="stage-title"><span>{0}</span></p>
          <p 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>
          </p>
        </p>\n&#39;&#39;&#39;.format(self.mount_point,self.status[&#39;fstype&#39;],self.status[&#39;total&#39;],self.status[&#39;used_percent&#39;])
    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=&#39;&#39;&#39;    <p class="list-item">
      <p class="category">
        <span>磁盘</span>
      </p>
      <p class="second-stage">\n&#39;&#39;&#39;
    for index in range(len(self.disks)):
      result=result+self.disks[index].str()
    result=result+&#39;&#39;&#39;      </p>
    </p>\n&#39;&#39;&#39;
    return result

class cpuResources(object):
  def init(self,status):
    self.status=status
  def str(self):
    result=&#39;&#39;&#39;    <p class="list-item">
      <p class="category">
        <span>CPU</span>
      </p>
      <p class="second-stage">
        <p class="stage-list">
          <p class="stage-title"><span>global</span></p>
          <p 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>
          </p>
        </p>
      </p>
    </p>\n&#39;&#39;&#39;.format(self.status[&#39;user&#39;],self.status[&#39;system&#39;],self.status[&#39;idle&#39;],self.status[&#39;hardirq&#39;],self.status[&#39;softirq&#39;],self.status[&#39;iowait&#39;])
    return result

class memResources(object):
  def init(self,status):
    self.status=status

  def str(self):
    result=&#39;&#39;&#39;    <p class="list-item">
      <p class="category">
        <span>MEM</span>
      </p>
      <p class="second-stage">
        <p class="stage-list">
          <p class="stage-title"><span>global</span></p>
          <p 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>
          </p>
        </p>
      </p>
    </p>&#39;&#39;&#39;.format(self.status[&#39;total&#39;],self.status[&#39;avariable&#39;])
    return result


class mysqlResources(object):
  def init(self,status):
    self.status=status
  def str(self):
    result=&#39;&#39;&#39;    <p class="list-item">
      <p class="category">
        <span>MYSQL</span>
      </p>
      <p class="second-stage">
        <p class="stage-list">
          <p class="stage-title"><span>{0}</span></p>
          <p 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">
              <span class="detail-title">create_temp_disk_table</span>
              <span class="detail-describe">{3}</span>
            </p>
                        <p class="detail-list">
                            <span class="detail-title">Slow_querys</span>
                            <span class="detail-describe">{4}</span>
                        </p>

            <p class="detail-list">
              
            </p>
          </p>
        </p>
      </p>
    </p>&#39;&#39;&#39;.format(self.status[&#39;port&#39;],self.status[&#39;Innodb_log_waits&#39;],self.status[&#39;Binlog_cache_use&#39;],
             self.status[&#39;Created_tmp_disk_tables&#39;],self.status[&#39;Slow_queries&#39;])

    return result

class hostResources(object):
  def init(self,cpu_info,mem_info,disk_info,mysql_info,report_title=&#39;MySQL巡检报告&#39;):
    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=&#39;&#39;&#39;<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>巡检报告</title>
<style>
*{
  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;
  }
</style>
</head>
<body>
  <p class="content">
        <p class="list-item">
            <p class="top-title">report_title</p>
        </p>\n&#39;&#39;&#39;

    result=result.replace(&#39;report_title&#39;,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+&#39;&#39;&#39;  </p>
</body>
</html>&#39;&#39;&#39;
    return result


if name=="main":
  parser=argparse.ArgumentParser()
  parser.add_argument(&#39;--verbose&#39;,type=int,default=1,help=&#39;verbose for output&#39;)
  parser.add_argument(&#39;--user&#39;,default=&#39;chkuser&#39;,help=&#39;user name for connect to mysql&#39;)
  parser.add_argument(&#39;--password&#39;,default=&#39;123456&#39;,help=&#39;user password for connect to mysql&#39;)
  parser.add_argument(&#39;--host&#39;,default=&#39;127.0.0.1&#39;,help=&#39;mysql host ip&#39;)
  parser.add_argument(&#39;--port&#39;,default=3306,type=int,help=&#39;mysql port&#39;)
  parser.add_argument(&#39;--int-status&#39;,default=(&#39;Com_select,Com_insert,Com_update,Com_delete,Innodb_log_waits,&#39;
                        &#39;Binlog_cache_disk_use,Binlog_cache_use,Created_tmp_disk_tables,&#39;
                        &#39;Slow_queries&#39;)
            ,help=&#39;mysql status its value like int&#39;)
  parser.add_argument(&#39;--report-title&#39;,default=&#39;MySQL巡检报告&#39;,help=&#39;report title&#39;)
  parser.add_argument(&#39;--output-dir&#39;,default=&#39;/tmp/&#39;,help=&#39;default report file output path&#39;)
  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(&#39;,&#39;)]
  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(&#39; &#39;,&#39;^&#39;)
  if args.output_dir.endswith(&#39;/&#39;) != True:
    args.output_dir=args.output_dir+&#39;/&#39;
  filename=args.output_dir+&#39;mysql_inspection_{0}.html&#39;.format(now)
  with open(filename,&#39;w&#39;) as output:
    output.write(hr.str())
  print(&#39;[report]  the report been saved to {0}  ok.... ....&#39;.format(filename))

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!