#!/usr/bin/perl

#
# Script to connect to a remote MySQL server and make a backup of a large, running, table.
#
# We will connect to the server and create a snapshot volume for the database filesystem.
# We will then rsync the raw database directory to the backup location.
# Once complete, we will then delete the snapshot volume.
#
# Hopefully, the script can work out all the stuff for itself to make it future proof.
#
# Removed gzip compression due to the size of the files and the impact it is having on nfs01.
#
# Added rotation, for multiple copies
#
use strict;
#use warnings;

package My::BMySQL;
our ($host, $umount, $rmdir, $rem_snap, $dbbackup);

# Unbuffered I/O
$| = 1;

my $GZIP        = "/usr/bin/gzip";
my $SAVE_PATH   = "/raid5/mysql-backup";
my $USER        = "backup";
my $PASSWORD    = "";
my $rotate      = 4; #* 0 = disable, >0 number of rotations to perform
my %dbhosts     = ( "sql03" => "uno_mpptwo_prod" );

sub int_handler
{
  my($signal) = @_;
  print "Cleanup called: $signal\n";

  #
  # Unmount the volume and remove the mount point
  #
  if ( $umount == 1 or $rmdir == 1 )
  {
    my $cmd = "\"" . ( $umount == 1 ? "sudo /bin/umount /mnt/dbbackup;" : "" ) . ( $rmdir == 1 ? "sudo /bin/rm -fr /mnt/dbbackup" : "" ) . "\"";
    system("ssh $USER\@$host $cmd > /dev/null 2>&1");
    if ( ($? >> 8) == 0 )
    {
      $umount = 0;
      $rmdir  = 0;
    }
  }

  #
  # Remove the snapshot volume
  #
  if ( $rem_snap == 1 )
  {
    my $cmd = "\"sudo /usr/sbin/lvremove -f $dbbackup\"";
    system("ssh $USER\@$host $cmd > /dev/null 2>&1");
    if ( ($? >> 8) == 0 )
    {
      $rem_snap = 0;
    }
  }
}

