Monitoring the MySQL ERR Log

If you've ever wanted a quick and easy way to monitor MySQL for errors, well, you've stumbled onto the right place.

At work we've been getting these corrupt tables that go unnoticed until an end-user notifies us. Which, of course, for any website is utterly disastrous since some part of your site (even if it is just a minuscule widget on the site) is now broken. So, being the proactive technologists that we are we decided to monitor the MySQL err log daily. You can find your err log in the "data" directory where you installed MySQL (it defaults to the name: hostname.err).

What this perl script does is parse through the err log and find today's ERRORs. After it finds the errors, it then emails them. So, obviously go through and change the variables to your likings and set up a cron to have this run at the end of the day.

Cheers!


#!/usr/bin/perl -w
#
# chkmysqlerrlog.pl v1.0
# 2009-02-17
# Adrian J. Cruz
#

use strict;

my $dbserver = "dbserver.domain.com";
my $email_to = "email\@domain.com";
my $email_from = "mysqlerr\@domain.com";
my $errlog = "/usr/local/mysql/data/dbserver.domain.com.err";
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =
localtime(time);
my $yy = sprintf("%02d", $year % 100);
$year += 1900;
$mon += 1;
if ($mon < 10) {
$mon = 0 . $mon;
}
# set $today in date format: YYMMDD to get today's errors
# e.g.:
# 090217 18:38:22 [ERROR] mysqld: Can't open file: 'ts.MYI' (errno: 144)
my $today = $yy . $mon . $mday;
my $todaylog = $year . $mon . $mday . $hour . $min;

# parse through the mysql err log for ERRORs
my @errors;
open(IN, $errlog);
while () {
chomp;
if ($_ =~ m/$today.+(\[ERROR\].+$)/) {
push(@errors, $1);
}
}
close(IN);

# we only want the unique errors
my %sorthash;
@sorthash{@errors} = ();
my @error_msgs = keys %sorthash;

my $email_message = "$todaylog\n";
for my $e (@error_msgs) {
$email_message .= $e . "\n";
}

if ($email_message && @error_msgs) {
sendMail( $email_to, $email_from,
"$todaylog $dbserver ERRORS", $email_message );
}

sub sendMail {
my ($to, $from, $subject, $message) = @_;
my $sendmail = "/usr/sbin/sendmail";
open(MAIL, "|$sendmail -oi -t");
print MAIL "From: $from\n";
print MAIL "To: $to\n";
print MAIL "Subject: $subject\n\n";
print MAIL "$message\n";
close(MAIL);
}


NOTE: with a little bit of work you can even include the Warnings; we weren't too worried about those.