ganglia 监控mysql_Ganglia监控MySQL

附gmetric-mysql.sh[root@node1 192.168.228.156]# cat /etc/ganglia/gmetric-mysql.sh

#!/bin/bash

#

# Getstatistics from MySQL and feed them into Ganglia formonitoring.

#

#To use, simply adjust the path to gmetric and mysql credentials asappropriate.

#

# Author: David Winterbottom ([e-mail])

# 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" ; thenprintf "The command $GMETRICis notavailable";exit 192fi

#Function forsubmiting metricsfunctionrecord_value

{if [$# -lt 1]; thenprintf "You must specify a look-up value\n"exit 192fi

LOOKUP_VAR=$1GANGLIA_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 forsubmitting delta metricsfunctionrecord_value_rate

{if [$# -lt 1]; thenprintf "You must specify a look-up value\n"exit 192fi

MYSQL_VAR=$1GANGLIA_NAME=${2-unspecified}

GANGLIA_TYPE=${3-float}

GANGLIA_UNITS=${4-"per second"}

# Getvalues from old andnew 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 fornow

printf "Weird data value-skipping\n"else# Needto pipe to bc toperform 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 metrics

record_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 data

cp "$NEW_DATA_FILE" "$OLD_DATA_FILE"