foreach $host (keys(%dbhosts))
{
  my $datadir   = "";
  my $rep_secs  = 99999;
  my $rep_table = 0;
  my $db_mountp = "/";
  my $db_device = "";

  # Umount and remove required
  $umount    = 0;
  $rmdir     = 0;

  # Set this once the snapshot volume is created so we know to remove it!
  $rem_snap  = 0;

  # Path to the snapshot device
  $dbbackup;

  # Which database to backup
  my $db = $dbhosts{$host};

  $SIG{'HUP'}      = \&int_handler;
  $SIG{'INT'}      = \&int_handler;
  $SIG{'QUIT'}     = \&int_handler;
  $SIG{'ABRT'}     = \&int_handler;
  $SIG{'KILL'}     = \&int_handler;
  $SIG{'TERM'}     = \&int_handler;
  $SIG{'STOP'}     = \&int_handler;
  $SIG{'__DIE__'}  = \&int_handler;
  $SIG{'__WARN__'} = \&int_handler;

  # STOP FLAG!
  my $can_cont  = 0;

  #
  # Compress the SQL files
  #
  #print "Uncompressing: " . join("/", $SAVE_PATH, $host) . "\n";
  #system("$GZIP -drv " . join("/", $SAVE_PATH, $host));
  #if ( ($? >> 8) != 0 )
  #{
  #  print "Unable to use the save directory!\n";
  #  exit 1;
  #}

  #
  # Find the path to the datafiles
  #
  my $cmd = "\"mysql -u$USER -p$PASSWORD -e 'show variables like \\\"datadir\\\"'\"";
  open INP, "ssh $USER\@$host $cmd 2>&1 |";
  while ( <INP> )
  {
    if ( /datadir\s+(\S+)$/ )
    {
      $datadir  = $1;
      $can_cont = 1;
    }
  }
  close (INP);

  #
  # Find the replication status
  #
  if ( $can_cont == 1 )
  {
    $can_cont = 0;

    my $cmd = "\"mysql -u$USER -p$PASSWORD -e 'show slave status\\\G'\"";
    open INP, "ssh $USER\@$host $cmd  2>&1 |";
    while ( <INP> )
    {
      if ( /Seconds_Behind_Master:\s+(\d+)/ )
      {
        $rep_secs = $1;
        $can_cont = 1;
      }
      elsif ( /Replicate_Do_DB:.*$db.*/ )
      {
        $rep_table = 1;
      }
    }
    close (INP);
  }
  else
  {
    print "FAIL: Could not proceed to show slave status\n";
  }

  #
  # Start getting filesystem information
  #
  if ( $can_cont == 1 )
  {
    $can_cont = 0;

    my $cmd = "df -hP";
    open INP, "ssh $USER\@$host $cmd 2>&1 |";
    while ( <INP> )
    {
      my $fullpath = $datadir . $db;

      if ( /(\S+)\s+\S+\s+\S+\s+\S+\s+\S+\s+(\S+)/ )
      {
        my ($mount,$path) = ($1,$2);

        my $len = length($path);

        if ( index ($fullpath,$path) == 0 )
        {
          if ( index($path,$db_mountp) == 0 )
          {
            $db_mountp = $path;
            $can_cont = 1;
          }
        }
      }
    }
    close (INP);
  }
  else
  {
    print "FAIL: Could not proceed to attain file system information\n";
  }

  #
  # Get the partition device
  #
  if ( $can_cont == 1 )
  {
    $can_cont = 0;

    my $cmd = "grep $db_mountp /etc/fstab";
    open INP, "ssh $USER\@$host $cmd 2>&1 |";
    while ( <INP> )
    {
      if ( /(\S+)\s+.*$db_mountp/ )
      {
        $db_device = $1;
        $can_cont = 1;
      }
    }
    close (INP);
  }
  else
  {
    print "FAIL: Could not proceed to get the partition device\n";
  }

  #
  # We have enough info to create a snapshot and start the backup
  #
  print "[$db_device][$db_mountp][$datadir][$rep_secs][$rep_table]\n";
  
  # Store the device of the snapshot volume
  ($dbbackup = $db_device) =~ s/\/[A-z]*$//;
  $dbbackup .= "/dbbackup";

  #
  # Create the snapshot volume
  #
  if ( $can_cont == 1 )
  {
    $can_cont = 0;

    my $cmd = "\"sudo /usr/sbin/lvcreate -L2G -s -n dbbackup $db_device\"";
    open INP, "ssh $USER\@$host $cmd 2>&1 |";
    while ( <INP> )
    {
      if ( /Logical volume "dbbackup" created/ )
      {
        $can_cont = 1;
        $rem_snap = 1;
      }
    }
    close (INP);
  }
  else
  {
    print "FAIL: Could not continue to create the snapshot volume\n";
  }

  #
  # Create the mount point and mount the snapshot volume
  #
  if ( $can_cont == 1 )
  {
    $can_cont = 0;

    my $cmd = "\"sudo /bin/mkdir -vp /mnt/dbbackup;sudo /bin/mount -vr $dbbackup /mnt/dbbackup\"";
    open INP, "ssh $USER\@$host $cmd 2>&1 |";
    while ( <INP> )
    {
      if ( /mkdir: created directory .*\/mnt\/dbbackup/ )
      {
        print "Directory created OK\n";
        $can_cont++;
        $rmdir   = 1;
      }
      elsif ( /on \/mnt\/dbbackup type .*\(ro\)/ )
      {
        print "Snapshot mounted OK\n";
        $can_cont++;
        $umount  = 1;
      }
    }
    close (INP);
  }
  else
  {
    print "FAIL: Could not continue to mount the snapshot volume\n";
  }

  #
  # Do some backup stuff here (if server is not behind the times)
  #
  if ( $can_cont == 2 )
  {
    $can_cont = 0;

    # Where to rsync the data to.
    my $destdir = join("/", $SAVE_PATH, $host);

    # Do backup rotation.
    if ( $rotate > 0 )
    {
      # Process the first one.
      my $tmppath = $destdir . "." . $rotate;
      ( -d $tmppath ) && system("rm -rf $tmppath");

      # Process all those inbetween
      for ( my $c = ($rotate - 1); $c > 0; $c-- )
      {
        ( -d $destdir . "." . $c ) && rename($destdir . "." . $c, $destdir . "." . ($c+1));
      }

      # Process the last one
      ( -d $destdir ) && rename ($destdir, $destdir . "." . "1");
    }

    if ( $rep_secs < 2 )
    {
      print "Backing up: $db on $host to $destdir";
      my $cmd = "rsync -av --bwlimit=8000 $USER\@$host:/mnt/dbbackup/* $destdir";
      system("$cmd");
      if ( ($? >> 8) != 0 )
      {
        die ("rsync failed!");
      }
    }
  }
  else
  {
    print "FAIL: Could not continue to perform the backup\n";
  }

  # Umount and remove the snapshot volume
  warn ("removing snapshot volume");

  # Restore the default handlers
  $SIG{'HUP'}      = 'DEFAULT';
  $SIG{'INT'}      = 'DEFAULT';
  $SIG{'QUIT'}     = 'DEFAULT';
  $SIG{'ABRT'}     = 'DEFAULT';
  $SIG{'KILL'}     = 'DEFAULT';
  $SIG{'TERM'}     = 'DEFAULT';
  $SIG{'STOP'}     = 'DEFAULT';
  $SIG{'__DIE__'}  = 'DEFAULT';
  $SIG{'__WARN__'} = 'DEFAULT';

  #
  # Compress the SQL files
  #
  #print "Compressing: " . join("/", $SAVE_PATH, $host) . "\n";
  #system("$GZIP -frv " . join("/", $SAVE_PATH, $host));
}