python 监控mysql脚本
#!/usr/bin/env python #-*- coding: UTF-8 -*- from __future__ import print_function from mysql import connector import logging,argparse,sys import sys #create user monitoruser@'127.0.0.1' identified by '123456'; #grant replication client on *.* to monitoruser@'127.0.0.1'; #grant super on *.* to monitoruser@'127.0.0.1'; class MonitorItem(object): """ 所有监控项的基类 """ def __init__(self,user='monitoruser',password='123456',host='127.0.0.1',port=3306): """初始化属性与到数据库端的连接""" self.user=user self.password=password self.host=host self.port=port self.cnx=None self.cursor=None try: config={'user':self.user,'password':self.password,'host':self.host,'port':self.port} self.cnx=connector.connect(**config) self.cursor=self.cnx.cursor(prepared=True) except connector.Error as err: """如果连接失败就赋空值""" self.cnx=None self.cursor=None sys.stderr.write(err.msg+'n') def __str__(self): attrs={} attrs['user']=self.user attrs['password']=self.password attrs['host']=self.host attrs['port']=self.port return "instance of {0} {1}".format(self.__class__,attrs) def __del__(self): """在python 进行垃圾回收时关闭连接""" if self.cnx != None: self.cnx.close() def get_result(self): """返回监控项的状态,由子类实现相应的功能""" pass def print_result(self): """打印监控项的状态""" print(self.get_result()) def action(self): """监控项达到阀值时可以触发的操作""" print("末定义任何有意义的操作") ########计算磁盘使用率############## class MysqlDiskUsed(MonitorItem): def get_result(self): try: sql_cmd = "select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2)) as data_size,concat(truncate(sum(index_length)/1024/1024,2)) as index_size from information_schema.tables group by TABLE_SCHEMA order by data_length desc;" #单位MB self.cursor.execute(sql_cmd) row = self.cursor.fetchall() #self.cursor.close() #self.cnx.close() disk_size = 0 for i in row: for j in i[1:]: disk_size += float(j.decode('utf8')) disk_used_percent = disk_size / 50 * 100 return int(disk_used_percent) except Exception as err: sys.stderr.write(err.__str__()+'n') return -1 ##################################### ################计算内存使用率############## class MysqlMemUsed(MonitorItem): """计算内存使用率""" def get_result(self): try: sql_cmd = "select (@@key_buffer_size + @@query_cache_size + @@tmp_table_size +@@innodb_buffer_pool_size +@@innodb_additional_mem_pool_size +@@innodb_log_buffer_size +@@max_connections * (@@read_buffer_size +@@read_rnd_buffer_size +@@sort_buffer_size + @@join_buffer_size +@@binlog_cache_size +@@thread_stack)) /1024/1024/1024 AS MAX_MEMORY_GB;" self.cursor.execute(sql_cmd) row = self.cursor.fetchone() mem_used_GB = 0 #代为GB mem_used_GB = float(row[0].decode('utf8')) mem_used_percent = mem_used_GB / 16 * 100 return "%.2f" % mem_used_percent except Exception as err: sys.stderr.write(err.__str__()+'n') return -1 #以下类用于检测MySQL数据库的正常与否 class IsAlive(MonitorItem): """监控MySQL数据库是否正常运行、{正常:on line,宕机:off line}""" def get_result(self): if self.cnx != None: return "on line" else: return "off line" #以下类用于检测MySQL数据库的基本信息 class MysqlVariable(MonitorItem): """派生自MonitorItem类，用于所有variable 监控项的基类""" variable_name=None def get_result(self): try: if self.cursor != None: stmt=r"""show global variables like '{0}';""".format(self.variable_name) self.cursor.execute(stmt) return self.cursor.fetchone()[1].decode('utf8') except Exception as err: sys.stderr.write(err.__str__()+'n') return -1 class MysqlPort(MonitorItem): """监控MySQL数据库监听是否正常、{正常:端口号,异常:-1}""" def get_result(self): if self.cnx != None: return self.port else: return -1 class MysqlBasedir(MysqlVariable): """监控MySQL安装目录所在位置，{正常:安装目录位置，异常:-1}""" variable_name="basedir" class MysqlDatadir(MysqlVariable): """监控MySQL数据目录所在位置，{正常:数据目录位置，异常:-1}""" variable_name="datadir" class MysqlVersion(MysqlVariable): """监控MySQL版本号，{正常:版本号，异常:-1}""" variable_name="version" class MysqlServerId(MysqlVariable): """监控MySQL的server_id""" variable_name="server_id" class MysqlLogBin(MysqlVariable): """binlog 是否有开启""" variable_name="log_bin" class MysqlLogError(MysqlVariable): """errorlog文件名""" variable_name="log_error" class MysqlPerformanceSchema(MysqlVariable): """performance_schema是否有开启""" variable_name="performance_schema" class MysqlInnodbBufferPoolSize(MysqlVariable): """监控MySQL innodb_buffer_pool的大小，{正常:缓冲池大小(byte)，异常:-1}""" variable_name="innodb_buffer_pool_size" class MysqlMaxConnections(MysqlVariable): """最大连接数""" variable_name="max_connections" #派生自MonitorItem类，用于所有status 监控项的基类 class MysqlStatu(MonitorItem): """派生自MonitorItem类，用于所有statu 监控项的基类""" statu_name=None def get_result(self): try: if self.cursor != None: stmt=r"""show global status like '{0}';""".format(self.statu_name) self.cursor.execute(stmt) return self.cursor.fetchone()[1].decode('utf8') except Exception as err: sys.stderr.write(err.__str__()+'n') return -1 class MysqlCurrentClient(MysqlStatu): """当前的客户端连接数""" statu_name="Threads_connected" class MysqlTableOpenCacheHitRate(MysqlStatu): """表缓存命中率""" def get_result(self): try: if self.cursor != None: stmt=r"""show global status like 'table_open_cache_hits';""" self.cursor.execute(stmt) hit=float((self.cursor.fetchone()[1].decode('utf8'))) stmt=r"""show global status like 'table_open_cache_misses';""" self.cursor.execute(stmt) miss=float(self.cursor.fetchone()[1].decode('utf8')) return hit/(hit+miss) except Exception as err: sys.stderr.write(err.__str__()) return -1 class MysqlTableOpenCacheOverflows(MysqlStatu): """表缓存溢出次数，如果大于0,可以增大table_open_cache和table_open_cache_instances.""" statu_name="Table_open_cache_overflows" class MysqlTableLocksWaited(MysqlStatu): """因不能立刻获得表锁而等待的次数""" statu_name="table_locks_waited" class MysqlSlowqueries(MysqlStatu): """执行时间超过long_query_time的查询次数，不管慢查询日志有没有打开""" statu_name="slow_queries" class MysqlSortScan(MysqlStatu): """全表扫描之后又排序(排序键不是主键)的次数""" statu_name="sort_scan" class MysqlSortRows(MysqlStatu): """与sortscan差不多，前者指的是sortscan的次数，srotrows指的是sort操作影响的行数""" statu_name="sort_rows" class MysqlSortRange(MysqlStatu): """根据索引进行范围扫描之后再进行排序(排序键不能是主键)的次数""" statu_name="sort_range" class MysqlSortMergePasses(MysqlStatu): """排序时归并的次数，如果这个值比较大(要求高一点大于0)那么可以考虑增大sort_buffer_size的大小""" statu_name="sort_merge_passes" class MysqlSelectRangeCheck(MysqlStatu): """如果这个值不是0那么就要好好的检查表上的索引了""" statu_name="select_range_check" class MysqlQuestions(MysqlStatu): """erver端执行的语句数量，但是每执行一个语句它又只增加一，这点让我特别被动""" statu_name="Questions" class MysqlQcacheFreeMemory(MysqlStatu): """query cache 的可用内存大小""" statu_name="qcache_free_memory" class MysqlPreparedStmtCount(MysqlStatu): """由于本监控程序就是通过prepare语句完成的，所以这个监控项的值最少会是1不是0""" statu_name="prepared_stmt_count" class MysqlOpenedTables(MysqlStatu): """mysql数据库打开过的表，如果这个值过大，应该适当的增大table_open_cache的值""" statu_name="opened_tables" class MysqlOpenTables(MysqlStatu): """当前mysql数据库打开的表数量""" statu_name="open_tables" class MysqlServerLevelOpenFiles(MysqlStatu): """mysql数据库的server层当前正打开的文件数据""" statu_name="open_files" class MysqlInnodbAvailableUndoLogs(MysqlStatu): """innodb当前可用的undo段的数据""" statu_name="innodb_available_undo_logs" class MysqlInnodbNumOpenFiles(MysqlStatu): """innodb当前打开的文件数量""" statu_name="innodb_num_open_files" class MysqlInnodbRowsUpdated(MysqlStatu): """innodb层面执行的update所影响的行数""" statu_name="innodb_rows_updated" class MysqlInnodbRowsRead(MysqlStatu): """innodb 层面受读操作所影响的行数""" statu_name="innodb_rows_read" class MysqlInnodbRowsInserted(MysqlStatu): """innodb 层面受insert操作所影响的行数""" statu_name="innodb_rows_inserted" class MysqlInnodbRowsDeleted(MysqlStatu): """innodb 层面受delete操作所影响的行数""" statu_name="innodb_rows_deleted" class MysqlInnodbRowLockWaits(MysqlStatu): """innodb 行锁等待的次数""" statu_name="innodb_row_lock_waits" class MysqlInnodbRowLockTimeMax(MysqlStatu): """innodb层面行锁等待的最大毫秒数""" statu_name="innodb_row_lock_time_max" class MysqlInnodbRowLockTimeAvg(MysqlStatu): """innodb层面行锁等待的平均毫秒数""" statu_name="Innodb_row_lock_time_avg" class MysqlInnodbRowLockTime(MysqlStatu): """innodb层面行锁等待的总毫秒数""" statu_name="Innodb_row_lock_time" class MysqlInnodbPagesWritten(MysqlStatu): """innodb层面写入磁盘的页面数""" statu_name="Innodb_pages_written" class MysqlInnodbPagesRead(MysqlStatu): """从innodb buffer pool 中读取的页数""" statu_name="Innodb_pages_read" class MysqlInnodbOsLogWritten(MysqlStatu): """innodb redo 写入字节数""" statu_name="Innodb_os_log_written" class MysqlInnodbOsLogPendingWrites(MysqlStatu): """innodb redo log 被挂起的写操作次数""" statu_name="Innodb_os_log_pending_writes" class MysqlInnodbOsLogPendingFsyncs(MysqlStatu): """innodb redo log 被挂起的fsync操作次数""" statu_name="Innodb_os_log_pending_fsyncs" class MysqlInnodbOsLogFsyncs(MysqlStatu): """innodb redo log fsync的次数""" statu_name="Innodb_os_log_fsyncs" class MysqlInnodbLogWrites(MysqlStatu): """innodb redo log 物理写的次数""" statu_name="innodb_log_writes" class MysqlInnodbLogWriteRequests(MysqlStatu): """innodb redo log 逻辑写的次数""" statu_name="Innodb_log_write_requests" class MysqlInnodbLogWaits(MysqlStatu): """innodb 写redo 之前必须等待的次数""" statu_name="Innodb_log_waits" class MysqlInnodbDblwrWrites(MysqlStatu): """innodb double write 的次数""" statu_name="Innodb_dblwr_writes" class MysqlInnodbDblwrPagesWritten(MysqlStatu): """innodb double write 的页面数量""" statu_name="Innodb_dblwr_pages_written" class MysqlInnodbDoubleWriteLoader(MysqlStatu): """innodb double write 压力1~64、数值越大压力越大""" def get_result(self): try: if self.cursor != None: stmt=r"""show global status like 'innodb_dblwr_pages_written';""" self.cursor.execute(stmt) pages=float((self.cursor.fetchone()[1].decode('utf8'))) stmt=r"""show global status like 'innodb_dblwr_writes';""" self.cursor.execute(stmt) requests=float(self.cursor.fetchone()[1].decode('utf8')) if requests == 0: return 0 return pages/requests except Exception as err: sys.stderr.write(err.__str__()) return -1 class MysqlInnodbBufferPoolHitRate(MysqlStatu): """innodb buffer pool 命中率""" def get_result(self): try: if self.cursor != None: stmt=r"""show global status like 'innodb_buffer_pool_read_requests';""" self.cursor.execute(stmt) hit_read=float((self.cursor.fetchone()[1].decode('utf8'))) stmt=r"""show global status like 'innodb_buffer_pool_reads';""" self.cursor.execute(stmt) miss_read=float(self.cursor.fetchone()[1].decode('utf8')) total_read=(miss_read+hit_read) if total_read == 0: return 0 return hit_read/total_read except Exception as err: sys.stderr.write(err.__str__()) return -1 class MysqlInnodbBufferPoolFreePagePercent(MysqlStatu): """innodb buffer pool free page 百分比""" def get_result(self): try: if self.cursor != None: stmt=r"""show global status like 'innodb_buffer_pool_pages_total';""" self.cursor.execute(stmt) total_page=float((self.cursor.fetchone()[1].decode('utf8'))) stmt=r"""show global status like 'innodb_buffer_pool_pages_free';""" self.cursor.execute(stmt) free_page=float(self.cursor.fetchone()[1].decode('utf8')) return free_page/total_page except Exception as err: sys.stderr.write(err.__str__()) return -1 class MysqlInnodbBufferPoolDirtyPercent(MysqlStatu): """innodb buffer pool dirty page 百分比""" def get_result(self): try: if self.cursor != None: stmt=r"""show global status like 'innodb_buffer_pool_pages_total';""" self.cursor.execute(stmt) total_page=float((self.cursor.fetchone()[1].decode('utf8'))) stmt=r"""show global status like 'innodb_buffer_pool_pages_dirty';""" self.cursor.execute(stmt) dirty_page=float(self.cursor.fetchone()[1].decode('utf8')) return dirty_page/total_page except Exception as err: sys.stderr.write(err.__str__()) return -1 class MysqlCreated_tmp_disk_tables(MysqlStatu): """mysql运行时所创建的磁盘临时表的数量，如果这个数值比较大，可以适当的增大 tmp_table_size | max_heap_table_size""" statu_name="Created_tmp_disk_tables" class MysqlComSelect(MysqlStatu): """select 语句执行的次数""" statu_name="com_select" class MysqlComInsert(MysqlStatu): """insert 语句执行的次数""" statu_name="com_insert" class MysqlComDelete(MysqlStatu): """delete 语句执行的次数""" statu_name="com_delete" class MysqlComUpdate(MysqlStatu): """update 语句执行的次数""" statu_name="com_update" class MysqlBinlogCacheDiskUse(MysqlStatu): """事务引擎因binlog缓存不足而用到临时文件的次数，如果这个值过大，可以通过增大binlog_cache_size来解决""" statu_name="Binlog_cache_disk_use" class MysqlBinlogStmtCacheDiskUse(MysqlStatu): """非事务引擎因binlog缓存不足而用到临时文件的次数，如果这个值过大，可以通过增大binlog_stmt_cache_size来解决""" statu_name="Binlog_stmt_cache_disk_use" class MysqlReplication(MonitorItem): """所有监控mysql replication的基类""" def __init__(self,user='monitoruser',password='123456',host='127.0.0.1',port=3306): MonitorItem.__init__(self,user,password,host,port) try: if self.cursor != None: stmt="show slave status;" self.cursor.execute(stmt) self.replication_info=self.cursor.fetchone() except Exception as err: pass class MysqlReplicationIsRunning(MysqlReplication): """mysql replication 是否正常运行""" def get_result(self): if self.replication_info == None: return "replication is not running" else: slave_io_running=self.replication_info[10].decode('utf8') slave_sql_running=self.replication_info[11].decode('utf8') if slave_io_running == 'Yes' and slave_sql_running == 'Yes': return "running" return "replication is not running" class MysqlReplicationBehindMaster(MysqlReplication): """监控seconde behind master """ def get_result(self): if self.replication_info != None: return self.replication_info[32] else: return -1 #监控项字典 items={ #实例配置信息收集项 'port' :MysqlPort, 'baseDir' :MysqlBasedir, 'dataDir' :MysqlDatadir, 'version' :MysqlVersion, 'serverId' :MysqlServerId, 'isBinlogEnable' :MysqlLogBin, 'isErrorlogEnable' :MysqlLogError, 'isPerformanceScheamEnable' :MysqlPerformanceSchema, 'innodbBufferPoolSize' :MysqlInnodbBufferPoolSize, 'maxConnections' :MysqlMaxConnections, #实例运行时信息收集项 'isOnLine' :IsAlive, 'currentConnections' :MysqlCurrentClient, 'tableCacheHitRate' :MysqlTableOpenCacheHitRate, 'tableOpenCacheOverflows' :MysqlTableOpenCacheOverflows, 'tableLocksWaited' :MysqlTableLocksWaited, 'slowqueries' :MysqlSlowqueries, 'sortScan' :MysqlSortScan, 'sortRows' :MysqlSortRows, 'sortRange' :MysqlSortRange, 'sortMergePasses' :MysqlSortMergePasses, 'selectRangeCheck' :MysqlSelectRangeCheck, 'questions' :MysqlQuestions, 'qcacheFreeMemory' :MysqlQcacheFreeMemory, 'preparedStmtCount' :MysqlPreparedStmtCount, 'openedTables' :MysqlOpenedTables, 'openTables' :MysqlOpenTables, 'serverLevelOpenFiles' :MysqlServerLevelOpenFiles, 'created_tmp_disk_tables' :MysqlCreated_tmp_disk_tables, 'comSelect' :MysqlComSelect, 'comInsert' :MysqlComInsert, 'comDelete' :MysqlComDelete, 'comUpdate' :MysqlComUpdate, 'binlogCacheDiskUse' :MysqlBinlogCacheDiskUse, 'binlogStmtCacheDiskUse' :MysqlBinlogStmtCacheDiskUse, 'MysqlDiskUsed' :MysqlDiskUsed, 'MysqlMemUsed':MysqlMemUsed, #innodb运行时信息收集项 'innodbAvailableUndoLogs' :MysqlInnodbAvailableUndoLogs, 'innodbOpenFiles' :MysqlInnodbNumOpenFiles, 'innodbRowsUpdated' :MysqlInnodbRowsUpdated, 'innodbRowsRead' :MysqlInnodbRowsRead, 'innodbRowsInserted' :MysqlInnodbRowsInserted, 'innodbRowsDeleted' :MysqlInnodbRowsDeleted, 'innodbRowLockWaits' :MysqlInnodbRowLockWaits, 'innodbRowLockTimeMax' :MysqlInnodbRowLockTimeMax, 'innodbRowLockTimeAvg' :MysqlInnodbRowLockTimeAvg, 'innodbRowLockTime' :MysqlInnodbRowLockTime, 'innodbPagesWritten' :MysqlInnodbPagesWritten, 'innodbPagesRead' :MysqlInnodbPagesRead, 'innodbOsLogWritten' :MysqlInnodbOsLogWritten, 'innodbOsLogPendingWrites' :MysqlInnodbOsLogPendingWrites, 'innodbOsLogPendingFsyncs' :MysqlInnodbOsLogPendingFsyncs, 'innodbOsLogFsyncs' :MysqlInnodbOsLogFsyncs, 'innodbLogWrites' :MysqlInnodbLogWrites, 'innodbLogWriteRequests' :MysqlInnodbLogWriteRequests, 'innodbLogWaits' :MysqlInnodbLogWaits, 'innodbDblwrWrites' :MysqlInnodbDblwrWrites, 'innodbDblwrPagesWritten' :MysqlInnodbDblwrPagesWritten, 'innodbDoubleWriteLoader' :MysqlInnodbDoubleWriteLoader, 'innodbBufferPoolHitRate' :MysqlInnodbBufferPoolHitRate, 'innodbBufferPoolFreePagePercent' :MysqlInnodbBufferPoolFreePagePercent, 'innodbBufferPoolDirtyPercent' :MysqlInnodbBufferPoolDirtyPercent, #对mysql replication 的监控 'replicationIsRunning' :MysqlReplicationIsRunning, 'replicationBehindMaster' :MysqlReplicationBehindMaster, } # item_key_names=[name for name in items.keys()] if __name__=="__main__": parser=argparse.ArgumentParser() parser.add_argument('--user',default='root',help='user name for connect to mysql') parser.add_argument('--password',default='xxxxxx',help='user password for connect to mysql') parser.add_argument('--host',default='172.31.x.x',help='mysql host ip') parser.add_argument('--port',default=1231,type=int,help='mysql port') parser.add_argument('monitor_item_name',choices=item_key_names) args=parser.parse_args() m=items[args.monitor_item_name](host=args.host,port=args.port,user=args.user,password=args.password) m.print_result()
