-
Hahn Axel (hahn) authoredHahn Axel (hahn) authored
check_mysqlserver 9.30 KiB
#!/bin/bash
# ======================================================================
#
# !!! WORK IN PROGRESS !!! DO NOT USE YET !!!
#
# Check MYSQL / MARIADB SERVER
#
# requirements:
# - mysql client
#
# installation:
# - execute check_mysqlserver -i
#
# ----------------------------------------------------------------------
#
# for mysql query cache:
# https://www.techpaste.com/2013/01/query-cache-mysql-server/
#
# ----------------------------------------------------------------------
# 2020-08-xx v0.0 <axel.hahn@iml.unibe.ch>
# 2021-06-08 v0.1 <axel.hahn@iml.unibe.ch> update HOME .. better implement a param and ENV var
# 2021-06-24 v0.2 <axel.hahn@iml.unibe.ch> check if query cache is enabled as a shared function
# ======================================================================
. `dirname $0`/inc_pluginfunctions
_version="0.2"
# --- set HOME
# HOME=/etc/icinga2-passive-client
HOME=/etc/icingaclient
# cd $( dirname $0)
# cd ..
# HOME=$( pwd ); export HOME
# cd - >/dev/null
# --- other vars...
cfgfile=$HOME/.my.cnf
myuser=icingamonitor
datafile=/tmp/mysqlvars.out
# new line
NL="
"
lastvalue=
out=" "
# ----------------------------------------------------------------------
# FUNCTIONS
# ----------------------------------------------------------------------
# uninstall database user
function _uninstall(){
echo UNINSTALL ...
mysql -e "DROP user $myuser@localhost ;"
}
# (re)install database user for monitoring
function _install(){
echo INSTALLING ...
local pwlength=64
echo "- check mysql connection..."
mysql -e ";"
if [ $? -ne 0 ]; then
echo "ERROR: mysql connect without password failed."
exit 1
fi
echo "- creating mysql user $myuser@localhost with random password ($pwlength chars)..."
mypw=$( head /dev/urandom | tr -dc A-Za-z0-9 | head -c $pwlength )
mysql -e "CREATE USER $myuser@localhost IDENTIFIED BY '$mypw';"
if [ $? -ne 0 ]; then
echo "ERROR: mysql command to create user failed."
exit 1
fi
echo "- grant SELECT on mysql tables ..."
mysql -e "GRANT SELECT ON mysql.* TO $myuser@localhost;"
if [ $? -ne 0 ]; then
echo "ERROR: mysql command to grant permissions failed."
exit 1
fi
echo "- flush privileges ..."
mysql -e "FLUSH PRIVILEGES;"
echo "- creating config file $cfgfile ... "
cat >$cfgfile <<EOF
#
# generated on `date`
#
[client]
user=$myuser
host=localhost
password=$mypw
EOF
ls -l $cfgfile
if [ $? -ne 0 ]; then
echo "ERROR: creation of config file failed."
exit 1
fi
}
# show usage
function _usage(){
cat <<EOH
______________________________________________________________________
CHECK MYSQL SERVER :: v${_version}
(c) Institute for Medical Education - Univerity of Bern
Licence: GNU GPL 3
______________________________________________________________________
USAGE:
`basename $0` [OPTIONS] -m METHOD
OPTIONS:
-h this help
-i install monitoring user (must be executed as root)
-u uninstall monitoring user (must be executed as root)
PARAMETERS:
-m method; valid methods are:
connections current/ max connections
connects connects per min and aborted connections/ clients
commands current running statements insert, select, ...
qcache-blocks query cache blocks total vs free
qcache-queries cached / non cached queries; low memeory prunes
qcache-hits query cache efficiency: cached vs non cached and inserted
EXAMPLES:
`basename $0` -i
`basename $0` -m commands
EOH
}
function _mysqlreadvars(){
mysql -e "SHOW GLOBAL VARIABLES ;" --skip-column-names >$datafile
mysql -e "SHOW STATUS ;" --skip-column-names >>$datafile
}
function _mysqlgetvar() {
local sVarname=$1
lastvalue=`grep "^$sVarname[^_a-z]" ${datafile} | awk '{ print $2 }'`
echo $lastvalue
}
# get a value from mysql status output
# param string variable name
# param string prefix to remove from beginning of variable
# param (set) flag if it is a delta value
function _mysqlrendervar() {
local sVarname=$1
local sRemove=$2
local sDeltaUnit=$3
local sFloat=$4
# local iValue=`grep "^$sVarname[^_a-z]" ${datafile} | awk '{ print $2 }'`
local iValue=`_mysqlgetvar "$sVarname"`
if [ "$iValue" = "" ]; then
ph.abort "no value for ${sVarname}"
fi
# get label for perfdata
local sLabel=`echo ${sVarname} | sed "s#^${sRemove}##g"`
if [ ! -z $sDeltaUnit ]; then
local iSpeed=` ph.perfdeltaspeed "mysql-${sVarname}" ${iValue} $sDeltaUnit $sFloat`
out=$out`printf "%25s: %10s %s \n" "${sLabel}" "${iValue}" "... delta = ${iSpeed} per $sDeltaUnit${NL}"`
ph.perfadd "${sLabel}" "${iSpeed}"
lastvalue=$iSpeed
else
out=${out}`printf "%25s: %10s \n" "${sLabel}" "${iValue}${NL}"`
ph.perfadd "${sLabel}" "${iValue}"
lastvalue=$iValue
fi
}
function _mysqlrenderdelta() {
local deltaUnit=$3
test -z $deltaUnit && deltaUnit="sec"
_mysqlrendervar "$1" "$2" $deltaUnit $4
}
# helper function for caching checks
# The function aborts the check ending with OK.
function _verify_cache(){
_mysqlgetvar "have_query_cache" | grep "YES" >/dev/null
if [ $? -ne 0 ]; then
echo "INFO: Query cache (have_query_cache) is not active."
ph.exit
fi
_mysqlgetvar "query_cache_type" | grep "OFF" >/dev/null
if [ $? -eq 0 ]; then
echo "INFO: Query cache (have_query_cache) is active but query_cache_type is OFF."
ph.exit
fi
}
# ----------------------------------------------------------------------
# MAIN
# ----------------------------------------------------------------------
bOptInstall=` ph.hasParamoption "i" "$@"`
bOptUninstall=` ph.hasParamoption "u" "$@"`
bOptHelp=` ph.hasParamoption "h" "$@"`
if [ $bOptHelp -eq 1 -o $# -lt 1 ]; then
_usage
exit 0
fi
# --- check required tools
ph.require mysql
# --- install
if [ $bOptInstall -eq 1 -a "$( whoami )" = "root" ]; then
if [ -f $cfgfile ]; then
ph.status "SKIP installation. config file already exists: $cfgfile."
ph.exit
fi
HOME=/root
export HOME
_uninstall
_install
ph.status "Installation was done"
ph.exit
fi
# --- uninstall
if [ $bOptUninstall -eq 1 -a "$( whoami )" = "root" ]; then
HOME=/root
_uninstall
rm -f $cfgfile
ph.status "Uninstalled."
ph.exit
fi
# --- check installation
grep $myuser $cfgfile >/dev/null 2>/dev/null
if [ $? -ne 0 ]; then
ph.abort "MYSQL access not possible yet. You need to install the monitoring user first: as root execute `basename $0` -i"
fi
# ----------------------------------------------------------------------
# --- get data
_mysqlreadvars
if [ $? -ne 0 ]; then
rm -f $datafile
ph.abort "MYSQL access not possible. Maybe Mysql is not running??"
fi
# --- set optional limits
typeset -i iWarnLimit=` ph.getValueWithParam 0 w "$@"`
typeset -i iCriticalLimit=` ph.getValueWithParam 0 c "$@"`
sMode=`ph.getValueWithParam '' m "$@"`
case "${sMode}" in
"connections")
descr="current/ max connections"
_mysqlrendervar max_connections
typeset -i iMaxConn=$lastvalue
_mysqlrendervar Max_used_connections
typeset -i iMaxUsed=$lastvalue
typeset -i iMaxPercent=$iMaxUsed*100/$iMaxConn
out=$out" --> $iMaxPercent % ${NL}${NL}"
_mysqlrendervar Threads_connected
typeset -i iThreads=$lastvalue
typeset -i iUsage=$iThreads*100/$iMaxConn
out=$out" --> $iUsage % ${NL}${NL}"
if [ $iWarnLimit -gt 0 -a $iCriticalLimit -gt 0 ]; then
ph.setStatusByLimit $iUsage $iWarnLimit $iCriticalLimit
fi
_mysqlrendervar Questions
;;
"connects")
descr="connects per min and aborted connections/ clients"
_mysqlrenderdelta Connections "" min float
_mysqlrenderdelta Aborted_clients "" min float
_mysqlrenderdelta Aborted_connects "" min float
;;
"commands")
descr="currently executed commands"
_mysqlrendervar Com_delete "Com_"
_mysqlrendervar Com_insert "Com_"
_mysqlrendervar Com_replace "Com_"
_mysqlrendervar Com_select "Com_"
_mysqlrendervar Com_update "Com_"
;;
"qcache-blocks")
descr="query cache blocks"
_verify_cache
_mysqlrendervar Qcache_total_blocks "Qcache_"
_mysqlrendervar Qcache_free_blocks "Qcache_"
;;
"qcache-queries")
descr="query cache data"
_verify_cache
_mysqlrendervar Qcache_queries_in_cache "Qcache_"
_mysqlrenderdelta Qcache_not_cached "Qcache_" min float
_mysqlrenderdelta Qcache_lowmem_prunes "Qcache_" min float
;;
"qcache-hits")
descr="query cache hits"
_verify_cache
_mysqlrenderdelta Qcache_hits "Qcache_" min
_mysqlrenderdelta Qcache_inserts "Qcache_" min
_mysqlrenderdelta Qcache_not_cached "Qcache_" min
;;
*)
echo ERRROR: [${sMode}] is an INVALID mode
_usage
ph.abort
esac
ph.status "Mysql $sMode :: $descr"
echo "$out"
rm -f $datafile
ph.exit
# ----------------------------------------------------------------------