nagios-plugins-contrib 14.20141104 / usr / lib / nagios / plugins / pmp-check-mysql-status

#!/bin/sh

# ########################################################################
# This program is part of Percona Monitoring Plugins
# License: GPL License (see COPYING)
# Authors:
#  Baron Schwartz, Roman Vynar
# ########################################################################

# ########################################################################
# Redirect STDERR to STDOUT; Nagios doesn't handle STDERR.
# ########################################################################
exec 2>&1

# ########################################################################
# Set up constants, etc.
# ########################################################################
STATE_OK=0
STATE_WARNING=1
STATE_CRITICAL=2
STATE_UNKNOWN=3
STATE_DEPENDENT=4

# ########################################################################
# Run the program.
# ########################################################################
main() {
   # Get options
   for o; do
      case "${o}" in
         -c)              shift; OPT_CRIT="${1}"; shift; ;;
         --defaults-file) shift; OPT_DEFT="${1}"; shift; ;;
         -C)              shift; OPT_COMP="${1}"; shift; ;;
         -H)              shift; OPT_HOST="${1}"; shift; ;;
         -I)              shift; OPT_INCR="${1}"; shift; ;;
         -l)              shift; OPT_USER="${1}"; shift; ;;
         -L)              shift; OPT_LOPA="${1}"; shift; ;;
         -o)              shift; OPT_OPER="${1}"; shift; ;;
         -p)              shift; OPT_PASS="${1}"; shift; ;;
         -P)              shift; OPT_PORT="${1}"; shift; ;;
         -S)              shift; OPT_SOCK="${1}"; shift; ;;
         -T)              shift; OPT_TRAN="${1}"; shift; ;;
         -w)              shift; OPT_WARN="${1}"; shift; ;;
         -x)              shift; OPT_VAR1="${1}"; shift; ;;
         -y)              shift; OPT_VAR2="${1}"; shift; ;;
         --version)       grep -A2 '^=head1 VERSION' "$0" | tail -n1; exit 0 ;;
         --help)          perl -00 -ne 'm/^  Usage:/ && print' "$0"; exit 0 ;;
         -*)              echo "Unknown option ${o}.  Try --help."; exit 1; ;;
      esac
   done
   # Set default option values
   OPT_COMP="${OPT_COMP:->=}"
   if [ -e '/etc/nagios/mysql.cnf' ]; then
      OPT_DEFT="${OPT_DEFT:-/etc/nagios/mysql.cnf}"
   fi
   if is_not_sourced; then
      if [ -n "$1" ]; then
         echo "WARN spurious command-line options: $@"
         exit 1
      fi
   fi

   # Validate the options.
   OPT_ERR=""
   if [ -z "${OPT_CRIT}${OPT_WARN}" ]; then
      OPT_ERR="you must specify either -c or -w"
   elif [ -z "${OPT_VAR1}" ]; then
      OPT_ERR="you must specify -x"
   elif [ "${OPT_OPER}" -a -z "${OPT_VAR2}" ]; then
      OPT_ERR="you specified -o but not -y"
   elif [ "${OPT_VAR2}" -a -z "${OPT_OPER}" ]; then
      OPT_ERR="you specified -y but not -o"
   elif [ "${OPT_TRAN}" = 'pct'  -a -z "${OPT_VAR2}" ]; then
      OPT_ERR="you specified -T pct but not -y"
   elif [ "${OPT_TRAN}" ]; then
      case "${OPT_TRAN}" in
         pct|str)
            ;;
         *)
            OPT_ERR="-T must be one of: pct str"
            ;;
      esac
   fi
   case "${OPT_COMP}" in
      '=='|'!='|'>='|'>'|'<'|'<=')
         ;;
      *)
         OPT_ERR="-C must be one of: == != >= > < <="
         ;;
   esac
   if [ "${OPT_OPER}" ]; then
      case "${OPT_OPER}" in
         /|'*'|+|-)
            :
            ;;
         *)
            OPT_ERR="-o must be one of: / * + -"
            ;;
      esac
   fi
   if [ "${OPT_ERR}" ]; then
      echo "Error: $OPT_ERR. Try --help."
      exit 1
   fi

   NOTE="UNK could not evaluate the expression."

   # Set up a temporary file
   local TEMP1=$(mktemp -t "${0##*/}.XXXXXX") || exit $?
   local TEMP2=$(mktemp -t "${0##*/}.XXXXXX") || exit $?
   trap "rm -f '${TEMP1}' '${TEMP2}' >/dev/null 2>&1" EXIT

   if get_status_variables "${TEMP1}" "${TEMP2}" "${OPT_INCR}"; then
      LEVEL=$(compute_result "${TEMP1}" "${OPT_VAR1}" "${OPT_OPER}" "${OPT_VAR2}" "${OPT_TRAN}")
      if [ $? = 0 -a -n "${LEVEL}" ]; then
         NOTE="${OPT_VAR1}${OPT_OPER:+ ${OPT_OPER}}${OPT_VAR2:+ ${OPT_VAR2}}${OPT_TRAN:+ (${OPT_TRAN})}"
         NOTE="${NOTE} = ${LEVEL}"

         # XXX Make sure this line and the "case" don't get separated.
         compare_result "${LEVEL}" "${OPT_CRIT}" "${OPT_WARN}" "${OPT_COMP}" "${OPT_TRAN}"
         case $? in
            $STATE_OK)
               NOTE="OK $NOTE"
               ;;
            $STATE_CRITICAL)
               NOTE="CRIT $NOTE"
               ;;
            $STATE_WARNING)
               NOTE="WARN $NOTE"
               ;;
         esac

         # Build the common perf data output for graph trending
         if [ "${OPT_TRAN}" = 'pct' ]; then
            PERFDATA_MAX=100
         fi
         PERFDATA="${OPT_VAR1}${OPT_OPER}${OPT_VAR2}=${LEVEL};${OPT_WARN};${OPT_CRIT};0;${PERFDATA_MAX}"
         NOTE="$NOTE | $PERFDATA"
      fi
   else
      NOTE="UNK could not get MySQL status/variables."
   fi

   echo $NOTE
}

