MySQL data fragmentation using Perl

We are using Nagios XI which stores all kind of monitoring data in MySQL databases - nagios and nagiosql.  Due to high activity a lot of tables get fragmented very often.  Inspired by Sean Hull's article on MySQL data fragmentation I decided to automate this job using Perl.

The script can be executed by any user as long as you have admin level authentication information for MySQL.

You need to edit the following variables before running the script

• $user
• $pass
• $recipients
• $cc (optional) 
• $sender

This Perl script requires DBI, DBD::mysql and MIME::Lite modules, though the last one is required to send e-mail with results to users found in $recipients and $cc.  Mailing support for Windows is currently disabled, however you may want to give it a try.

Prior to writing this script I didn't know that MySQL supports regular expressions: http://goo.gl/8pxIt, for example

my $fragmented_tables = q/select table_schema, table_name from tables where table_schema REGEXP "^nagios(ql)?" and engine="MyISAM" and data_free > 0/;

Here is the script:

#!/usr/bin/perl32
 
# This script is released under the same license(s) as Perl
 
use strict;
use warnings;
use DBI;
 
my $os = $^O;
my $mailing = 0;
 
if ($os =~ /mswin/i) {
    # no support for e-mailing on Windows
    print "** Windows OS detected.  Mailing support is disabled\n";
} else {
    # check to see if we have MIME::Lite installed
    eval ("use MIME::Lite;");
    $@ ? warn ("** MIME::Lite not found, mailing support is disabled\n\n")
        : $mailing++;  # enable mailing support
}
 
# get system hostname
my $hostname = qx/hostname/;
chomp $hostname;
 
# ---------- 8< ----------
# Edit $pass, $recipients, $cc, $sender
# mysql authentication and connection information
my $user = "root";
my $pass = "PASSWORD";
my $db = "information_schema";
my $dsn = "dbi:mysql:$db";
 
# list of comma separated users to send results to in e-mail
my $recipients = 'foo@example.com, bar@example.com';
 
# carbon copy to comma separated list of users
my $cc = '';
 
# send e-mail as mysql@hostname
my $sender = 'mysql@' . $hostname;
 
# ---------- 8< ----------
 
# array container for fragmented, defragmented and problematic tables
my (@fragmented, @defragmented, @problematic);
 
# mysql query to get the list of fragmented tables
my $fragmented_tables = q/select table_schema, table_name from tables where 
    table_schema not in ('information_schema', 'mysql') 
        and engine="MyISAM" and  data_free > 0/;
 
sub notify {
    my ($retval, $data) = @_;
 
    if ($mailing) {
        my $msg = MIME::Lite->new (
            From => 'mysql@' . $hostname,
            To => $recipients,
            Cc => defined ($cc) ? $cc : undef,
            Subject => "MySQL Defragmentation Result - $hostname",
            Data => $data,
        );
 
        $msg->send;
    } else {
        print $data;
    }
    exit $retval;
}
 
my $dbh = DBI->connect ($dsn, $user, $pass) or
    notify (1, "Unable to connect to $db: $DBI::errstr\n");
 
my $sth = $dbh->prepare($fragmented_tables);
$sth->execute();
 
while (my @row = $sth->fetchrow_array()) {
    # store the results in database.table format
    push @fragmented, join ".", @row;
}
 
# construct optimize table query constituting all fragmanted tables
my $optimize_tables = "optimize table " . join (", ", @fragmented);
# print $optimize_tables, "\n";
 
$sth = $dbh->prepare($optimize_tables);
$sth->execute();
 
while (my @row = $sth->fetchrow_array()) {
    # get only tables with return status OK
    # what else could be displayed besides OK?
    ($row[3] =~ /OK/) ? push @defragmented, $row[0] 
        : push @problematic, $row[0];
}
 
my $results;
if (@defragmented) {
    $results = "The following MySQL tables have been defragmented:\n\n";
    $results .= join "\n", @defragmented;
    if (@problematic) {
        $results .= "\n\nProblematic tables:\n\n";
        $results .= join "\n", @problematic;
    }
} else {
    $results = "No defragmentation was required.";
}
 
# notify the recipients with the results
notify(0, $results);
 
END {
    # because they say its a good practice
    $dbh->disconnect if defined $dbh;
}
 
__END__