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()
