Backup MySQL Database

#!/usr/bin/perl -w
#
# PERL MODULES WE WILL BE USING
use Mysql;
use POSIX qw/strftime/;

# MySQL CONFIG VARIABLES
$database = "my_database_name";
$user = "my_database_user";
$pw = "my_db_password";
# number of days to keep
$keepFiles = 30;
# first arg passed to go to safe mode
# contributed by Tim Ederer
if(defined($ARGV[0]))
{
	$safe = $ARGV[0];
}
else
{
	$safe = '';
}

#mysql dump options
$options = "--add-drop-database --quick";
#for archive file name date
$qtime = strftime("%Y-%m-%d", localtime);
#location of the gz file
$location = "/my/site/archive/path/";

#table name to query from
@tables = ('table_1','table_2','table_3');

#first part of the mysql cmd that we can create outside of the loop
$mysqlCmd = "mysqldump -u " . $user . " " . "-p" . $pw . " " . $database . " ";

# run the dump for each table listed in the array
foreach (@tables) {
	#name for the backup file
	# if it is safe  do not date the file
	if($safe eq "safe"){
 		$buFile = $_ . " " . $options . "|gzip > " . $location . $_ . ".sql.gz";
 	}else {
 		$buFile = $_ . " " . $options . "|gzip > " . $location . $qtime . "_" . $_ . ".sql.gz";
 	}
 	# run the mysqldump
 	system("$mysqlCmd$buFile");
}

# open the directory and create the list of files to delete
opendir(DIR,"$location");
@files =grep(/.sql.gz/,readdir(DIR));
closedir(DIR);

# delete files older than $keepFiles
# only delete files that this script created - $location@files
foreach(@files) {
	if(-M "$location$_" > $keepFiles) {
		unlink("$location$_");
	}
}

Dump Archive and Remove MySql Records

#!/usr/bin/perl -w
###################################################################################################
#File path: /Volumes/jbroussard/Documents/SeatExpert/scripts/se_tp_query_cache.pl
#
#run on server: db3 (webflyer.com) 
#
#Script to dump records from the seatexpert.com xml query cache then delete the same record.
#GZ files are placed in a directory that another script pulls down those files for backup.
#
#We run this script weekly via Crontab.  We intend on keeping 30 days of cache in the db.
#
#This script can be used for two tables, just change the table name and file name.
#We just use two scripts 
#
#By: Jamie Broussard
#2009-06-26
#Last update:
#2009-06-29
#
###################################################################################################
#
#
# PERL MODULES WE WILL BE USING
use Mysql;
use POSIX qw/strftime/;

# MySQL CONFIG VARIABLES
$host = "localhost";
$database = "seatexpert";
$user = "seatexpert_cache_bu";
$pw = "";

#mysql dump options, just so that the command is easier to read
#we don't want to use this dump as a full backup.  these options create insert only statements
$options = "--skip-add-drop-table --no-create-info --quick";
#how many days of cache do we want to keep
#at the time this script was created, we decided on 30 days
$daysbefore = "30";
#for archive date query and file name date
$qtime = strftime( "%Y%m%d", localtime(time()-(24*60*60*$daysbefore)) );
#location of the gz file
$location = "/mysql/hotcopy/";
#name for the backup file with out the date
$filename =".se_tp_query_cache.sql.gz";
#table name to query from
$table = "travelport_query_cache";
#full path to this months archive, we want to know if it exists before the delete
$fullfilename = "$location$qtime$filename";

#remove last months archive or any files that match the filename
system( "rm -f /$location*$filename" );

# dump the data that we don't want in the db any longer
system ( "mysqldump -u $user -p$pw $database $table $options --where='cache_date < $qtime'|gzip > $fullfilename" );

#run delete only if file exists
if (-e $fullfilename) {
        #PERL MYSQL CONNECT
        $connect = Mysql->connect($host, $database, $user, $pw);
        # SELECT DB
        $connect->selectdb($database);
        # DEFINE A MySQL QUERY
        $query = "DELETE FROM $table WHERE cache_date < '$qtime';";
        # EXECUTE THE QUERY FUNCTION
        $execute = $connect->query($query);
        #CLEAN UP THE TABLE AFTER DELETE
        #PERL MYSQL CONNECT
        $connect = Mysql->connect($host, $database, $user, $pw);
        # SELECT DB
        $connect->selectdb($database);
        # DEFINE A MySQL QUERY
        $query = "OPTIMIZE TABLE `$table`;";
        # EXECUTE THE QUERY FUNCTION
        $execute = $connect->query($query);

}
else
{
        print "Archive failed!";
}