Mysql Backup Script

This is a little backup script that I’ve been using with mysql for a while now. It takes a mysql dump of the named databases and runs them through bzip2 compression. It also has code make a local and remote copy of the files via mount and rsync, though that should be easy to modify to work by other means.

#!/usr/bin/perl -w
use strict;
use warnings;
use diagnostics;

use Compress::Bzip2;
use Sys::Syslog qw(:DEFAULT setlogsock);

setlogsock('unix');

my @dblist=(
    'database1',
    'database2',
);

# Backup command to run
my $dump='/usr/bin/mysqldump --defaults-extra-file=backup-credentials.cnf -h localhost -u root --opt --flush-logs --single-transaction ';

# Path to backup local backup copy
my $backup_path='/root/mysql-backup/';

# path to remote backup mount point
my $remote_mount='/mnt/backup';

# Full path to where remote backupsa re stored
my $rsync_path="$remote_mount/ezekiel_backup/mysql";

openlog($0,'','user');

log('info',"Staring database backups . . . ");

# do a backup of each database in the dblist
foreach my $db (@dblist) {
	log('info', "Backing up $db");
	my $backup_call=$dump . ' ' . $db;
	my $backup_file=$backup_path . $db . '_' . timestamp() . '.sql.bz2';

	my $bz=Compress::Bzip2->new('c');
	my $fh;

	open(DB, '-|', $backup_call)
		or die "$/Unable to open backup processes!$/";
	open($fh, '>', $backup_file)
		or die "$/ Unable to open backup file!$/";

	# setup the bzip stream
	$bz->bzopen($fh,'w');
	my $out=0;

	while() {
		$out+=$bz->bzwrite($_);
	}

	$bz->bzclose();

	close($fh);
	close(DB);
	log('info', "Wrote $out bytes.");
}

syslog('info', 'Cleaning up old files . . . ');

# do some cleanup
system('find ' . $backup_path .  ' -mtime +14 -name \'*.sql.bz2\' -exec rm {} \;');
system('rsync -a --delete ' . $backup_path .  ' ' . $rsync_path);

log('info', 'Done.');

# generate a standard unix timestamp
sub timestamp() {
	my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime();
	$year+=1900;
	return sprintf("%4d%02d%02d%02d%02d%02d",$year,$mon,$mday,$hour,$min,$sec);
}

# output a log message
sub log {
	my $msg= join ' ', @_;
	print timestamp() . ' ' . $msg . $/;
}

0;

Leave a Reply