Pages

Monday, February 18, 2013

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.

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.

1. Test that the remote connection works
From terminal on the monitoring servermysql -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.shActual 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)

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!