运维实用脚本合集

一、监控jvm GC指标-shell

主要通过shell脚本采集jvm GC信息推给pushgateway

vi jvm_stat_exporter.sh
!# /bin/ksh
echo  "start ..."
#JAVA_PROCESS_LIST=`jps | grep -v " Jps$" | grep -v " Jstat$"` 
#echo $JAVA_PROCESS_LIST
HOST_IP=`ifconfig -a|grep inet|grep -v 127.0.0.1|grep -v 192.168|grep -v inet6|awk '{print $2}'|tr -d "addr:"`
#echo  "$HOST_IP"
push_jvm_stat()
{
  line=$1
  #echo $line
  PID=`echo $line | cut -d ' ' -f 1`
  PNAME=`echo $line | cut -d ' ' -f 2`
  #echo "PID:$PID,HOST_IP:$HOST_IP,PNAME:$PNAME"

  GC_LINE=`jstat -gc $PID | tail -1`
  #echo "$GC_LINE"
  # S0C    S1C    S0U    S1U      EC       EU        OC         OU       MC     MU    CCSC   CCSU   YGC     YGCT    FGC    FGCT     GCT
  # S0C
  S0C=`echo $GC_LINE | cut -d ' ' -f 1`
  S1C=`echo $GC_LINE | cut -d ' ' -f 2`
  S0U=`echo $GC_LINE | cut -d ' ' -f 3`
  S1U=`echo $GC_LINE | cut -d ' ' -f 4`
  EC=`echo $GC_LINE | cut -d ' ' -f 5`
  EU=`echo $GC_LINE | cut -d ' ' -f 6`
  OC=`echo $GC_LINE | cut -d ' ' -f 7`
  OU=`echo $GC_LINE | cut -d ' ' -f 8`
  MC=`echo $GC_LINE | cut -d ' ' -f 9`
  MU=`echo $GC_LINE | cut -d ' ' -f 10`
  CCSC=`echo $GC_LINE | cut -d ' ' -f 11`
  CCSU=`echo $GC_LINE | cut -d ' ' -f 12`
  YGC=`echo $GC_LINE | cut -d ' ' -f 13`
  YGCT=`echo $GC_LINE | cut -d ' ' -f 14`
  FGC=`echo $GC_LINE | cut -d ' ' -f 15`
  FGCT=`echo $GC_LINE | cut -d ' ' -f 16`
  GCT=`echo $GC_LINE | cut -d ' ' -f 17`
  #echo $S0C $S1C $S0U    $S1U      $EC       $EU        $OC         $OU       $MC     $MU    $CCSC   $CCSU   $YGC     $YGCT    $FGC    $FGCT     $GCT
  #echo "******* $HOST_IP $PNAME *******"
  cat <<EOF | curl --data-binary @- http://192.168.10.10:48888/metrics/job/test_jvm_job/instance/${HOST_IP}_$PNAME
  # TYPE jvm_s0c gauge
  jvm_s0c{processname="$PNAME",hostip="$HOST_IP"} $S0C
  # TYPE jvm_s1c gauge
  jvm_s1c{processname="$PNAME",hostip="$HOST_IP"} $S1C
  # TYPE jvm_s0u gauge
  jvm_s0u{processname="$PNAME",hostip="$HOST_IP"} $S0U
  # TYPE jvm_s1u gauge
  jvm_s1u{processname="$PNAME",hostip="$HOST_IP"} $S1U
  # TYPE jvm_ec gauge
  jvm_ec{processname="$PNAME",hostip="$HOST_IP"} $EC
  # TYPE jvm_eu gauge
  jvm_eu{processname="$PNAME",hostip="$HOST_IP"} $EU
  # TYPE jvm_oc gauge
  jvm_oc{processname="$PNAME",hostip="$HOST_IP"} $OC
  # TYPE jvm_ou gauge
  jvm_ou{processname="$PNAME",hostip="$HOST_IP"} $OU
  # TYPE jvm_mc gauge
  jvm_mc{processname="$PNAME",hostip="$HOST_IP"} $MC
  # TYPE jvm_mu gauge
  jvm_mu{processname="$PNAME",hostip="$HOST_IP"} $MU
  # TYPE jvm_ccsc gauge
  jvm_ccsc{processname="$PNAME",hostip="$HOST_IP"} $CCSC
  # TYPE jvm_ccsu gauge
  jvm_ccsu{processname="$PNAME",hostip="$HOST_IP"} $CCSU
  # TYPE jvm_ygc counter
  jvm_ygc{processname="$PNAME",hostip="$HOST_IP"} $YGC
  # TYPE jvm_ygct counter
  jvm_ygct{processname="$PNAME",hostip="$HOST_IP"} $YGCT
  # TYPE jvm_fgc counter
  jvm_fgc{processname="$PNAME",hostip="$HOST_IP"} $FGC
  # TYPE jvm_fgct counter
  jvm_fgct{processname="$PNAME",hostip="$HOST_IP"} $FGCT
  # TYPE jvm_gct counter
  jvm_gct{processname="$PNAME",hostip="$HOST_IP"} $GCT
EOF
 # echo "******* $PNAME 2 *******"
}
while [ 1 = 1 ]
do
  jps |grep -v " Jps$" | grep -v " Jstat$" | while read line_jps
  do
    push_jvm_stat "$line_jps"
  done
  echo "`date` pushed" > ./lastpushed.log
  sleep 5