# ########################################################################
# Execute a MySQL command.
# ########################################################################
mysql_exec() {
   mysql ${OPT_DEFT:+--defaults-file="${OPT_DEFT}"} ${OPT_HOST:+-h"${OPT_HOST}"} ${OPT_USER:+-u"${OPT_USER}"} \
      ${OPT_PASS:+-p"${OPT_PASS}"} ${OPT_SOCK:+-S"${OPT_SOCK}"} ${OPT_PORT:+-P"${OPT_PORT}"} \
      ${OPT_LOPA:+--login-path="${OPT_LOPA}"} -ss -e "$1"
}

# ########################################################################
# Compares the variable to the thresholds. Arguments: VAR CRIT WARN CMP TRAN
# Returns nothing; exits with OK/WARN/CRIT.
# ########################################################################
compare_result() {
   local VAR="${1}"
   local CRIT="${2}"
   local WARN="${3}"
   local CMP="${4}"
   local TRAN="${5}"
   echo 1 | awk "END {
      if ( \"${CRIT}\" != \"\" ) {
         if ( \"${TRAN}\" == \"str\" ) {
            if ( \"${VAR}\" ${CMP} \"${CRIT:-0}\" ) {
               exit $STATE_CRITICAL
            }
         } else {
            if ( ${VAR} ${CMP} ${CRIT:-0} ) {
               exit $STATE_CRITICAL
            }
         }
      }
      if ( \"${WARN}\" != \"\" ) {
         if ( \"${TRAN}\" == \"str\" ) {
            if ( \"${VAR}\" ${CMP} \"${WARN:-0}\" ) {
               exit $STATE_WARNING
            }
         } else {
            if ( ${VAR} ${CMP} ${WARN:-0} ) {
               exit $STATE_WARNING
            }
         }
      }
      exit $STATE_OK
   }"
}

