#!/usr/bin/perl
# database_backup                                       Author: Rich West
#                                                       Rich.West@wesmo.com
#                                                       Dec. 12, 2003
#
# Make a text backup of the database.
#
# This can be done live, so it is no big deal.  We keep several
# copies of the backup on-line.
#

use strict;
$|++;

my($DEBUG) = 0;

my($TOTAL_HISTORY)=30;
my($BACKUP_DIR)="/root/etc/backups/database";
my($USER)="root";
my($PASSWORD)="password";
&perform_backup($USER,$PASSWORD,$BACKUP_DIR);

##
# Standard connection to the MySQL database
##
sub connectdb
{
   my ($dbname,$host,$user,$pass) = @_;
   my ($dbh);

   print "Connecting to $dbname.." if ($DEBUG);
   if (!($dbh = DBI->connect("DBI:mysql:database=$dbname;host=$host", $user, $pass,)))
   {
      die "[connectdb] Database connect failed: $dbh->errstr\n";
   }
   return ($dbh);
   print "Done.\n" if ($DEBUG);
}


##
# Archive the old backups.. just in case.
##
sub archive
{
   my ($BACKUP_DIR,$table) = @_;
   my ($REV);

   for ($REV=$TOTAL_HISTORY; $REV>=1; $REV--)
   {
      rename ("$BACKUP_DIR/archive/$table." . ($REV-1) . ".gz", "$BACKUP_DIR/archive/$table.$REV.gz");
   }

   rename ("$BACKUP_DIR/$table.gz", "$BACKUP_DIR/archive/$table.0.gz");
}

##
# Optimize and Repair (if needed) all of the tables
##
sub optimize_and_repair
{
   my ($db_name, $db_user, $db_pass) = @_;
   my ($dbh2, $table);

   use DBI;

   $dbh2 = &connectdb($db_name, "localhost", $db_user, $db_pass);

# Repair and optimize each table
    foreach $table ($dbh2->tables) {
        unless ($dbh2->do("REPAIR TABLE $table")) {
            print "Skipped:  $table\n" if ($DEBUG);
            next;
        };
        if ($dbh2->do("OPTIMIZE TABLE $table")) {
            print "Repaired/Optimized: $table\n" if ($DEBUG);
        }
    }
# Close the database connection
    $dbh2->disconnect;
}

##
# Backup each database
##
sub perform_backup
{
   my ($USER,$PASSWORD,$BACKUP_DIR) = @_;
   my ($qs, $sth, $dbh, @row);

   use DBI;

   $dbh = &connectdb("mysql", "localhost" , $USER, $PASSWORD);

   $qs = "SHOW DATABASES";
   $sth = $dbh->prepare($qs)
      or warn "[get_users] Query had some problem: " . $dbh->errstr . "\n$qs\n";
   $sth->execute
      or warn "[get_users] Query had some problem: " . $sth->errstr . "\n$qs\n";
   while (@row=$sth->fetchrow_array())
   {
      &optimize_and_repair($row[0], $USER, $PASSWORD);
      &archive($BACKUP_DIR,$row[0]);
      system ("/usr/bin/mysqldump -u$USER -p$PASSWORD " . $row[0] . " | gzip > $BACKUP_DIR/" . $row[0] . ".gz");
   }
   $dbh->disconnect;
}