done

二、监控mysql指标-python

主要通过python脚本采集mysql信息推给pushgateway

import pymysql
import sys
from prometheus_client import CollectorRegistry, Gauge, push_to_gateway

# 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='192.168.40.20', 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 = pymysql.connect(
                host=self.host,  # 要连接的主机地址
                user=self.user,  # 用于登录的数据库用户
                password=self.password,  # 密码
                # database='testdb',      # 要连接的数据库
                port=self.port,  # 端口，一般为 3306
                charset='',  # 字符编码
                use_unicode=None,  # 是否使用 unicode 编码
                connect_timeout=10  # 连接超时时间，(default: 10, min: 1, max: 31536000)

            )
            # self.cursor = self.cnx.cursor(prepared=True)
            self.cursor = self.cnx.cursor()
        except pymysql.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数据库是否正常运行、{正常:1,宕机:0}"""

    def get_result(self):
        if self.cnx != None:
            return "1"
        else:
            return "0"


# 以下类用于检测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,
    #'isBinlogEnab': MysqlLogBin,
    #'isErrorlogEnab': MysqlLogError,
    #'isPerformanceScheamEnabe ': MysqlPerformanceSchema,
    'innodbBufferPoolSi': MysqlInnodbBufferPoolSize,
    'maxConnectio': MysqlMaxConnections,

    # 实例运行时信息收集项
    'isOnLine': IsAlive,
    'currentConnectio': MysqlCurrentClient,
    'tableCacheHitRa': MysqlTableOpenCacheHitRate,
    'tableOpenCacheOverflos': MysqlTableOpenCacheOverflows,
    'tableLocksWait': MysqlTableLocksWaited,
    'slowqueries': MysqlSlowqueries,
    'sortScan': MysqlSortScan,
    'sortRows': MysqlSortRows,
    'sortRange': MysqlSortRange,
    'sortMergePass': MysqlSortMergePasses,
    'selectRangeChe': MysqlSelectRangeCheck,
    'questions': MysqlQuestions,
    'qcacheFreeMemo': MysqlQcacheFreeMemory,
    'preparedStmtCou': MysqlPreparedStmtCount,
    'openedTables': MysqlOpenedTables,
    'openTables': MysqlOpenTables,
    'serverLevelOpenFil': MysqlServerLevelOpenFiles,
    'created_tmp_disk_tabls': MysqlCreated_tmp_disk_tables,
    'comSelect': MysqlComSelect,
    'comInsert': MysqlComInsert,
    'comDelete': MysqlComDelete,
    'comUpdate': MysqlComUpdate,
    'binlogCacheDiskU': MysqlBinlogCacheDiskUse,
    'binlogStmtCacheDiskUe': MysqlBinlogStmtCacheDiskUse,
    #'MysqlDiskUsd': MysqlDiskUsed,
    #'MysqlMemUsed': MysqlMemUsed,

    # innodb运行时信息收集项
    #'innodbAvailableUndoLos': MysqlInnodbAvailableUndoLogs,
    'innodbOpenFil': MysqlInnodbNumOpenFiles,
    'innodbRowsUpdat': MysqlInnodbRowsUpdated,
    'innodbRowsRe': MysqlInnodbRowsRead,
    'innodbRowsInsert': MysqlInnodbRowsInserted,
    'innodbRowsDelet': MysqlInnodbRowsDeleted,
    'innodbRowLockWai': MysqlInnodbRowLockWaits,
    'innodbRowLockTimeM': MysqlInnodbRowLockTimeMax,
    'innodbRowLockTimeA': MysqlInnodbRowLockTimeAvg,
    'innodbRowLockTi': MysqlInnodbRowLockTime,
    #'innodbPagesWritt': MysqlInnodbPagesWritten,
    #'innodbPagesRe': MysqlInnodbPagesRead,
    #'innodbOsLogWritt': MysqlInnodbOsLogWritten,
    #'innodbOsLogPendingWrits': MysqlInnodbOsLogPendingWrites,
    #'innodbOsLogPendingFsyns': MysqlInnodbOsLogPendingFsyncs,
    #'innodbOsLogFsyn': MysqlInnodbOsLogFsyncs,
    #'innodbLogWrit': MysqlInnodbLogWrites,
    #'innodbLogWriteRequess': MysqlInnodbLogWriteRequests,
    #'innodbLogWai': MysqlInnodbLogWaits,
    #'innodbDblwrWrit': MysqlInnodbDblwrWrites,
    #'innodbDblwrPagesWrittn': MysqlInnodbDblwrPagesWritten,
    #'innodbDoubleWriteLoadr': MysqlInnodbDoubleWriteLoader,
    #'innodbBufferPoolHitRae': MysqlInnodbBufferPoolHitRate,
    #'innodbBufferPoolFreePagePercet': MysqlInnodbBufferPoolFreePagePercent,
    #'innodbBufferPoolDirtyPercet': MysqlInnodbBufferPoolDirtyPercent,

    # 对mysql replication 的监控
    #'replicationIsRunni': MysqlReplicationIsRunning,
    #'replicationBehindMastr': MysqlReplicationBehindMaster,
}


if __name__ == "__main__":
    registry = CollectorRegistry()
    hostIP = '192.168.40.20'
    for myKey in items.keys():
        g = Gauge(myKey, '', ['process', 'instance'],
                  registry=registry)  # Guage(metric_name,HELP,labels_name,registry=registry)
        g.labels(myKey, '192.168.40.10').set(items[myKey].get_result(items[myKey]()))
        print(items[myKey].get_result(items[myKey]()))
        push_to_gateway('192.168.40.10:9091', job='mysql_status', registry=registry)

三、获取系统性能指标-python

1、获取系统信息

import platform

def showInfo(tip, info):
    print("{}:{}".format(tip, info))


showInfo('操作系统和版本信息：', platform.platform())
showInfo('系统版本号：', platform.version())
showInfo('系统名称：', platform.system())
showInfo('系统位数：', platform.architecture())
showInfo('计算机类型：', platform.machine())
showInfo('计算机名称：', platform.node())
showInfo('处理器类型：', platform.processor())
showInfo('计算机相关信息：', platform.uname())

2、获取主机ip

'''
获取主机ip
'''


def getHostIP():
    import socket
    # return socket.gethostbyname(socket.gethostname())
    try:
        s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
        s.connect(('114.114.114.114', 80))
        ip = s.getsockname()[0]
    except Exception as e:
        print(e)
    finally:
        s.close()
    return ip

3、获取主机ips

def checkDevice(device_name):
    import re
    list1 = ['docker', 'lo']
    list2 = []
    for n in list1:
        p = re.compile('^('+n+'.*)$')
        if p.match(device_name):
            list2.append(True)
        else:
            list2.append(False)
    if True in list2:
        return False
    else:
        return True

'''
获取主机ips
'''


def getHostIPS(host_ips):
    import re
    import psutil
    for key, info in psutil.net_if_addrs().items():
        m = checkDevice(key)
        if not m:
            pass
        else:
            for i in range(1, len(info)):
                p = re.compile('^((25[0-5]|2[0-4]\d|[01]?\d\d?)\.){3}(25[0-5]|2[0-4]\d|[01]?\d\d?)$')
                if p.match(info[i][1]) and info[1][1] != '127.0.0.1':
                    host_ips.append([key, info[i][1], info[i][2]])
    return host_ips

4、获取主机name

'''
获取主机name
'''


def getHostName():
    import socket
    return socket.gethostname()

5、获取cpu信息

'''
获取cpu信息
cpu_info=[cpu物理核数,cpu核心数，cpu使用率]
'''


def getCpuInfo(cpu_info):
    import psutil
    cpu_info.append(str(psutil.cpu_count(logical=False)))
    cpu_info.append(str(psutil.cpu_count()))
    cpu_info.append(str(psutil.cpu_percent(1)))
    return cpu_info

6、获取内存信息

'''
获取内存信息
mem_info=[总内存大小M，内存剩余大小M，内存使用率，交换空间内存信息]
'''


def getMemInfo(mem_info):
    import psutil
    mem = psutil.virtual_memory()
    mem_info.append(int(mem.total / 1024 / 1024))
    mem_info.append(int(mem.free / 1024 / 1024))
    mem_info.append(str(mem.percent))
    mem_info.append(str(psutil.swap_memory()))
    return mem_info

7、获取磁盘信息

'''
获取磁盘信息
disk_info = [’盘符‘,磁盘总空间G,已经使用百分比]
'''


def getDiskInfo(disk_info):
    import psutil
    device = []
    disk_part = psutil.disk_partitions()
    for i in range(0, len(disk_part)):
        if disk_part[i][2] != '':
            device.append(str(disk_part[i][0]))
    for m in range(0, len(device)):
        disk_usage = psutil.disk_usage(device[m])
        disk_info.append([device[m], int(disk_usage[0] / 1024 / 1024 / 1024), disk_usage[len(disk_usage) - 1]])
    return disk_info

8、获取网卡流量信息

'''
获取网卡信息:
key_info：网卡名称数
recv：网卡接收流量大小总数bite
bytes_sent：网卡发送流量大小总数bite
'''
def getNetKeys():
    import psutil
    key_info = psutil.net_io_counters(pernic=True).keys()
    recv = {}
    sent = {}
    for key in key_info:
        recv.setdefault(key, psutil.net_io_counters(pernic=True).get(key).bytes_recv)
        sent.setdefault(key, psutil.net_io_counters(pernic=True).get(key).bytes_sent)
    return key_info, recv, sent


'''
获取网卡信息:
key_info：网卡名称数
net_in：最近1s网卡接收流量大小kb
net_out：最近1s网卡发送流量大小kb
'''
def getNetRate():
    import time
    key_info, old_recv, old_sent = getNetKeys()
    time.sleep(1)
    key_info, now_recv, now_sent = getNetKeys()
    net_in = {}
    net_out = {}
    for key in key_info:
        net_in.setdefault(key, round((now_recv.get(key) - old_recv.get(key)) / 1024, 2))
        net_out.setdefault(key, round(((now_sent.get(key)) - old_sent.get(key)) / 1024, 2))
    return key_info, net_in, net_out

'''
获取网卡流量信息：
net_info = [1s总接受流量大小kb,1s总发送流量大小,[[网卡名称,1s接受流量大小kb,1s发送流量大小],.....]]
'''
def getNetInfo(net_info):
    import psutil
    import time
    key_info, net_in, net_out = getNetRate()
    net_rate = []
    for key in key_info:
        net_rate.append([key, net_in[key], net_out[key]])
    old_recv, old_sent = {}, {}
    old_sent = psutil.net_io_counters()[0] / 1024
    old_recv = psutil.net_io_counters()[1] / 1024
    time.sleep(1)
    now_sent = psutil.net_io_counters()[0] / 1024
    now_recv = psutil.net_io_counters()[1] / 1024
    net_info.append(round(now_recv - old_recv, 2))
    net_info.append(round(now_sent - old_sent, 2))
    net_info.append(net_rate)
    return net_info