Aditya Kothadiya's Blog

Let me admit this – I did not backup the Tweeght’s database until today. Somehow I kept procrastinating it for no reason. Since Tweeght is growing consistently, it was crucial for me to backup its database on daily basis and prepare myself from loosing all the crucial data in some sort of catastrophic event.

I use Media Temple’s Grid-Server for Tweeght. MT has good knowledge base article on – How can I Backup and Restore a MySQL database?.

But this article just mentions about how to backup your database for once. I wanted to automate this backup process on daily basis. So I Googled a little-bit, and came across this wonderful script. So I took that script, updated it for MT’s Grid-Server infrastructure, and now I’m all set within 20 minutes to take daily backups of my crucial databases.

Here are the steps I followed:

1. Go to /data directory in your account. I preferred this over /domains directory because this is accessible only by you and not by public. You don’t want the public to accidentally access dump of your database.

2. Create /db_backup directory. Go to /db_backup directory and create three new directories – /scripts , /daily and /recent.

3. Go to /scripts directory and open up your favorite editor. Copy and paste following code -

#!/bin/bash
# Set a value that we can use for a datestamp
DATE=`date +%Y-%m-%d`
# Our Base backup directory
BASEBACKUP="/home/XXXXX/data/db_backup/daily"

for DATABASE in `cat /home/XXXXX/data/db_backup/scripts/db_list.txt`
do
# This is where we throw our backups.
FILEDIR="$BASEBACKUP/$DATABASE"

# Test to see if our backup directory exists.
# If not, create it.
if [ ! -d $FILEDIR ]
then
mkdir -p $FILEDIR
fi

echo -n "Exporting database:  $DATABASE"
mysqldump --add-drop-table -h internal-db.sXXXXX.gridserver.com -udbXXXXX -pPASSWORD $DATABASE | gzip -c -9 > $FILEDIR/$DATABASE-$DATE.sql.gz
echo "      ......[ Done Exporting to local backup, now exporting for remote backup] "
cp $FILEDIR/$DATABASE-$DATE.sql.gz /home/XXXXX/data/db_backup/recent/$DATABASE.sql.gz
echo "      .......[Done]"
done

# AutoPrune our backups.  This will find all files
# that are "MaxFileAge" days old and delete them.
MaxFileAge=4
find $BASEBACKUP -name '*.gz' -type f -mtime +$MaxFileAge -exec rm -f {} \;

4. Change few things in above code -

• Replace all the instances of ‘XXXXX’ with your gridserver account number.
• Replace ‘PASSWORD’ with your SSH password

5. Now save this file as something like ‘db_backup.sh’

6. Open up your editor one more time, and write the names of databases you want to back up on each single line -

dbXXXXX_proj1
dbXXXXX_proj2

7. Now save this file as something like ‘db_list.txt’

8. Basically, the script pulls a list of databases to be backed up from a file called db_list.txt, this file takes 1 database name per line. It then exports the database and compresses, then saves it with a date-stamped filename to a directory called ../daily/<db_name>/.

Then, the script copies a non-date-stamped filename to a directory called ../recent/, thus overwriting the previous file stored there.

Finally it goes through the daily directory structure and deletes any files older than 4 days.

9. Now you can test your backup script by running following command -

./db_backup.sh

10. The script will echo out some progress messages. If no error message, then hopefully your databases should be backed up.

To see if it was successful execution, go to ../daily directory. You will see directories created with <db_name> names. Under these directories, you’ll see compressed version of sql dumps. Similarly, go to ../recent directory, and you’ll see the similar compressed version of sql dump.
To automate this backup process, go to your MediaTemple’s admin section of your primary domain and click the “Cron Jobs” link.

11. Click on the ‘Add a new cron job’ button.

12. Add your email address in the “Notification Email” field. This will send you emails whenever the cron job runs and will let you know if it was successful or not.

13. Also add path of your script in the “Command or script to execute” field. Enter: /home/XXXXX/data/db_backup/scripts/db_backup.sh. Again, replace ‘XXXXX’ with your gridserver account number.

14. On the same page, you can specify how often you’d like your script to run. This entire section is up to you requirements. I’ve set mine to run daily at 3:00 AM when traffic on my site is slower.

15. Click Save and you’re all set to backup your databases on daily basis!

I hope this will be useful to you. If you see any issues, please let me know in the comment section.