Monday, February 18, 2013

Monitoring and reporting on a remote MySQL database.

Depending on your environment, you won't grant every single server on your network the ability to send email. This sort of security precaution had me in a bit of a pickle, as I wanted to step up the mysqlchecks I currently have scripted via cron on my database servers. If your database server has the ability to send email, this solution may not be the best option for you.


I already had a Zabbix Server sitting around, cheerfully gathering data and pumping reports on my little server farm and I thought about giving it some more work to do, without any extra pay.  The zabbix server already had specific port access to these servers, so the security experts at my organization would not give me hell for this particular solution.


The logic was simple. Execute scripted checks from a remote host, run some data processing, if everything is good, just take a timestamp of execution. If everything is not good, send an email for the sysadmin or DBA to action immediately. The nice thing about this script is that it lists the tables that are not flagged as OK by MySQL.

Simple stuff, really.


Assumptions:
Database Server is 192.168.1.10
Monitoring Server is 192.168.1.11
Database to be monitored = mydb
Location of user scripts = /scripts
SSMTP is installed on the monitoring server. Installation Instructions here.

Both servers should already see each other or be able to ssh to/from one another. If not, seek assistance from your company's network gurus (walk with chocolates).

0. Login as root on the Database server.
You'll need to create an account that the remote server will access over the network.

Login as mysql root.

mysql -u root -p
<enter password>

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER on mydb.* to mydbuser@'192.168.1.11' identified by 'mydbuserpassword';

flush privileges;

1. Test that the remote connection works
From terminal on the monitoring server
mysql -u mydbuser -h 192.168.1.10 -p
<enter password>
You should be presented with a mysql prompt.

2. Install script on the Monitoring server
Copy and paste the following into a remotedbcheck.sh file in your scripts directory. Ensure the executable flag is set

chmod +x remotedbcheck.sh

Actual Script
#!/bin/sh
# A Script to execute mysqlcheck -c from a remote server
# and send an email if any table are flagged as NOT OK.
# The tables with issues are sent in the body of the
# email for admin action.
# Written by: Noveck Gowandan
# 09-01-2013
# Version 1.1
# Added script timer which will be appended to new logfile in mylocation (see variables)

##Variables
    # Location of Results and Status Files (default: /tmp)
    mylocation=/tmp 
    # Database to check
    mydbname=mydb
    # Database user with appropriate mysql access
    mydbuser=mydbuser
    # DB pass
    mydbpass=mydbpassword
    # Specify remote database host (IP preferred, hostname if entries are in /etc/hosts)
    mydbhost=192.168.1.10

    # Email variables (Valid Recipent and Sender Email required)
    notifyemail=myemailaddress@myorg.com
    serveremail=monitoring.server@myorg.com

    # These variables can be left as is
    resultsfilename=mysqlcheck-c-results.txt
    statusfilename=mysqlcheck-c-status.txt
    msgtext=`grep -v OK $mylocation/$mydbhost-$resultsfilename`
    status=0
   
# Start timer
time_start=`date +%s`

#Remove any resultsfile that exists
rm -rf $mylocation/$mydbhost-$resultsfilename

#Remove any statusfile that exists
rm -rf $mylocation/$mydbhost-$statusfilename

#Execute mysqlcheck with -c flag only
mysqlcheck -c $mydbname -u $mydbuser -p$mydbpass -h $mydbhost > $mylocation/$mydbhost-$resultsfilename

#Check results file and generate status file
grep -v OK $mylocation/$mydbhost-$resultsfilename | wc -l > $mylocation/$mydbhost-$statusfilename

#Check Status file, send email
status=`cat $mylocation/$mydbhost-$statusfilename`;

if test $status -ge 1
then

ssmtp $notifyemail << EOF
To: $notifyemail
From: $serveremail
Subject: `echo "PROBLEM: MYSQLCHECK "$mydbhost`;

$msgtext
EOF

else

echo "Last Check OK!" >> $mylocation/$mydbhost-$mydbname-check.log
fi

# Stop timer and calculate total time
time_end=`date +%s`
total_time=`expr $(( $time_end - $time_start ))`

# Log output: datestamp and time takes to execute

date >> $mylocation/$mydbhost-$mydbname-check.log
echo "Execution Time was $total_time seconds." >> $mylocation/$mydbhost-$mydbname-check.log
echo "____________" >> $mylocation/$mydbhost-$mydbname-check.log

3. Add script to cron
Depending on your organization and criticality of the service, this may need to be done daily. Given the size of the database in my case and the length of time it takes to run, this example is weekly on Sundays at 2:01 AM.

nano /etc/crontab


Append to file
01 2 * * 0 root /bin/sh /scripts/remotedbcheck.sh


That's it!
The script will execute quietly and will not send any email if everything reports as OK. Be prepared if you do get any email from this script, you should act immediately!

Cheers,
-noveck