A mysqldump script

This one was customized for backing up the data for a WordPress instance, but you’ll get the idea.

Keep in mind that the actual MySQL schema, and database-wide user accounts with their permissions, are kept in their own databases (information_schema, mysql). If you’re running multiple MySQL databases with a long list of users that connect to them you may want to include those in the list you back up.

If you want to backup all databases in one big file (not recommended), you would use the “- -all-databases” option for mysqldump rather than “- -database $dbName” below.

#!/usr/bin/perl
use strict;
my $HOME = $ENV{'HOME'};
our($myAdmin,$myPass);
require "/usr/local/etc/admin.conf";
my ($minute,$hour,$day,$month,$year) = (localtime)[1,2,3,4,5];
my $timestamp = sprintf("%04d%02d%02d%02d%02d", $year + 1900, $month + 1, $day, $hour, $minute);
my $DBHOME = "/data/app/mysql/var";
my $dbName = "wordpress1";
my $dumpName = "$dbName.$timestamp.sql";
my $dumpPath = "/data/backup/mysql";

print "Backing up MySQL database\n";

system("/usr/bin/mysqldump --user=$myAdmin --password=\"$myPass\" --database $dbName >$dumpPath/$dumpName");

system("chown devusr:devgroup $dumpPath/$dumpName");
system("chmod g+w $dumpPath/$dumpName");
system("gzip $dumpPath/$dumpName");

Briefly, when run as root this script will save a MySQL text dump of the named database (in the example, “wordpress1”) in the designated directory, attach a timestamp, re-permission so they can be accessed by a non-root user (here “devusr” and any member of “devgroup”), and gzip it to save space. Note that the script uses a config file located at /usr/local/bin/admin.conf. This file would look something like:

# Config file
$myAdmin = "root";
$myPass = "rootpassword";

1;

The “1;” is required to let the script know it has reached the end of the config file.

This entry was posted in System Administration on by .

About phil

My name is Phil Lembo. In my day job I’m an enterprise IT architect for a leading distribution and services company. The rest of my time I try to maintain a semi-normal family life in the suburbs of Raleigh, NC. E-mail me at philipATlembobrothersDOTcom. The opinions expressed here are entirely my own and not those of my employers, past, present or future (except where I quote others, who will need to accept responsibility for their own rants).