Check MySQL Tables Across Multiple Databases

If you manage multiple databases on a MySQL server that are identical in architecture, but unique in data well I'm sure you may come across this issue at some point in time. This issue being that, the MySQL err log shows an error on a table, but it doesn't specify which database!

(Error looks like this: # 090217 18:38:22 [ERROR] /usr/local/bin/mysqld: Can't open file: 'wp_comments.MYI' (errno: 144)
)

Now sure, you can just run a mysqlcheck on all-databases' tables, but obviously that may take a while, especially if you manage a lot of databases.

So, remember just a few blog posts before, I mentioned a way of monitoring MySQL's err log. Well, to be honest, that was actually a product of my original idea; that idea being:
- monitor the MySQL err log for table errors
- if there is a table error, run a mysqlcheck for that specific table against all databases
- email the results

Well, why only search for one specific error if you can search for them all? So, I broke my script down into two; an err log monitor and a mysqlcheck. Same as always, tailor the code to fit your environment. For example in this line: if ($dbdata->[0] =~ m/^wp_.+/) { I'm only going to search through our Wordpress databases and skip everything else. You can change the regex so that you will only check databases that begin with the letter 'a' if that fits your situation (/^a.+/).

I'm sure you get the point! Eventually (when I'm less busy), I'll modify some of my code so that this will run more of an automated process. --All you need to do is have the err log script write to a file with the tables giving errors and then write a wrapper to process the err log script, then process the mysqlcheck script. But, that's for another day kids!

Cheers!


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

use strict;
use DBI;

if (!$ARGV[0]) {
print "Usage: $0 \" [db_table]\"\n";
print "Note: Use quotes if you are checking multiple tables.\n";
exit;
}

my $db_table = $ARGV[0];

my $mysqlu = "dbuser";
my $mysqlp = qw(dbpass);
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; }
if ($mday < 10) { $mday = 0 . $mday; }
my $todaylog = $year . $mon . $mday . $hour . $min;
my $runlog = "/root/log/chkmysqltables.$todaylog.log";

my @tables;
push @tables, $db_table;
# we only want the unique tables
my %sorthash;
@sorthash{@tables} = ();
my @error_tables = keys %sorthash;

my @dbs;
my $dbh = DBI->connect("DBI:mysql:mysql",$mysqlu,$mysqlp)
|| die "Unable to connect: $!\n";
my $query = "SHOW DATABASES";
my $sth = $dbh->prepare($query)
|| die "Could not prepare: $!\n";
$sth->execute || die "Could not execute: $!\n";
while (my $dbdata = $sth->fetchrow_arrayref) {
if ($dbdata->[0] =~ m/^wp_.+/) {
push(@dbs,$dbdata->[0]);
}
}
$sth->finish;

open(LOG, ">>$runlog");
print LOG "$todaylog\n";
for my $t (@error_tables) {
for my $db (@dbs) {
system "/usr/local/bin/mysqlcheck --password=$mysqlp $db $t >> $runlog\n";
}
}
print LOG "--END LOG--\n";
close(LOG);

my $email_message;
open(MSG, "<$runlog");
$email_message = do {local $/; };
close(MSG);

sendMail( "you\@email.com", "mysqlerr\@email.com",
"mysqlerr: $db_table", "$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);
}