#!/usr/bin/perl
 
use strict;
use DBI;
use Time::localtime;
use Time::Local;
use IO::Compress::Gzip qw(gzip $GzipError) ;
use File::Path;
use File::Copy;
 
my $time                = time();
my $tm                  = localtime($time);
my $date                = sprintf("%04d%02d%02d",$tm->year+1900,$tm->mon+1,$tm->mday);
my $backupDir           = "/home/backup/sql/";      # Backup location of the databases
my $dbName              = "mysql";                  # We connect using the main db on the server.
my $dbUser              = "####################";   # Backup User that connect to all databases on server
my $dbPass              = "####################";   # Passwd for the Backup User
my $mysqldump_binary    = "/usr/bin/mysqldump";
my $mysqldump_options   = "-u$dbUser -p$dbPass --lock-tables=false";
 
my $maxLogKept  = "28"; # Max number of days to keep logs
my $maxLogUntar = "1"; # Max number of days to keey logs untarred
 
my @databases;
my $database;
my $filename;
 
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$year   += 1900;
$mon    += 1;
 
### Database backup sections
#connect to the database and downloads it to each file.
 
my $mysql = DBI->connect("DBI:mysql:database=$dbName;host=localhost;port=3306", "$dbUser", "$dbPass");
my $statement = $mysql->prepare("show databases");
$statement->execute;
while(my @row = $statement->fetchrow_array)
{
    my $dbDir = "$backupDir$row[0]";
 
    if(!(-d $dbDir))
    {
        mkdir($dbDir) or die("unable to create backup dir: $!");
    }
    my $dbFile = "$dbDir\/$row[0]-$date\.sql";
    system("$mysqldump_binary $mysqldump_options $row[0] > $dbFile\n");
    #system("/bin/gzip -f $dbFile\n");
 
}
 
opendir(DIR , $backupDir) or die("Can not open $backupDir; $!");
while (defined ($filename = readdir(DIR)) )
{
    if($filename !~ m/(^\.+|lost\+found)/)
    {
        push(@databases, $filename);
    }
}
close(DIR);
 
### File maintence
# this is where I check for old files or files that need to be tared up.
 
my $dbLogDir;
my $tempFileDate;
my %logFiles;
my $dayCount;
my $fileCount;
my $fileLocation;
 
foreach $database (@databases)
{
 
    $dbLogDir = $backupDir."/".$database;
    $filename = "";
    %logFiles = ();
    opendir(DIR , $dbLogDir) or die("Can not open $dbLogDir; $!");
    while (defined ($filename = readdir(DIR)) ) {
        if($filename !~ m/^\.+/) {
            $filename =~ /(\w+-)(\d+)(\.\w+)+/;
            $tempFileDate = $2;
            push @{$logFiles{$tempFileDate}},"$filename";
            $tempFileDate = "";
        }
        else {
            print "$time WARN\tBad filename: $filename\n";
        }
    }
    close(DIR);
    $dayCount = 1;
    foreach my $logByDay (reverse sort { $a <=> $b} keys(%logFiles))
    {
        if($dayCount <= $maxLogUntar) {
            $fileCount = 0;
            while($logFiles{$logByDay}[$fileCount])     {
                print "$time FILE\t$logFiles{$logByDay}[$fileCount]\tOk\n";
                $fileCount++;
            }
        }
        elsif($dayCount >=$maxLogUntar && $dayCount <= $maxLogKept) {
            $fileCount = 0;
            while($logFiles{$logByDay}[$fileCount]) {
                $fileLocation = $backupDir."/".$database."/".$logFiles{$logByDay}[$fileCount];
                if($logFiles{$logByDay}[$fileCount] !~ /(\w+-)(\d+)(\.\w+).gz/ && $logFiles{$logByDay}[$fileCount] !~ /(\w+-)(\d+)(\.\w+).tar.gz/) {
                    print "$time TAR $logFiles{$logByDay}[$fileCount]\t";
                    if(gzip $fileLocation => "$fileLocation.gz"){
                        print "OK\n";
                        print "$time DEL\t$logFiles{$logByDay}[$fileCount]\t";
                        if(unlink($fileLocation)) {
                            print "OK\n";
                        }
                        else{
                            print "Failed: $GzipError\n";
                        }
                    }
                    else
                    {
                        print "Failed: $!\n";
                    }
                }
                else
                {
                    print "$time FILE\t$logFiles{$logByDay}[$fileCount]\tOk\n";
                }
                $fileCount++;
            }
        }
        elsif($dayCount > $maxLogKept){
            $fileCount = 0;
            while($logFiles{$logByDay}[$fileCount]) {
                $fileLocation = $backupDir."/".$database."/".$logFiles{$logByDay}[$fileCount];
                print "$time DEL\t$logFiles{$logByDay}[$fileCount]\t";
                if(unlink($fileLocation))
                {
                    print "OK\n";
                }
                else
                {
                    print "Failed: $!\n";
                }
                $fileCount++;
            }
        }
        else{
            die("YOU SHOULD NEVER HAVE GOTTEN HERE! I am killing the APP!");
        }
        $dayCount++;
    }
    print "$time END\t\t$database\n";
}