Shulou(Shulou.com)05/31 Report--

How to use python to write a simple mysql inspection script, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Preparatory work:

1 install python 3.5, this time use source code to install.

2 install the psutil module and run directly using the easy_install package that comes with python3.5

Cd / opt/python3/bin

. / easy_install-3.5 psuitl installation

3 install the mysql_connector module, also using easy_install installation

Easy_install-3.5 mysql-connector==2.1.4

After the preparation is complete, you can run the following script:

[mysql@jing1 scripts] $more healthcheck.py

#! / usr/bin/env python3

#-*-coding: gbk-*-

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 Universe 1024

Mem_info ['avariable'] = data [1] / 1024 Universe 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_ in [mount _ point] ['total'] = data [0] / 1024 Universe 1024

Disk_ in [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_ in [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}

Range_format

{1}

Total_space

{2:8.2f} G

Used_space (%)

{3:8.2f}

\ 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='''

Disk

\ nFellows'

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

Result=result+ self.disks [index]. _ _ str__ ()

Result=result+'''

\ nFellows'

Return result

Class cpuResources (object):

Def _ init__ (self,status):

Self.status=status

Def _ str__ (self):

Result='''

CPU

Global

User_space (%)

{0}

Kernel_space (%)

{1}

Space (%)

{2}

Hard_cruppt (%)

{3}

Soft_cruppt (%)

{4}

Io_wait (%)

{5}

\ n'''.format (self.status ['user'], self.status [' system'], self.status ['idle'], self.status [' hardirq'], self.statu

S ['softirq'], self.status [' iowait'])

Return result

Class memResources (object):

Def _ init__ (self,status):

Self.status=status

Def _ str__ (self):

Result='''

MEM

Global

Total

{0:8.2f} G

Free

{1:8.2f} G

'.format (self.status ['total'], self.status [' avariable'])

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}

'.format (self.status ['port'], self.status [' Innodb_log_waits'], self.status ['Binlog_cache_use'])

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 health check'):

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='''

Health_check

* {

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:-10pxleft: 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: 10pxposition linepower height: 20px

}

.detail-list:last-child {

Border-bottom: 0

}

. list-item:hover {

Background-color: # eee

}

. detail-list:hover {

Background-color: # d1d1d1

}

Report_title

\ nFellows'

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='system',help='user name for connect to mysql')

Parser.add_argument ('--password',default='welcome123',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 health check',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))

After reading the above, have you mastered the method of how to write a simple mysql inspection script in python? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.