Select Git revision
-
Hahn Axel (hahn) authoredHahn Axel (hahn) authored
check_mysqlserver 9.31 KiB
#!/bin/bash
# ======================================================================
#
# 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
# 2023-06-06 v0.3 <axel.hahn@unibe.ch> some shell fixes
# 2023-07-28 v1.0 <axel.hahn@unibe.ch> update help page; fix [client]: command not found
# 2023-08-30 v1.1 <axel.hahn@unibe.ch> reverse return code in ph.hasParamoption to unix like return codes: 0=true; <>0 = false
# 2024-12-09 v1.2 <axel.hahn@unibe.ch> fix showing help
# ======================================================================
. $( dirname $0 )/inc_pluginfunctions
export self_APPVERSION=1.2
# --- set HOME
HOME=/etc/icingaclient
# --- other vars...
cfgfile=$HOME/.my.cnf
myuser=icingamonitor
datafile=/tmp/mysqlvars_$USER.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 help text
function showHelp(){
local _self; _self=$(basename $0)
cat <<EOF
$( ph.showImlHelpHeader )
USAGE:
$_self [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:
$_self -i
$_self -m commands
EOF
}
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
# ----------------------------------------------------------------------
ph.hasParamoption "i" "$@"; bOptInstall=$?
ph.hasParamoption "u" "$@"; bOptUninstall=$?
ph.hasParamoption "h" "$@"; bOptHelp=$?
if [ $bOptHelp -eq 0 -o $# -lt 1 ]; then
showHelp
exit 0
fi
# --- check required tools
ph.require mysql
# --- install
if [ $bOptInstall -eq 0 -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 0 -a "$( whoami )" = "root" ]; then
HOME=/root
_uninstall
rm -f $cfgfile
ph.status "Uninstalled."
ph.exit
fi
# --- check installation
if ! grep $myuser $cfgfile >/dev/null 2>&1; 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
showHelp
ph.abort
esac
ph.status "Mysql $sMode :: $descr"
echo "$out"
rm -f $datafile
ph.exit
# ----------------------------------------------------------------------