Ganglia监控MySQL-白红宇

Ganglia监控MySQL

阅读量：5022 次

发布时间：2019-06-12

本文共 4141 字，大约阅读时间需要 13 分钟。

1、下载mysql监控脚本： [root@node1 app]# wget

2、修改脚本中的msyql用户名和密码

3、该脚本54和58号行机器不同需要进行修改（未查明原因），可以在$[ ]和$(())间替换。我的修改成DELTA_VALUE=$[ NEW_VALUE-PREVIOUS_VALUE ]、DELTA_TIMESTAMP=$[ NEW_TIMESTAMP-PREVIOUS_TIMESTAMP ]就好了。去掉了[]中的$

4、在crond执行该脚本

5、重启gmetad

附gmetric-mysql.sh[root@node1 192.168.228.156]# cat /etc/ganglia/gmetric-mysql.sh #! /bin/bash## Get statistics from MySQL and feed them into Ganglia for monitoring.## To use, simply adjust the path to gmetric and mysql credentials as appropriate.## Author: David Winterbottom (david.winterbottom@gmail.com)# Site:   http://codeinthehole.com# Configdeclare -r GMETRIC=/usr/bin/gmetricdeclare -r NEW_DATA_FILE=/tmp/mysql-stats.newdeclare -r OLD_DATA_FILE=/tmp/mysql-stats.olddeclare -r MYSQL_USER="root"declare -r MYSQL_PASSWORD="123456"# Sanity checksif test -z "$GMETRIC" ; then    printf "The command $GMETRIC is not available";    exit 192fi# Function for submiting metricsfunction record_value{    if [ $# -lt 1 ]; then        printf "You must specify a look-up value\n"        exit 192    fi    LOOKUP_VAR=$1    GANGLIA_NAME=${
   2-unspecified}    GANGLIA_TYPE=${
   3-float}    GANGLIA_UNITS=${
   4-units}    GANGLIA_VALUE=`grep "$LOOKUP_VAR[^_]" "$NEW_DATA_FILE" | awk '{print $2}'`    printf " * $GANGLIA_NAME: $GANGLIA_VALUE\n"    $GMETRIC --type "$GANGLIA_TYPE" --name "$GANGLIA_NAME" --value $GANGLIA_VALUE --unit "$GANGLIA_UNITS"}# Function for submitting delta metricsfunction record_value_rate{    if [ $# -lt 1 ]; then        printf "You must specify a look-up value\n"        exit 192    fi    MYSQL_VAR=$1    GANGLIA_NAME=${
   2-unspecified}    GANGLIA_TYPE=${
   3-float}    GANGLIA_UNITS=${
   4-"per second"}    # Get values from old and new files    PREVIOUS_VALUE=`grep "$MYSQL_VAR[^_]" "$OLD_DATA_FILE" | awk '{print $2}'`    NEW_VALUE=`grep "$MYSQL_VAR[^_]" "$NEW_DATA_FILE" | awk '{print $2}'` #   DELTA_VALUE=$[ $NEW_VALUE-$PREVIOUS_VALUE ]     DELTA_VALUE=$[ NEW_VALUE-PREVIOUS_VALUE ] #   DELTA_TIMESTAMP=$[ NEW_TIMESTAMP-PREVIOUS_TIMESTAMP ]    PREVIOUS_TIMESTAMP=`date -r "$OLD_DATA_FILE" +%s`    NEW_TIMESTAMP=`date -r "$NEW_DATA_FILE" +%s` #   DELTA_TIMESTAMP=$[ $NEW_TIMESTAMP-$PREVIOUS_TIMESTAMP ]     DELTA_TIMESTAMP=$[ NEW_TIMESTAMP-PREVIOUS_TIMESTAMP ]    if [ $DELTA_VALUE -lt 0 ] || [ $DELTA_TIMESTAMP -lt 0 ]; then        # Something strange here - MYSQL may just have started.  Ignore for now        printf "Weird data value - skipping\n"    else        # Need to pipe to bc to perform floating point operations        DELTA_RATE=`echo "scale=4; $DELTA_VALUE/$DELTA_TIMESTAMP" | bc -l`        printf " * $GANGLIA_NAME -- Previous value: $PREVIOUS_VALUE, new value: $NEW_VALUE, delta: $DELTA_VALUE, previous timestamp: $PREVIOUS_TIMESTAMP, new timestamp: $NEW_TIMESTAMP, delta: $DELTA_TIMESTAMP, $DELTA_RATE per second\n"        $GMETRIC --type "$GANGLIA_TYPE" --name "$GANGLIA_NAME" --value $DELTA_VALUE --unit "$GANGLIA_UNITS"    fi}# Read MySQL statistics into a temporary filemysql --user=$MYSQL_USER --password=$MYSQL_PASSWORD --execute "SHOW GLOBAL STATUS" > "$NEW_DATA_FILE"# Submit metricsrecord_value_rate "Connections" "MYSQL_CONNECTIONS" "float" "Connections/sec"record_value_rate "Com_update" "MYSQL_UPDATE_QUERIES" "float" "Queries/sec"record_value_rate "Com_select" "MYSQL_SELECT_QUERIES" "float" "Queries/sec"record_value_rate "Com_insert" "MYSQL_INSERT_QUERIES" "float" "Queries/sec"record_value_rate "Com_delete" "MYSQL_DELETE_QUERIES" "float" "Queries/sec"record_value_rate "Created_tmp_tables" "MYSQL_CREATED_TMP_TABLES" "float" "Tables created/sec"record_value_rate "Slow_queries" "MYSQL_SLOW_QUERIES" "float" "Queries/sec" record_value_rate "Qcache_hits" "MYSQL_QUERY_CACHE_HITS" "float" "Hits/sec"record_value "Qcache_queries_in_cache" "MYSQL_QUERIES_IN_CACHE" "float" "Queries"record_value_rate "Questions" "MYSQL_QUESTIONS" "float" "Questions/sec"record_value_rate "Threads_connected" "MYSQL_THREADS_CONNECTED" "float" "Threads connected/sec"record_value "Threads_running" "MYSQL_THREADS_RUNNING" "float" "Threads running"# Copy datacp "$NEW_DATA_FILE" "$OLD_DATA_FILE"

6、监控效果：

转载于:https://www.cnblogs.com/Richardzhu/p/3685688.html

你可能感兴趣的文章