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;
}

—————————————–