Full Text

February 20th, 2008

Simple MySQL Backup Using Perl

I present to you a short script to automate backup and compression of a MySQL database using perl on a Linux system. I had to solve this problem today and figured there might be others out there in need of the same solution. Store the script in a safe location, hidden from the Internets, and chmod it to 700 to prevent other users from viewing the source (because it contains db login information). Customize the variables at the top of the script and test it out (perl sqlBackup.pl). If it runs without outputting any errors, add a line to your crontab file to run the backup at the desired interval.

There are of course better ways of doing this, namely this one. I am doing it my way because I’m more comfortable with perl and this script gives me more flexibility and a bit of debugging in case something goes wrong. This script assumes you’ve got cron setup to email you any output, so you should receive notification if something goes wrong.

Here is the perl code.

#!/usr/bin/perl
# MySQL Database Backup
# This script should be called by cron
# Jeremy Modjeska (http://daqron.com)
#

use strict;

#
## Make sure this file is chmod 700 because
## it contains the mysql username/password. 
#

########################################################################
# Config Required
########################################################################

#
## Set the location where backups should be stored. 
## Make sure it's outside of the web dir and has an ending '/'
## (i.e., /home/user/backup/db/)
#

my $loc = '/home/user/backup/db/';

#
## Set the filename (date will be automatically appended to the end)
#

my $fn = "database_name--";

#
## Set the username, password, and name of the database
#

my $dbuser = "dbusername";
my $dbpass = "dbpassword";
my $dbname = "dbname";

########################################################################

#
## Format date for filename; other global stuff
#

my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =
   localtime(time);
my @months =
   qw( jan feb mar apr may jun jul aug sep oct nov dec );

my @errors;
my $errcount = 0;

#
## Append date to filename (dd-mon-yyyy)
#

# Create the full filename (filename + date)
  my $ffn = $fn . $mday . "-" . $months[$mon] . "-" . 
            ($year+1900);

# Create the dump file name (filename + .dump)
  my $dfn = $ffn . ".dump";

# Create the dump file location (abs path + filename + .dump)
  my $dfl = $loc . $dfn;

# Create the zip file name (filename + .tgz)
  my $zfn = $ffn . ".tgz";

# Create the zip file location (abs path + zip file name)
  my $zfl = $loc . $zfn;

#
## Error Generator
#

sub error {

  my $error = shift;

  $error = " -=>   Error with db backup: \n" .
           " -=>   $error \n";

  push (@errors, $error);
  $errcount++;

}

#
## Perform the backup to a dump file
#

`mysqldump --opt --user=$dbuser --password=$dbpass $dbname > $dfl`;

#
## Check for the dump file. If it's there, zip it up.
#

if ( -e $dfl ) {

  my $ziperr = `cd $loc ; tar -czvf $zfn $dfn 2>&1 1>/dev/null`;

  if ( $ziperr && ( ! $ziperr =~ m/Removing leading/ ) ) {
    error("Error creating zip file - $ziperr");
  }

  # Check for the final zipped file. If it's there, remove the dump file

  if ( -e $zfl ) {
    unlink($dfl) || error("Couldn't cleanup dump file - $!");
  }

  else { error("Failed to create zip file.") }

}

else { error("No SQL dump file found.") }

#
##
## If any errors, print output so cron will email us
##
#

if ( $errcount > 0 ) {
  print @errors;
}
  • Digg
  • del.icio.us
  • Google Bookmarks
  • Reddit
  • StumbleUpon
  • Technorati
  • Facebook
  • LinkedIn
  • Twitter
Posted in Code  |  No Comments

Leave a Reply