# ########################################################################
# Computes an expression against the file of variables.  Returns a float.
# Arguments: TEMP VAR1 OPER VAR2 TRAN
# ########################################################################
compute_result() {
   local TEMP="$1"
   local VAR1="$2"
   local OPER="$3"
   local VAR2="$4"
   local TRAN="$5"
   if [ "${VAR2}" ]; then
      # Extract two variables, apply an operator, and possibly apply a
      # transform.
      awk -F'\t' "
         BEGIN {
            got1 = \"Could not find variable ${VAR1}\";
            got2 = \"Could not find variable ${VAR2}\";
         }
         \$1 == \"${VAR1}\" {
            var1 = \$2;
            got1 = \"\";
         }
         \$1 == \"${VAR2}\" {
            var2 = \$2;
            got2 = \"\";
         }
         END {
            if ( got1 == \"\" && got2 == \"\" ) {
               if ( var2 == 0 && \"${OPER}\" == \"/\" ) {
                  # Divide-by-zero; make the result simply 0
                  val = 0;
               }
               else {
                  val = var1 ${OPER} var2;
               }
               if ( \"${TRAN}\" == \"pct\" ) {
                  val = val * 100;
               }
               if ( val ~ /\.[0-9]/ ) {
                  printf \"%.6f\\n\", val;
               }
               else {
                  print val;
               }
            }
            else {
               print got1, got2 | \"cat 1>&2\";
               exit 1;
            }
         }
         " "${TEMP}"
   else
      # This is the simplest case. We're just extracting a single variable and
      # returning it.
      awk -F'\t' -v var1="${VAR1}" '
         BEGIN {
            got = 0;
         }
         $1 == var1 {
            val = $2;
            got = 1;
         }
         END {
            if ( got == 1 ) {
               print val;
            }
            else {
               print "Unknown variable", var1 | "cat 1>&2";
               exit 1;
            }
         }
      ' "${TEMP}"
   fi
}

# ########################################################################
# Gets status variables.  The first argument is the file to store the results.
# Optional second argument is another temp file.
# Optional third argument makes SHOW STATUS incremental/relative, and has the
# added effect of filtering out non-numeric variables.
# ########################################################################
get_status_variables() {
   if [ "$3" ]; then
      mysql_exec "SHOW /*!50000 GLOBAL*/ STATUS" > "${2}" || exit 1
      sleep "$3"
      # Technically we ought to use another temp file, and check the return
      # status of this mysql_exec, but if the first one worked it's likely that
      # this one will too.
      mysql_exec "SHOW /*!50000 GLOBAL*/ STATUS" | cat "${2}" - \
         | awk -F'\t' '
         /Aborted_clients/ { seen++; }
         {
            if ( seen > 1 && $2 !~ /[^0-9.]/ ) {
               print $1 "\t" $2 - var[$1];
            }
            else {
               var[$1] = $2;
            }
         }
         ' > "${1}"
   else
      mysql_exec "SHOW /*!50000 GLOBAL*/ STATUS" > "${1}" || exit 1
   fi
   mysql_exec "SHOW /*!40101 GLOBAL*/ VARIABLES" >> "${1}"
}

# ########################################################################
# Determine whether this program is being executed directly, or sourced/included
# from another file.
# ########################################################################
is_not_sourced() {
   [ "${0##*/}" = "pmp-check-mysql-status" ] || [ "${0##*/}" = "bash" -a "$_" = "$0" ]
}

# ########################################################################
# Execute the program if it was not included from another file.
# This makes it possible to include without executing, and thus test.
# ########################################################################
if is_not_sourced; then
   OUTPUT=$(main "$@")
   EXITSTATUS=$STATE_UNKNOWN
   case "${OUTPUT}" in
      UNK*)  EXITSTATUS=$STATE_UNKNOWN;  ;;
      OK*)   EXITSTATUS=$STATE_OK;       ;;
      WARN*) EXITSTATUS=$STATE_WARNING;  ;;
      CRIT*) EXITSTATUS=$STATE_CRITICAL; ;;
   esac
   echo "${OUTPUT}"
   exit $EXITSTATUS
fi

# ############################################################################
# Documentation
# ############################################################################
: <<'DOCUMENTATION'
=pod

=head1 NAME

pmp-check-mysql-status - Check MySQL SHOW GLOBAL STATUS output.

=head1 SYNOPSIS

  Usage: pmp-check-mysql-status [OPTIONS]
  Options:
    -c CRIT         Critical threshold.
    --defaults-file FILE Only read mysql options from the given file.
                    Defaults to /etc/nagios/mysql.cnf if it exists.
    -C COMPARE      Comparison operator to apply to -c and -w.
                    Possible values: == != >= > < <=. Default >=.
    -H HOST         MySQL hostname.
    -I INCR         Make SHOW STATUS incremental over this delay.
    -l USER         MySQL username.
    -L LOGIN-PATH   Use login-path to access MySQL (with MySQL client 5.6).
    -o OPERATOR     The operator to apply to -x and -y.
    -p PASS         MySQL password.
    -P PORT         MySQL port.
    -S SOCKET       MySQL socket file.
    -T TRANS        Transformation to apply before comparing to -c and -w.
                    Possible values: pct str.
    -w WARN         Warning threshold.
    -x VAR1         Required first status or configuration variable.
    -y VAR2         Optional second status or configuration variable.
    --help          Print help and exit.
    --version       Print version and exit.
  Options must be given as --option value, not --option=value or -Ovalue.
  Use perldoc to read embedded documentation with more details.

