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;