Perl Script to Backup MySQL Databases

So far, we covered backing up files locally and over a network. Now let let see how to backup databases(only mysql supported – yet).

The script uses ‘mysqldump’ command to backup the data. That means that the backups are in the SQL dump format. The dumps of all the databases that are backed up are compressed and stored in the destination folder. They will be named in this format – YYYY-MM-DD.tar.gz.

Configuration File

This script reads a configuration file named ‘dbbackup.config’ and backups all the databases specified in that file to another location in the same system. This configuration file must be in the same folder as the perl script. The configuration file format is given below…

Data
Project_Nexty
App_activecollab
# Unwanted_DB - commented - will not be backedup
binco
binnyva

The Perl Script


#!/usr/bin/perl
# Backups all the databases specified in the dbbackup.config file

$backup_folder = '/var/Backup/Special/Databases'; #EDIT THIS LINE

use File::Basename;
my $config_file = dirname($0) . "/dbbackup.config";
my @databases = removeComments(getFileContents($config_file));

chdir($backup_folder) or die("Cannot go to folder '$backup_folder'");

my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$year += 1900;
$mon++;
#Zero padding
$mday = '0'.$mday if ($mday<10);
$mon = '0'.$mon if ($mon<10);

my $folder = "$year-$mon-$mday";
mkdir($folder) or die("Cannot create a folder called '$folder'");

foreach my $database (@databases) {
	next if ($database eq '');
	chomp($database);
	
	my $table = '';
	if(index($database,' ')+1) { #Get just 1 table in the database - if there is a ' '(space) in the db name
		my @parts = split(' ',$database);
		$database = $parts[0];
		$table = $parts[1];
	}
	
	print "Backing up $database ... ";

 	my $file = $database;
 	$file .= '_' . $table if($table ne '');
 	$file .= ".sql";
 	
 	`mysqldump -u root $database $table > $folder/$file`;

	print "Done\n";
}
print "Compressing the folder ... ";
`tar -czf $folder.tar.gz $folder/`;
print "Done\nRemoving Folder ... ";
`rm -rf $folder`;
print "Done\n\n";


sub getFileContents {
	my $file = shift;
	open (FILE,$file) || die("Can't open '$file': $!");
	my @lines=<FILE>;
	close(FILE);
	
	return @lines;
}

sub removeComments {
	my @lines = @_;

	@cleaned = grep(!/^\s*#/, @lines); #Remove Comments
	@cleaned = grep(!/^\s*$/, @cleaned); #Remove Empty lines

	return @cleaned;
}

If you need, you can set this script as a cron job – this will make sure that you don’t have to worry about the backup.

17 comments

  1. @suexID
    You must have perl installed for this to work. If you have perl, run the command ‘which perl’. This should give you the location of your perl executable. Replace the /usr/bin/perl in the first line of the script with this location.

    Hopefully, it will fix the issue.

  2. BinnyVA: Thank you so much for your quick reply! Perl is installed on the server, and the command you wrote in your reply gives me the usr/bin/perl location too. This is why I’m so confused… 🙁

  3. @suexID
    Im not sure either. It could be an error that occurred when copy pasting the code. I’ll send an email with the file to you – try using that script.

  4. The “^M” means that you’ve got carriage return characters in the file, specifically on the first line which has the name of the bash executable.

    tr -d ‘\r’ new

  5. That post took away the redirection characters and messed up the command.

    Alternately use dos2unix command.

  6. hi all,

    I need to create 5/6 folders on C: or D: with specific name.

    and i also want to Create ‘n’ number of Sub folders under a Folder.

    I dont know anything abt PERL kindly do me a favor and write it for me Please.

Leave a Reply

Your email address will not be published. Required fields are marked *