Perl Script to Backup MySQL Databases

Written by BinnyVA on June 19, 2008 – 11:13 pm -

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.


del.icio.us | Digg it | reddit | StumbleUpon

Tags: , , , ,
Posted in Programming, Scripting, Shell Scripts | 17 Comments »

17 Comments to “Perl Script to Backup MySQL Databases”

  1. I can’t get the mysqldump command to execute from the script. I can get it to execute from the console. Any thoughts?

  2. suexID says:

    Giving me the following error:

    /usr/bin/perl^M: bad interpreter: No such file or directory

  3. BinnyVA says:

    @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.

  4. suexID says:

    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… :(

  5. suexID says:

    I don’t really know what that “^M” symbol means in the error message…

  6. BinnyVA says:

    @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.

  7. […] have several backup systems in place – but you know the Murphy’s law when in comes to backups… You […]

  8. Amit Agarwal says:

    The ^M comes if you modified the file in windows system :)
    You can remove that character and that should work fine.

  9. John says:

    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

  10. John says:

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

    Alternately use dos2unix command.

  11. […] Copias de seguridad MySQL con Perl. (más información) […]

  12. shailesh says:

    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.

  13. You will find here:
    http://www.piprime.fr/1308/duplication-de-bases-de-donnees-mysql-par-connexions-securisees-ssh/
    an interactive shell script (only for unix-like OS) that duplicates locally databases via ssh connexion.

  14. Stanislav says:

    You have forgot to include print “Content-type:text/html;\n\n”;

  15. BinnyVA says:

    Nah – this script is meant to be executed from the command line – so that is not necessary.

  16. sinx says:

    Hi! :)

    I’ve created something simillar for making all MySQL databases backup but in Bash you may want to check it out. My solution makes one file for each database.

  17. Hi! First of all – thanks!

    It would be nice to see the same script on others languages.

    And actually why not to use MySQL Backup Tools such as dbForge Studio:

    http://www.devart.com/dbforge/mysql/studio/database-administration.html

Leave a Comment