MariaDB

Your database can send you important information by instant message!

A database is always changing and you need to keep tabs on what is happening. Here are two examples of querying the Mariadb database and returning the results to a mobile phone by way of an XMPP instant message. These are bash scripts that can be run from cron.

Can we connect and query?

This first example will report whether or not we can connect and query.

#!/bin/bash
# by Edward Stoever
/usr/bin/sleep $((1 + $RANDOM %55)) # include this when running from cron
. /etc/profile 2>/dev/null
. /root/.profile 2>/dev/null

TT=`echo "use mysql; select 'success' from db limit 1;"`

PASSWORD='my_password'
HOST_IP='192.168.0.11'
DB_USER='edward'
ANSWER=`echo $TT | mysql -u $DB_USER -p$PASSWORD --silent -h $HOST_IP 2>&1`

# UNCOMMENT NEXT LINE FOR TESTING WITHOUT SHUTTING DOWN MARIADB
# ANSWER="ERROR 2002 (HY000): Can't connect to MySQL server on '192.168.0.11' (115)"

# CLEANUP VARIABLE
ANSWER=`echo $ANSWER | head -n1 | awk '{print $1;}'`

if [ $ANSWER == "success" ]; then
  echo "Can Query"
  /usr/local/bin/monitor.chat.sh "<FROG> MariaDB is visible from the app server."
else
  echo "Cannot Query"
  /usr/local/bin/monitor.chat.sh "<REDFLAG> MariaDB is not visible from the app server."
fi

Text messages for when the database is down, and when it is up.

Are there any recent slow queries?

If you configure your mariaDB instance to record slow query data to a table, you can keep informed as to how well your database is performing by instant message.

#!/bin/bash
# by Edward Stoever
. /etc/profile 2>/dev/null
. /root/.profile 2>/dev/null
/usr/bin/sleep $((1 + $RANDOM %55)) # include this when running from cron

TT="select concat(if(count(*)>0,'<TURTLE>','<RABBIT2>'),
                  ' MariaDB has logged ',
                  cast(count(*) as char),
                  ' slow ',
                  if(count(*)=1,'query','queries'),
                  ' in the past 12 hours.')
    from mysql.slow_log
    where start_time > now() - interval 12 hour and sql_text<>'COMMIT'
union all
   select concat(if(count(*)>0,'<TURTLE>','<RABBIT2>'),
                 ' MariaDB has logged ',
                 cast(count(*) as char),
                 ' slow ',
                 if(count(*)=1,'commit','commits'),
                 ' in the past 12 hours.')
    from mysql.slow_log
    where start_time > now() - interval 12 hour and sql_text='COMMIT'"

PASSWORD='my_password'
HOST_IP=192.168.0.11
DB_USER='edward'
SLOW=`echo $TT | mysql -u $DB_USER -p$PASSWORD --silent -h $HOST_IP`

/usr/local/bin/monitor.chat.sh "$SLOW"

The results of our slow query sent by XMPP instant message.

Last modified December 15, 2020