下面小编就为大家带来一篇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=''' <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'''.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=''' <p class="list-item"> <p class="category"> <span>磁盘</span> </p> <p class="second-stage">\n''' for index in range(len(self.disks)): result=result+self.disks[index].str() result=result+''' </p> </p>\n''' return result class cpuResources(object): def init(self,status): self.status=status def str(self): result=''' <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'''.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=''' <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>'''.format(self.status['total'],self.status['avariable']) return result class mysqlResources(object): def init(self,status): self.status=status def str(self): result=''' <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>'''.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巡检报告'): 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='''<!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''' 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+''' </p> </body> </html>''' 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))
以上是详细介绍mysql巡检脚本的案例代码的详细内容。更多信息请关注PHP中文网其他相关文章!

在数据库优化中,应根据查询需求选择索引策略:1.当查询涉及多个列且条件顺序固定时,使用复合索引;2.当查询涉及多个列但条件顺序不固定时,使用多个单列索引。复合索引适用于优化多列查询,单列索引则适合单列查询。

要优化MySQL慢查询,需使用slowquerylog和performance_schema:1.启用slowquerylog并设置阈值,记录慢查询;2.利用performance_schema分析查询执行细节,找出性能瓶颈并优化。

MySQL和SQL是开发者必备技能。1.MySQL是开源的关系型数据库管理系统,SQL是用于管理和操作数据库的标准语言。2.MySQL通过高效的数据存储和检索功能支持多种存储引擎,SQL通过简单语句完成复杂数据操作。3.使用示例包括基本查询和高级查询,如按条件过滤和排序。4.常见错误包括语法错误和性能问题,可通过检查SQL语句和使用EXPLAIN命令优化。5.性能优化技巧包括使用索引、避免全表扫描、优化JOIN操作和提升代码可读性。

MySQL异步主从复制通过binlog实现数据同步,提升读性能和高可用性。1)主服务器记录变更到binlog;2)从服务器通过I/O线程读取binlog;3)从服务器的SQL线程应用binlog同步数据。

MySQL是一个开源的关系型数据库管理系统。1)创建数据库和表:使用CREATEDATABASE和CREATETABLE命令。2)基本操作:INSERT、UPDATE、DELETE和SELECT。3)高级操作:JOIN、子查询和事务处理。4)调试技巧:检查语法、数据类型和权限。5)优化建议:使用索引、避免SELECT*和使用事务。

MySQL的安装和基本操作包括:1.下载并安装MySQL,设置根用户密码;2.使用SQL命令创建数据库和表,如CREATEDATABASE和CREATETABLE;3.执行CRUD操作,使用INSERT,SELECT,UPDATE,DELETE命令;4.创建索引和存储过程以优化性能和实现复杂逻辑。通过这些步骤,你可以从零开始构建和管理MySQL数据库。

InnoDBBufferPool通过将数据和索引页加载到内存中来提升MySQL数据库的性能。1)数据页加载到BufferPool中,减少磁盘I/O。2)脏页被标记并定期刷新到磁盘。3)LRU算法管理数据页淘汰。4)预读机制提前加载可能需要的数据页。

MySQL适合初学者使用,因为它安装简单、功能强大且易于管理数据。1.安装和配置简单,适用于多种操作系统。2.支持基本操作如创建数据库和表、插入、查询、更新和删除数据。3.提供高级功能如JOIN操作和子查询。4.可以通过索引、查询优化和分表分区来提升性能。5.支持备份、恢复和安全措施,确保数据的安全和一致性。


热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

AI Hentai Generator
免费生成ai无尽的。

热门文章

热工具

WebStorm Mac版
好用的JavaScript开发工具

螳螂BT
Mantis是一个易于部署的基于Web的缺陷跟踪工具,用于帮助产品缺陷跟踪。它需要PHP、MySQL和一个Web服务器。请查看我们的演示和托管服务。

SecLists
SecLists是最终安全测试人员的伙伴。它是一个包含各种类型列表的集合,这些列表在安全评估过程中经常使用,都在一个地方。SecLists通过方便地提供安全测试人员可能需要的所有列表,帮助提高安全测试的效率和生产力。列表类型包括用户名、密码、URL、模糊测试有效载荷、敏感数据模式、Web shell等等。测试人员只需将此存储库拉到新的测试机上,他就可以访问到所需的每种类型的列表。

VSCode Windows 64位 下载
微软推出的免费、功能强大的一款IDE编辑器

Atom编辑器mac版下载
最流行的的开源编辑器