Simple Database Backup Script for Mediatemple (gs) Servers

Published 17th November 2009

When you run websites one thing you always need to keep in mind is not just backing up your files but also backing up your database. Recently I went through this process with my website host MediaTemple.net who are a great hosting provider. I use their (gs) shared hosting and I came across this great script for backing up your database.

The Script

#!/bin/sh ############################# SQLHOST="internal-db.s#####.gridserver.com" SQLDB="db#####_NAME" SQLUSER="db#####" SQLPASS="PASSWORD" SQLFILE="db#####_NAME_$(date '+%d_%m_%y').sql" LOCALBACKUPDIR="/home/#####/data/backup" ############################# echo "* Performing SQL dump..." cd $LOCALBACKUPDIR mysqldump -h $SQLHOST --add-drop-table --user="$SQLUSER" --password="$SQLPASS" $SQLDB > $SQLFILE # Remove backups older than 7 days: MaxFileAge=7 find $LOCALBACKUPDIR -name '*.sql' -type f -mtime +$MaxFileAge -exec rm -f {} ; echo "* Backed up..." exit

This is a simple shell script that does two things. It does a MySQL dump of a database to a SQL file and it deletes any backups in that folder older than 7 days. Pretty useful I thought. So how do I get it to work? Well you need to do the following:

  • First save this script as db_backup.sh or some other filename. Make sure to fill in the correct details replacing ##### with your (gs) id.
  • Open up your FTP program and navigate to your data directory and upload the above script.
  • In the data directory create a backup directory and make sure it is writeable.
  • You can now run your script by logging into your server via SSH and running the following command: /home/#####/data/db_backup.sh
  • (remember to replace ##### with your (gs) id.

You should see something similar to the following output:

[email protected]:~$ /home/#####/data/db_backup.sh * Performing SQL dump... * Backed up...

Hopefully now if you use your FTP program and navigate to your backup folder there should be an SQL file there.

How to Automate the Backup

Now it goes without saying that we want this script to run on a regular basis without us having to worry about it. Well thankfully MediaTemple provide an easy way for us to set up a cron job to run our script on a schedule. If you login to the MediaTemple account center and click on your primary domain then click on “Cron Jobs” you will be presented with the option to add anew cron job.

Here you simply put the command we used earlier into the “Command or script to execute” box (/home/#####/data/db_backup.sh) and enter an email address if you wish to receive notifications about the cron job. Then you can also set the schedule for the Cron Job but I’ll leave that for you to play with.

Conclusion

So there you have it. A simple script set up to run on a schedule to backup any database you have. Obviously to restore the database you just need to download the SQL file via FTP and run it on your MySQL database to restore your information. If you use this script or have any ideas on how to make it better then please let me know.

I should also point out that this is not my script and all credit goes to the author of this script (whoever it is) for helping us mere mortals out.