=head1 DESCRIPTION

This Nagios plugin captures SHOW GLOBAL STATUS and SHOW GLOBAL VARIABLES from
MySQL and evaluates expressions against them.  The general syntax is as follows:

  VAR1 [ OPERATOR VAR2 [ TRANSFORM ] ]

The result of evaluating this is compared against the -w and -c options as usual
to determine whether to raise a warning or critical alert.

Note that all of the examples provided below are simply for illustrative
purposes and are not supposed to be recommendations for what to monitor. You
should get advice from a professional if you are not sure what you should be
monitoring.

For our first example, we will raise a warning if Threads_running is 20 or over,
and a critical alert if it is 40 or over:

  -x Threads_running -w 20 -c 40

The threshold is implemented as greater-or-equals by default, not strictly
greater-than, so a value of 20 is a warning and a value of 40 is critical.  You
can switch this to less-or-equals or other operators with the -C option, which
accepts the arithmetic comparison operators ==, !=, >, >=, <, and <=.

You can use any variable that is present in SHOW VARIABLES or SHOW STATUS. If
the variable is not found, there is an error.  To warn if Threads_connected
exceeds 80% of max_connections:

  -x Threads_connected -o / -y max_connections -T pct -w 80

The -T C<pct> option only works when you specify both -x and -y and implements
percentage transformation.  The plugin uses awk to do its
computations and comparisons, so you can use floating-point math; you are not
restricted to integers for comparisons.  Floating-point numbers are printed with
six digits of precision. The -o option accepts the arithmetic operators /, *,
+, and -.  A division by zero results in zero, not an error.

If you specify the -I option with an integer argument, the SHOW STATUS values
become incremental instead of absolute.  The argument is used as a delay in
seconds, and instead of capturing a single sample of SHOW STATUS and using it
for computations, the plugin captures two samples at the specified interval and
subtracts the second from the first.  This lets you evaluate expressions over a
range of time.  For example, to warn when there are 10 disk-based temporary
tables per second, over a 5-second sampling period:

  -x Created_tmp_disk_tables -o / -y Uptime -I 5 -w 10

That is somewhat contrived, because it could also be written as follows:

  -x Created_tmp_disk_tables -I 5 -w 50

The -I option has the side effect of removing any non-numeric SHOW STATUS
variables.  Be careful not to set the -I option too large, or Nagios will simply
time the plugin out, usually after about 10 seconds.

This plugin does not support arbitrarily complex expressions, such as computing
the query cache hit ratio and alerting if it is less than some percentage.  If
you are trying to do that, you might be doing it wrong.  A dubious example for
the query cache might be to alert if the hit-to-insert ratio falls below 2:1, as
follows:

  -x Qcache_hits -o / -y Qcache_inserts -C '<' -w 2

Some people might suggest that the following is a more useful alert for the
query cache:

  -x query_cache_size -c 1

To check Percona XtraDB Cluster node status you may want to use the following
alert similar to what its clustercheck does:

  -x wsrep_local_state -C '!=' -w 4

To compare string variables use -T C<str> transformation. This is required as
numeric and string comparisons are handled differently. The following example
warns when the slave_exec_mode is IDEMPOTENT:

  -x slave_exec_mode -C '==' -T str -w IDEMPOTENT

=head1 PRIVILEGES

This plugin executes the following commands against MySQL:

=over

=item *

C<SHOW STATUS>.

=item *

C<SHOW VARIABLES>.

=back

This plugin executes no UNIX commands that may need special privileges. 

=head1 COPYRIGHT, LICENSE, AND WARRANTY

This program is copyright 2012-2014 Baron Schwartz, 2012-2014 Percona Inc.
Feedback and improvements are welcome.

THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

This program is free software; you can redistribute it and/or modify it under
the terms of the GNU General Public License as published by the Free Software
Foundation, version 2.  You should have received a copy of the GNU General
Public License along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA.

=head1 VERSION

Percona Monitoring Plugins pmp-check-mysql-status 1.1.4

=cut

DOCUMENTATION