mysql_backups

#!/usr/bin/perl
# backup de databases
# en ruim alles van meer dan twee maanden oud ( maand - 3) op

use Time::localtime;

$year = localtime->year()+1900;
$day = localtime->mday();
$month = localtime->mon()+1;
$vandaag = $year."_".$month."_".$day;   #

my $dirname = "/backups/mysql_$vandaag";

# maak de directory voor vandaag
$mkdircommando = "mkdir $dirname";
system($mkdircommando);

# dingen voor de database connectie
use DBD::mysql;
my $data_source = "DBI:mysql:information_schema:localhost";
# todo: add ENV values here

my ($username, $password, $objectStoreUrl, $objectStoreUser, $objectStorePass) = @ARGV;

my $dbh = DBI->connect( $data_source, $username, $password, { AutoCommit => 1, RaiseError => 1 } );
$dbh->do( "SET TRANSACTION ISOLATION LEVEL READ COMMITTED;" );

# -A Alle databases
# -a maak mysql specifieke create opties aan
# --opt dumpen en niet eerst alles in 't geheugen laden.
# --quote-names zet quote om de namen
# -u username
# -p het geheime password
my $statement = qq(SELECT DISTINCT table_schema as database_name FROM information_schema.tables);
my %databases;

my $sth = $dbh->prepare($statement) or die qq( $dbh->errstr . $statement );
my $rv = $sth->execute or die qq( $sth->errstr . $statement );
while ( $hash_ref = $sth->fetchrow_hashref ) {
        $databases{$hash_ref->{'database_name'}} = $hash_ref->{'database_name'};
}

foreach $database (keys %databases) {
        my $filename = qq($dirname/$database.sql);
        my $dumpcommand= "mysqldump -a --opt --quote-names --single-transaction -u$username -p'$password' --max_allowed_packet=16M --quick $database > $filename";
        system($dumpcommand);
}

# maak er een tar bal van
my $tarbalname = qq($dirname.tgz);
my $tarcommando = qq(tar --remove-files -czf $tarbalname $dirname/);

system($tarcommando);

# copy to the object store
system("curl -v -X PUT -T $dirname.tgz --user $objectStoreUser:$objectStorePass $objectStoreUrl/$dirname.tgz");

my ( $sec, $min, $hour, $day, $month, $year, $wday, $yday, $isdst ) = localtime( time );
$month++;                                                                                               # is 0 based
$year += 1900;                                                                  # is 0 based
use POSIX qw(mktime);
my $timestamp = time;
my $sourcedir = '/backups';
my ( $dirhandle, $file );
opendir( $dirhandle, $sourcedir ) or die( "Can't open dir $sourcedir" );
while ( $file = readdir( $dirhandle ) ) {

        if ( $file =~ /mysql_(\d+)_(\d+)_(\d+).tgz/ ) {
                my ( $file_year, $file_month, $file_day ) = ( $1, $2, $3 );
                my $file_timestamp = mktime( 0, 0, 0, $file_day, $file_month - 1, $file_year - 1900 );
                my $diff = $timestamp - $file_timestamp;
                if ( ( $diff > 3600 * 24 * 90 ) || ( $diff > 3600 * 24 * 7 && $file_day ne '1' && $file_day ne '15' ) ) {
                        eval { system( "rm $sourcedir/$file" ); };
                        if ( $@ ) {
                                $feedback .= "\nRemove old backups FAILED \n reported: " . $@;
                        }
                        # delete from object store; ignore errors
                        eval { system("curl -v -X DELETE --user $objectStoreUser:$objectStorePass $objectStoreUrl/$sourcedir/$file"); }
                }
        }
}

closedir( $dirhandle );