My file rotating MySQL database dumper

This is a script to be run from a daily cron that created a series of sanely named SQL dump files: weekly, monthly, etc.

Always have that backup ready!

#!/usr/bin/perl -w
#
# No arguments. The program is to be modified to include each database to be archived.
#
#
use strict;
use warnings;

use DateTime;

my $numRotations = 6;    # base 0,  so 6 = 7 rotations (.0 through .6)... plus the new file, so 8 total files
my $fileError;
my $jobError  = 0;
my $jobErrors = "";
my $result;

# Specify a data block for each remote database to be archived.
my %dumpJobs = (
				 'db1' => {
							'database'     => 'db1',
							'dbUser'       => 'db1username',
							'dbPassword'   => 'db1password',
							'dumpFilename' => 'db1.dump.sql',
							'mysqlDumpCmd' => '/usr/bin/mysqldump',
							'tarCmd'       => '/usr/bin/tar',
				 },
);

# Process each specified database dump/archive job.
for my $dumpJob ( sort keys %dumpJobs ) {
	$fileError = 0;
	my $tarballFilename = "$dumpJobs{$dumpJob}{'dumpFilename'}-" . tarDateSegment() . ".tgz";
	my $mysqlDumpCmd    = $dumpJobs{$dumpJob}{'mysqlDumpCmd'};
	my $tarCmd          = $dumpJobs{$dumpJob}{'tarCmd'};
	print "$dumpJob\n";

	my $dumpCommand = "$mysqlDumpCmd ";
	$dumpCommand .= "--user=$dumpJobs{$dumpJob}{'dbUser'} --password=$dumpJobs{$dumpJob}{'dbPassword'} ";
	$dumpCommand .= "$dumpJobs{$dumpJob}{'database'} > $dumpJobs{$dumpJob}{'dumpFilename'}";
	print $dumpCommand . "\n";
	$result = system($dumpCommand );
	if ($result) { $fileError = 1; }

	# create tarball
	if ( !$fileError ) {
		my $makeTarball = "$tarCmd ";
		$makeTarball .= "cvfz $tarballFilename $dumpJobs{$dumpJob}{'dumpFilename'}";
		print $makeTarball . "\n";
		$result = system($makeTarball );
		if ($result) { $fileError = 1; }
	}

	if ($fileError) {
		$jobError = 1;
		$jobErrors .= "$dumpJob ";
	}
}
if ($jobError) {
	warn "Errors were encountered: $jobErrors\n";
	exit(1);
}

# This rotates a series of files Unix log rotation style.
# CURRENTLY UNUSED - KEPT BECAUSE IT IS SO HANDY
#
# Run this with the name of the file to rotate and the max # of rotations, just before you
# create the newest iteration of the file. This will rename the older versions by appending
# ".0" though the number of rotations specified. (So it will actually keep one more than specified,
# including ".0".)
sub rotateFile {
	my ( $filename, $numRotations ) = @_;

	# if the highest exists, delete it
	if ( -f $filename . ".$numRotations" ) { unlink $filename . ".$numRotations"; }

	#
	for ( my $count = $numRotations ; $count >= 1 ; $count-- ) {
		my $fromFilename = $filename . "." . ( $count - 1 );
		my $toFilename = $filename . "." . $count;
		if ( -f $fromFilename ) {
			rename $fromFilename, $toFilename;
		}
	}
	if ( -f $filename ) {
		rename $filename, $filename . ".0";
	}
}

sub tarDateSegment {
	my $dt = DateTime->now();

	my ( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) = localtime(time);
	$year += 1900;
	my $dateTime = sprintf "%4d-%02d-%02d %02d:%02d:%02d", $year, $mon + 1, $mday, $hour, $min, $sec;
	my $date     = sprintf "%4d-%02d-%02d",                $year, $mon + 1, $mday;
	my @weekdays = qw( sun mon tue wed thu fri sat );
	my $weekday  = $weekdays[$wday];
	my @months   = qw( jan feb mar apr may jun jul aug sep oct nov dec );
	my $month    = $months[$mon];

	my $weekOfMonth = $dt->week_of_month;

	my $dateTar = "";

	# if the first day of the year, set $dateTar like: 2009-1st
	if ( $yday == 1 ) {
		$dateTar = "$year-1st";
	}

	# if the first day of the month, set $dateTar like: feb-1st
	elsif ( $mday == 1 ) {
		$dateTar = "$month-1st";
	}

	# if the first day of the week, set $dateTar like: mon-1
	# where the number is the week of the month number
	elsif ( $wday == 1 ) {
		$dateTar = "$weekday-$weekOfMonth";
	}

	# otherwise, set the $dateTar like: mon
	else {
		$dateTar = "$weekday";
	}

	# $sec      seconds          54
	# $min      monutes          37
	# $hour     hour             11
	# $mon      month            4
	# $year     year             2009
	# $wday     weekday          3
	# $yday     day of the year  146
	# $isdst    is DST           1
	# $weekday  day of the week  wed
	# $month    month            may
	# $dateTime date and time    2009-05-27 11:37:54
	# $date     date             2009-05-27
	return $dateTar;
}

=head1 NAME

rtar_mysql.pl - Andrew's remote MySQL archive program.

=head1 SYNOPSIS

    use: rtar_mysql.pl

=head1 DESCRIPTION

This is a program I wrote to SSH/dump/tar/download/rotate archives of MySQL databases.

=over

=back

=head1 LICENSE

None.

=head1 AUTHOR

Andrew Ault <http://www.andrewault.net/>

=cut