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

Moodle Issues: Backup and restore / course import fails

It was one of those strange situations that popped up after a disaster recovery scenario.

The database server suffered hardware failure, but that was quickly resolved and some tests performed on the database -
MYISAMCHK
and
MYSQLCHECK

Both tests indicated no issues.

Soon after I started getting reports of the course imports or (backup/restores) failing, and troubleshooting took a couple hours. There were a lot of leads on the moodle forums but this is the solution to my particular issue. I strongly recommend turning debugging on from the moodle interface, simply to get as much description on the error as possible.
Debugging revealed a cryptic message: Warning: fopen******* [function.fopen]: failed to open stream: No such file or directory in ******/backuplib.php on line ***followed by a bunch of fwrite problems.

PROBLEM MESSAGE: An error occurred while backing up course start

Solution:

0. Login as admin to the moodle interface.

1. Open the mysqladmin tool  (this can also be done from the MYSQL Server)
2. Truncate the following tables:

• mdl_backup_courses
• mdl_backup_files
• mdl_backup_ids

TRUNCATE TABLE tablename;

3.  Run a mysqlcheck with the check and repair flags just in case.

In my case, this solved the problem. The important point to note, is that the debugging may lead you in the correct direction, so DO NOT USE THIS SOLUTION if you are not certain your problem is identical!

Cheers,
-noveck

Moodle Issues after an upgrade! [Resolved!]

Part of my project in migrating to a load-balanced virtualized infrastructure involved running a minor version upgrade from 1.9.7 to 1.9.11 which addressed several bugs in our core Moodle usage. The project plan included checking the database before and after for corruption as well as re-indexing using mysqlcheck and myisamchk respectively, and no errors were detected, both in the Test environment as well as during the upgrade in the production environment. Basic checks were done post upgrade to ensure core moodle functionality. To my surprise (read: shock, horror, pain), two weeks after the upgrade, two major issues were reported by several lecturers:
    1. Quiz attempts were not being saved, where the quiz spanned multiple pages.
    2. Assignments (Advanced Uploading of Files) could not be submitted.

Tracking down this issue took some time, and since so many variables changed from the original environment and so much time had elapsed since the upgrade, reverting was not an option.
The order of troubleshooting included:

•     Determining if the course/activity settings were at fault.
•     Turning on Debugging to check for errors upon avtivity submits.
•     Determining if my (tested) load balancer environment was causing the issue.
•     Checking the new webserver configuration
•     Checking the database configuration
•     Checking and rechecking  and attempting to repair the database for corruption using variations of switches on the respective tools (mysqlcheck and myisamchk)
•     Checking if caching was causing the issue (disabling memcached)

I then decided to try two different tests:

•     Parallel installation of moodle on the same new environment/hardware.
•     Try the current database on different hardware

The first test required using a copy of the existing moodle code, but on a fresh database. To my surprise (read: temporary relief), the environment was fine. The empty database with a test course worked perfectly. The next logical step assumed that the database may be the issue, as the previous test used the same moodle code, effectively eliminating it as the root cause.
The second test confirmed this. I migrated the same Moodle code plus a copy of the database to a clean server and the problem was replicated. At this point the epic horror dawned upon me on the possibilities ahead as visions of rebuilding from scratch haunted me.

At this point, only two variables needed investigating:

•     Database Indexes
•     Database Data/Structure

In my test environment (where the problem was replicated), I attempted to perform some last ditch attempts to recover the environment. Firstly, I attempted to strip all the indexes from the database and use the mysiamchk tool to rebuild.
I took a backup of the database (without indexes) using the following code:
mysqlhotcopy -u root -p yourpassword --addtodest --noindices db_name /path/to/backup/destination/
I then used the MYSQL restore method in my earlier post to restore the database to the test environment.

Since the indexes had to be rebuilt using myisamchk, it required the mysql service to be stopped. The indexes were rebuilt using:
myisamchk -rq /var/lib/mysql/db_name/*.MYI

I then ran the mysqlcheck command as in step 7 of my earlier post.

Again, no errors/issues were reported. I held my breath as I started the test quiz and assignment upload....and it failed again. That meant the data was royally screwed. (...and that's putting it nicely).
I then decided I was going to take a look at the database structure and perhaps even delve into the data itself and try to see where the issues were.

I took mysqldumps of the structure of the working database and the non-working database and compared them line by line in Notepad++ (a tedious task, but some real team effort came into play here), but came to the conclusion that the structure was fundamentally the same. Therefore, by the process of elimination, the root cause was the actual tables/data.
The structure was analyzed using the following:
mysqldump --no-data --host localhost --user=root -p db_name > /path/to/report.sql

On to the analysis of data. Analysis of the tables from the phpmyadmin interface showed no anomalies. In Moodle, there's a database report that is included with the base code which contains a bit more useful data and can be accessed via:
http://yourmoodleurl.com/admin/dbperformance.php

I ran the script and used the view tables link and started scouring the tables one by one, searching for anomalies. Interesting enough, I stumbled upon two very strange issues.
The auto_increment fields for two tables were HUGE, compared to the number of rows the tables contained:

1. mdl_question_sessions
2. mdl_assignment_submissions

The figures were large enough to be beyond the range of the ID field (BIGINT-10) and the names of the questionable tables coincided with the issues we were having. A faint light began to appear at the end of this proverbial tunnel.
The problem lay with the Primay Keys of these two tables. After searching for the Moodle Database schema on Moodle.org (almost a herculian task), and careful analysis of the stable structure I learned that the Primary keys were not foreign keys in other tables. The proverbial light grew a little bigger.

The only way forward at this point would be to delete the entire primary key fields on the two affected tables, recreate the field (and associated options) and reset the auto_increment to 1.

This operation was done from the phpmyadmin interface and needed to be tested significantly to ensure that other functionality would not be negatively affected. Success! It worked in the test environment. The light grew stronger...

I was especially wary about implementation in production, having a very narrow maintenance window to attempt the fix. After the database fix, I re-ran the myisamchk with repair as well as the mysqlcheck to err on the safe side. The operation was a success, and I finally saw the end of the tunnel...Problem solved.

Thoughts: I've yet to determine WHY the moodle upgrade script failed and caused this databse anomaly. From the front-end, the upgrade was successful and NO errors were reported. I've also to understand how the mysqlcheck and myisamchk could not detect that a table field contained data beyond it's configured range. Even turning on Debugging on the Moodle site failed to show anything useful about a failed insert to at least provide a tip in the general direction of the issue.