Scripting Backups of MySQL with Perl via mysqldump

MySQL provides you with a nice utility for creating a backup of your databases. From the mysqldump documentation page: “The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.”

The mysqldump utility doesn’t provide you with a “hot” or live backup like MySQL Enterprise Backup does, and there may be issues with mysqldump locking tables during the backup process. So for critical data, you should consider MySQL Enterprise Backup.

There are too many options when using mysqldump to backup your data for me to discuss here, so this post will only provide you with a script to perform a basic mysqldump of your databases. For more information on mysqldump, see the mysqldump documentation page.

You run mysqldump from the command line. And while running a backup from the command line is easy, it is easier to have a script that will do this process for you at a regular interval. This script reads a list of the tables that you want to backup from a file, and then backs up each table separately into a named directory. I have been using this script on my home MySQL server for a couple of years now. Since my data isn’t critical, I have a cron job that runs once a day to backup my data.

You will still have to remember to clean out the directory where the backups are stored. For me, I use a simple Unix command to find any backup directories over seven days old (via the -ctime +7 option), and delete them. I run this command as a cron job and it runs once a week. Here is the command:

find /usr/local/backups/mysqlbak -depth 1 -ctime +7 -print -exec rm -rf {} \; 2>/dev/null >/usr/local/backups/mysqlbak/rm.txt

I use a text file named mysql_bak.config that contains a list of the database tables that I want to backup. I place this file in the same directory as the Perl script, but you may modify the Perl script to place this file anywhere on your system (as long as your user has permissions to access this file). Instead of using a list of databases from a file, you could use a mysql command “show databases” to find a list of your databases and incorporate that command into the Perl script. However, since my list of databases do not change often, I can use a static list.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| address            |
| cbgc               |
| twtr               |
+--------------------+

You may also tell the Perl script to ignore any database with a comment (#) before the database name. Using the list above, my backup list file looks like this:

information_schema
# address
cbgc
twtr

For this example, I don’t want to backup the address database. The rest of the databases will be backed up individually, and placed in a directory. The backup directory will be created by the Perl script with this naming convention: year-month-day-time, like this:

# pwd
/usr/local/backups/mysqlbak
# ls -lt
drwxr-xr-x 10 root staff 340 Aug 19 00:03 2011-08-19-0003
drwxr-xr-x 10 root staff 340 Aug 18 00:03 2011-08-18-0003
drwxr-xr-x 10 root staff 340 Aug 17 00:03 2011-08-17-0003
drwxr-xr-x 10 root staff 340 Aug 16 00:03 2011-08-16-0003

Inside each directory is a list of the databases that were backed up:

# pwd
/usr/local/backups/mysqlbak
# cd 2011-08-19-0003
# ls -l
total 318136
-rw-r--r-- 1 root staff 104043 Aug 19 00:03 cbgc.sql.Z
-rw-r--r-- 1 root staff 539 Aug 19 00:03 information_schema.sql.Z
-rw-r--r-- 1 root staff 159574979 Aug 19 00:04 twtr.sql.Z

And here is the Perl script. You will have to change parts of the script to match your system configuration.

#!/usr/bin/perl
# Perform a mysqldump on all the databases specified in the dbbackup.config file

use warnings;
use File::Basename;

# example config file
# You may use a comment to bypass any database that you don't want to backup
# # Unwanted_DB (commented - will not be backed up)
# twtr
# cbgc

# set the directory where you will keep the backup files
# make sure that the directory exists
$backup_folder = '/usr/local/backups/mysqlbak';

# the config file is a text file with a list of the databases to backup
# this should be in the same location as this script, but you can modify this
# if you want to put the file somewhere else
my $config_file = dirname($0) . "/mysql_bak.config";

# retrieve a list of the databases from the config file
my @databases = removeComments(getFileContents($config_file));

# change to the directory of the backup files.
chdir($backup_folder) or die("Cannot go to folder '$backup_folder'");

# grab the local time variables
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);

