Continuing with the backup theme of my last post, I've needed to back up our local LAMP server because its starting to hold some important data. Maybe I'm getting paranoid because of my first hard drive failure in 10 years (the last one was a 2GB HDD that got progressively larger areas of dead sectors, so it was a nice gradual death). Maybe I've just been taught a lesson and am starting to do what I always should have done anyway. In any case, here is the little script I wrote:
# System + MySQL backup script using rsync
# Copyright (c) 2008 Al Twohill
# This script is licensed under GNU GPL version 2.0 or above
###Adjust these variables as you need ###
#The directories to back up
DIRS="/home /etc /var/www"
#The MySQL login details
#Temp location to store MySQL Dumps
#Location to backup to
### The script (you shouldn't need to edit this) ###
### Start MySQL Backup ###
# Get all databases name
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
### Dump backup using rsync ###
rsync -aHk $DIRS $MYTEMP $BACKTO
### All done ###
t's nice and simple and should be fairly straightforward. However, this is not a one-size-fits-all solution, so I'll step through what I did so you can adjust for your needs.
The first bit is really straightforward, we just create some variables and assign them values.
"DIRS" is a space-separated list of the directories we want to back up. On a typical LAMP server this is probably all you need, but its easy enough to amend.
Next up we have the details we need to login to the database. Obviously because we store our password in plaintext we must make sure that our backp script is readable only by root.
"MYTEMP" is where we temporarily store the dumps of our databases. Again, as the database dumps are (gzipped) plaintext, we should make sure this folder is only accessible by root.
Finally in our variable list is where to backup to ("BACKTO"). You can use any value in here that fits in with an rsync destination. For more information on this, see http://samba.anu.edu.au/rsync/, and also this post. I'm using /mnt/backup because I have a remote filesystem mounted there, but you could just as easily rsync directly to a remote host (if the host supports it).
The Nitty Gritty
Now we come to how the script really works. We now get the necessary commands to perform our operations, using a nifty bit of code I found here (and some other code also came from here - just rewritten for my needs).
MySQL is a little tricky to back up, as if you just take the db files you'll need to start and stop the entire database service to do backups/restores, which isn't ideal - especially if you have several databases on the server. To get around this we use the tool mysqldump, which does what it says - it dumps its databases into a text file. We could make this bit of the script a little less complicated and dump the entire set of databases into one file, but I like this method better as it makes it really easy to restore individual databases. So, we execute 'show databases' to get a list of all the dbs we need to back up, then we loop through that list dumping each one into it's own gzipped file. Neat!
Finally, we actually rsync everything away, the directories we wanted at the start and the mysqldump temp directory of to our destination. One crontab entry later and we're happy! Rsync takes care of seeing what has changed where and makes this whole process pretty efficient.
Happy backupping everyone!