#!/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 IO::Socket::INET;

#use warnings;

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

# Unbuffered I/O
$| = 1;

my $GZIP        = "/usr/bin/gzip";
my $SAVE_PATH   = "/backup";
my $USER        = "root";
my $PASSWORD    = "";
my $rotate      = 4; #* 0 = disable, >0 number of rotations to perform
my %vmhosts     = ( "stats01" => "/var/lib/mysql" );

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/vmbackup;" : "" ) . ( $rmdir == 1 ? "sudo /bin/rm -fr /mnt/vmbackup" : "" ) . "\"";
    print " RUN: $cmd\n";
    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 $vmbackup\"";
    print " RUN: $cmd\n";
    system("ssh $USER\@$host $cmd > /dev/null 2>&1");
    if ( ($? >> 8) == 0 )
    {
      $rem_snap = 0;
    }
  }
}

foreach $host (keys(%vmhosts))
{
  my $datadir   = "";
  my $vm_mountp = "/";
  my $vm_device = "";
  my $vg_name   = "";
  my $vg_free   = "";

  # 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;

  # Which database to backup
  my $vm = $vmhosts{$host};

  # Which partition we are going to backup
  $datadir  = $vmhosts{$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;

  #
  # Firstly, check we can reach the server
  my $socket = "";
  my $reason = "";
  for ( my $tries = 0; !$socket && $tries < 3; $tries++ )
  {
    #
    # Connect to the remote IP/port
    $socket = IO::Socket::INET->new( PeerAddr  => $host,
                                     PeerPort  => 22,
                                     Proto     => 'tcp',
                                     Timeout   => 5 ) or $reason .= "\nCan't connect to $host:22 ($!)\n\n";
    #
    # Log multiple attempts to connect to the server
    if ( !$socket )
    {
      print "Try: $tries, $reason\n";
    }
  }

  # Skip the rest if we couldn't connect
  next if not $socket;

  # Close the socket, we only wanted it for testing
  close($socket);

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

    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, $vm_mountp) == 0 )
        {
          $vm_mountp = $path;
          $can_cont = 1;
        }
      }
    }
  }
  close (INP);

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

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

  # Fetch the volume group that this volume resides on.
  # (We could break down the path, but is this 100% reliable?)
  if ( $can_cont == 1 )
  {
    $can_cont = 0;

    my $cmd = "lvdisplay $vm_device";
    open INP, "ssh $USER\@$host $cmd 2>&1 |";
    while ( <INP> )
    {
      chomp;
      if ( /VG Name\s+(\S+)/ )
      {
        $vg_name = $1;
        $can_cont = 1;
      }
    }
    close (INP);
  }
  else
  {
    print "FAIL: Could not proceed to get the volume group name\n";
  }

  # Fetch the space available to create a snapshot
  if ( $can_cont == 1 )
  {
    $can_cont = 0;

    my $cmd = "vgdisplay $vg_name";
    open INP, "ssh $USER\@$host $cmd 2>&1 |";
    while ( <INP> )
    {
      chomp;
      if ( /Free\s+PE\s+\/\s+Size\s+(\d+)\s+\/\s+(\S+)\s+(\S+)/ )
      {
        if ( int($2) > 0 )
        {
          $vg_free = sprintf "%d%s", $2, $3;
          $can_cont = 1;
        }
      }
    }
    close (INP);
  }
  else
  {
    print "FAIL: Could not proceed to get the space available to create a snapshot\n";
  }

  # Work out the rsync offset from the /mnt/vmbackup directory
  my $rsync_path = "/" . substr($datadir, length($vm_mountp));

  # Store the device of the snapshot volume
  ($vmbackup = $vm_device) =~ s/\/[A-z0-9-]*$//;
  $vmbackup .= "/vmbackup";

  # Work out the rsync offset from the /mnt/vmbackup directory
  my $rsync_path = "/" . substr($datadir, length($vm_mountp));
  if ( substr($rsync_path,length($rsync_path) - 1,1) !~ "/" )
  {
    $rsync_path .= "/";
  }

  #
  # We have enough info to create a snapshot and start the backup
  #
  print "[$vm_device][$vg_name][$vg_free][$vm_mountp][$datadir][$vmbackup][$rsync_path]\n";
  
  #
  # Create the snapshot volume
  #
  if ( $can_cont == 1 )
  {
    $can_cont = 0;

    my $cmd = "\"sudo /usr/sbin/lvcreate -L$vg_free -s -n vmbackup $vm_device\"";
    print " RUN: $cmd\n";
    open INP, "ssh $USER\@$host $cmd 2>&1 |";
    while ( <INP> )
    {
      if ( /Logical volume "vmbackup" 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/vmbackup;sudo /bin/mount -vr $vmbackup /mnt/vmbackup\"";
    print " RUN: $cmd\n";
    open INP, "ssh $USER\@$host $cmd 2>&1 |";
    while ( <INP> )
    {
      if ( /mkdir: created directory .*\/mnt\/vmbackup/ )
      {
        print "Directory created OK\n";
        $can_cont += 1;
        $rmdir   = 1;
      }
      elsif ( /on \/mnt\/vmbackup type .*\(ro\)/ )
      {
        print "Snapshot mounted OK\n";
        $can_cont += 2;
        $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 == 3 )
  {
    $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");
    }

    print "Backing up: $vm on $host to $destdir";
    my $cmd = "rsync -av --inplace --bwlimit=8000 $USER\@$host:/mnt/vmbackup${rsync_path}* $destdir";
    print " RUN: $cmd\n";
    system("$cmd");
    die ("rsync failed!") unless ( ($? >> 8) == 0 );
  }
  else
  {
    print "FAIL: Could not continue to perform the backup (\$can_cont = $can_cont)\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));
}