mysql-slave-check

#!/usr/bin/perl -w

=head1 NAME

mysql-slave-check - Check on the status of a MySQL slave.

=cut

=head1 SYNOPSIS

  General Options:

   --help      Show the help information for this script.
   --verbose   Show useful debugging information.

=cut


=head1 ABOUT

mysql-slave-check is a script which is designed to be executed by
cron (ideally /etc/cron.hourly or /etc/cron.daily) from which it
will report upon the status of the MySQL replication.

The testing is carried out using the login from the Debian-specific
/etc/mysql/debian.cnf file.

NOTE: In all cases the return code of the script will be zero, this
is to avoid noise from cron.

=cut

=head1 REPLICATION TESTING

The replication is regarded as being "OK" if all the following three
conditions are true:

=over 8

=item The "Slave_IO_Running" setting shows "Yes".

=item The "Slave_SQL_Running" setting shows "Yes".

=item The replication lag is less than 24 hours

=back

=cut

=head1 AUTHOR

 Steve
 --
 http://www.steve.org.uk/

=cut


=head1 LICENSE

Copyright (c) 2013 by Steve Kemp.  All rights reserved.

This script is free software; you can redistribute it and/or modify it under
the same terms as Perl itself.

The LICENSE file contains the full text of the license.

=cut

use strict;
use warnings;

use English;
use Getopt::Long;
use Pod::Usage;


#
#  Get the options, either defaults or from the command line.
#
my %config = parsedOptions();


#
#  If we're not root we exit here.
#
if ( $UID != "0" )
{
    print <<EOF;
You must launch this script as root.
EOF
    exit(0);
}


#
#  If the Debian configuration file doesn't exist
#
if ( !-e "/etc/mysql/debian.cnf" )
{
    print <<EOF;
This script expects the file /etc/mysql/debian.cnf to contain suitable
login details.

For example:

[client]
host     = localhost
user     = root
password = some-pass
socket   = /var/run/mysqld/mysqld.sock

Please populate this file before re-running this script.  Take care that
this file is not world-readable.

EOF
    exit(0);
}

#
#  Run the command to check the slave
#
my $cmd =
  "mysql --defaults-file=/etc/mysql/debian.cnf --batch -e 'show slave status\\G'";

#
#  Run the command, capturing errors.
#
my $output = `$cmd 2>&1`;

#
#  If this fails then we've an error
#
if ($?)
{
    print <<EOF;
Failed to execute the status-checking command:

$cmd
EOF
    exit(0);
}

#
# Get the delay
#
my $delay = undef;
if ( $output =~ /Seconds_Behind_Master: (\d+)/gi )
{
    $delay = $1;
}

#
#  Otherwise we have the output so we can look for sane values.
#
if ( ( $output =~ /Slave_IO_Running: Yes/i ) &&
     ( $output =~ /Slave_SQL_Running: Yes/i ) &&
     ( defined($delay) ) &&
     ( $delay < ( 60 * 60 * 24 ) ) )
{
    $config{ 'verbose' } && print "The slave is running, successfully.\n";
    $config{ 'verbose' } && print "Replication lag: $delay seconds\n";
    exit(0);
}


#
#  Error
#
print <<EOF;
The replication appears to show an error:

$output

EOF

$config{ 'verbose' } && print "This output was generated by\n\n$cmd\n";
exit(0);



=begin doc

Parse the options and return suitable values.

=end doc

=cut

sub parsedOptions
{
    my %vars;

    exit
      if (
           !GetOptions( "help"    => \$vars{ 'help' },
                        "verbose" => \$vars{ 'verbose' } ) );

    pod2usage(1) if ( $vars{ 'help' } );

    return (%vars);

}