#!/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 - University 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

# ----------------------------------------------------------------------