Originally published on April 29, 2003, I’ve made some formatting corrections & bumped this up a bit.

Yesterday, Mark Pilgrim’s message of the day was
You know what’s cool? Backups.

Well who am I to argue with such coolness? So in the spirit of “what is Hip“, and myself being in a situation where my host is also changing data centers, I would like to share with you a little utility script I run on my system every night ubiquitously entitled “backupMySQL.pl.”

Basically this little Perl takes advantage of naming conventions used by the standard-fare Apache configuration many of us enjoy. That is, our accounts are usually stored in directories such as “/home/USERNAME” and our database are prefixed with our USERNAME, such as USERNAME_mt. Moreover, a properly configured system will allow you to securely house and run such scripts BELOW the public /public_html &/or /www directory where all your public stuff is published.

With this configuration in mind, I FTP this script in ASCII mode to my root directory (below HTTP access), a chmod -755 so it would execute. I then created a subdictory entitled /dbs and chmod -755 /dbs so my script can access it. I then went to my control panel and cron’d the job to run every night. Okay, so I lied, I did this all from the command line, but as you can see, you can implement this script without having to bash yourself silly.

One other optional feature I have in this script is the ability to FTP my backup to a friend who hosts a website on an entirely different server and service. I reciprocate in kind for him. What this does is insures that we have an “off site” backup — based on the principle that if both our servers go down, then we’ve got a much larger issue at hand (how about global thermonuclear war?). So here it is. Use it, tweak it, let me know ho you like it — just make sure to check the files from time to time to make sure your backups can be restored.

#!/usr/bin/perl -w
# ———————————————————————–
# copyright Dean Peters © 2003 – all rights reserved
# http://www.HealYourChurchWebSite.org
# ———————————————————————–
#
# * Obligatory Legal Stuff *
#
# backupmysql.pl is free software. You can redistribute and modify it
# freely without any consent of the developer, Dean Peters, if and
# only if the following conditions are met:
#
# (a) The copyright info and links in the headers remains intact.
# (b) The purpose of distribution or modification is non-commercial.
#
# Commercial distribution of this product without a written
# permission from Dean Peters is strictly prohibited.
# This script is provided on an as-is basis, without any warranty.
# The author does not take any responsibility for any damage or
# loss of data that may occur from use of this script.
#
# You may refer to our general terms & conditions for clarification:
# http://www.healyourchurchwebsite.com/archives/000002.shtml
# For more info. about this code, please refer to the following article:
# http://www.healyourchurchwebsite.com/archives/000802.shtml
#
# * Technical Notes and ASSUMPTIONS (PLEASE READ) *
#
# this code assumes a standard Apache configuration where
# the $HOME directory is a path beneath public_html &/or www
# and employs a naming scheme such as /home/YOURACCOUNTNAME/…
#
# it also assumes that your databases are prefixed with your
# account name, such as YOURACCOUNTNAME_mt
#
# do NOT under any circumstances run this from a directory accessible
# via HTTP (e.g. public_html/… or www/…)
# it makes system calls, and although it takes no input, just don’t!
#
# this program works best with CRON, e.g.
# 0 0 * * * /home/YOURACCOUNTNAME/backupmysql.pl
#
use DBI;
use Net::FTP;

# this assumes you have previous created a subdirectory named /dbs
# and have chmod 777 /dbs
$path = “/home/$username/dbs/”;
$file = “/home/$username/dbs.tar.gz”;

# databse connection info …
$host = “localhost”;
$username = “YOURACCOUNTNAME”;
$password = “YOURPASSWORD”;

# connect to the database and retrieve a tuple of your databases
$dbh = DBI->connect(“DBI:mysql:host=$host”,$username,$password) or die “Bad login info”;
$sth = $dbh->prepare(“show databases like \’$username\_%\'”);
$sth->execute();

# for each database … back it up!
while(@row = $sth->fetchrow_array()) {
	if(!$row[0]) { die “No dbs to backup!”; }
	foreach $db (@row) {
		system(“mysqldump –opt –user=$username –password=$password $db > /home/$username/dbs/$db\.sql”);
	}
}

# you’re done with the database
$sth->finish();
$dbh->disconnect();

# delete the old version — probably should “grandfather it”
if(-e $file) { unlink $file; }

# create a single, easy to use and transport file
system(“tar -cf dbs.tar dbs”);
system(“gzip dbs.tar”);
system(“rm $path\$username_*”)	if $path =~ m/home\/$username/;

#
# OPTIONAL – you can comment this out, or not
# this assumes you have a friend on a different server
# with whom you’ve made arrangements to hold backups of each
# other’s data. This way, if a server fails, you can get it
# from your friend’s site
#
# it also assumes your friend has created a directory for
# you entitled “/backup” — this of course can be changed
# if your friend sets up an individual FTP account to a directory
# … which is what I actually do … I love my friends!
#
$ftp = Net::FTP->new(“MYFRIENDSDOMAIN.COM”, Debug => 0);
if($ftp->login(“FTPUSERNAME”,’FTPPASSWORD’)) {
  $ftp->binary();
  $ftp->cwd(“/backup”);
  $ftp->put(“dbs.tar.gz”, $username.”_dbs.tar.gz”);
  $ftp->quit;
}

# bye bye
exit;