$hour = "0$hour" if $hour < 10;
$min = "0$min" if $min < 10;

# create the name of the backup folder that will contain all of the backup files
my $folder = "$year-$mon-$mday-$hour$min";
mkdir($folder) or die("Cannot create a folder called '$folder'");

# backup each database contained in the @databases array
foreach my $database (@databases) {
next if ($database eq '');
chomp($database);

my $table = '';
# Get just 1 table in the database - if there is a ' '(space) in the db name
if(index($database,' ')+1) {
my @parts = split(' ',$database);
$database = $parts[0];
$table = $parts[1];
}

# you may comment out this print statement if you don't want to see this information
print "Backing up $database ... ";

my $file = $database;
$file .= '_' . $table if($table ne '');
$file .= ".sql";

# perform a mysqldump on each database
# change the path of mysqldump to match your system's location
# make sure that you change the root password to match the correct password
`/usr/local/mysql/bin/mysqldump -R --triggers -u root --password=password $database $table | compress > $folder/$file.Z`;

# you may comment out this print statement if you don't want to see this information
print "Done\n";
}
# you may comment out this print statement if you don't want to see this information
print "Done\n\n";

exit;

# this subroutine simply creates an array of the list of the databases

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

# remove any commented tables from the @lines array

sub removeComments {
my @lines = @_;
@cleaned = grep(!/^\s*#/, @lines); #Remove Comments
@cleaned = grep(!/^\s*$/, @cleaned); #Remove Empty lines
return @cleaned;
}

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
About these ads

3 Responses to Scripting Backups of MySQL with Perl via mysqldump

  1. Justin says:

    If i’m reading this script correctly (and I might not be) won’t this create an inconsistent backup? None of the tables will be from a single Point-in-Time. Granted that wouldn’t be the case for any MyISAM table no matter what, but if those tables are InnoDB it won’t be consistent.

    You’re better off doing something like:

    mysqldump –opt –single-transaction –master-data=2 -h [host] -p [database name] > /backup.sql

    that will at least backup all tables in a single DB schema consistently from a single point-in-time.

    • shannon says:

      Hi Justin,

      Just a quick comment,

      –master-data turns off –lock-tables and on –lock-all-tables unless –single-transaction is used. But that won’t be a point in time if MyISAM is getting updated as myisam isn’t transactional. (also no need to specify –opt it’s on by default).

      See these bugs for more information on that behavior.

      http://bugs.mysql.com/bug.php?id=12999

      http://bugs.mysql.com/bug.php?id=23344

      won’t matter really if myisam is static during the dump though.

      One can start 2 simultaneous dumps (1 for innodb tables with single-transaction, 1 for myisam with –lock-all-tables) or use just use –master-data (which will turn on lock-all-tables and will block writes for dump duration). If the myisam tables aren’t static or the dump and resulting restore prohibitive due to size, various binary options work best (fs snapshots, binary backup, MySQL Enterprise Backup), each has + and -.

  2. Dave Cross says:

    For getting a formatted datetime value, you’re better off using the strftime function from POSIX.pm (which is part of the standard Perl distribution).

    use POSIX ‘strftime’; # Need to explicitly load the functions in POSIX

    # Note that if you pass no argument to localtime, it assumes the current time
    my $folder = strftime ‘%Y-%m-%d-%H%M’, localtime;

    You use backticks to execute external commands. Backticks execute external commands and return any output from those commands. As you’re not doing anything with that output, this is wasteful. Far better to use the ‘system’ function to run your external commands.

    You should get into the habit of using lexical filehandles. They have the advantage of automatically closing once the variable goes out of scope. For example, your getFileContents function becomes:

    sub getFileContents {
    my $file = shift;
    open my $fh, ‘<', $file or die "Can't open '$file': $!";
    return ;
    }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 39 other followers

%d bloggers like this: