#!/usr/bin/perl -w
#
# Quick and dirty dump and time-stamp of listed MySQL databases.
#
# by Timothy M. Kunau
# Note: There will be a point at which this will run out of diskspace for backs
#       and this script has no way of knowing. There is a companion script 
#       (find one-liner) that iterates through the ARCHIVES and erases files over 
#       a certain number of days old.
#       Assumes 'gzip' and 'mysqldump' are in the PATH.
#
#       Admittedly a bit fast and loose with variables and system returns. 
#       It might also be more useful if you could simply pass in the database name
#       as a variable.
#       These, and numerous other improvements, are left as an exercise for the reader.
 
my $workingdir = "/tmp";
my $host       = "localhost";
my $username   = "root";
my $pw         = "THE_ROOT_PASSWORD_FOR_YOUR_MYSQL_INSTANCE_GOES_HERE";
#
# Add a database you want to backup by name to the @dbs array below:
my @dbs        = qw{ hapmap_human concrete gallery2 wordpress };
 
###############  Nothing should need to change beyond this point.
 
# Create a $TIMESTAMP for log entry.
sub get_date {
   # brute force method
 
   my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
 
   if ($year < 10) { $year = "0$year"; }
   $year = $year + 1900;
   if ($mday < 10) { $mday = "0$mday"; }
   if ($hour < 10) { $hour = "0$hour"; }
   if ($min < 10) { $min = "0$min"; }
   if ($sec < 10) { $sec = "0$sec"; }
 
   # Format a $TIMESTAMP for easy sorting
   $monthord = $mon + 1;
   if ($monthord < 10) { $monthord = "0$monthord"; }
   $TIMESTAMP = "$year$monthord$mday$hour$min$sec";
}
 
####
 
# generate TIMESTAMP
&get_date;
 
foreach $db (@dbs) {
 
    # dump databse
    system("mysqldump --flush-logs --opt --host=$host --user=$username --password=$pw $db > $workingdir/$db-$TIMESTAMP.dmp");
 
    # compress dump file to save space
    system("gzip $workingdir/$db-$TIMESTAMP.dmp");
 
}
 
exit;