Automating Your mySQL Database Backup On Media Temple’s Grid-Server

In part one of this tutorial, Automating Your Server Backups On Media Temple’s Grid-Server, I explained how to automate backups of your files on your grid-server. Unfortunately, not all your data was covered in those backups because it did not include your database. Now I will describe how you can utilize cron and mysqldump to automate database backups on the grid-server side.

The Process

Like the last tutorial, we will go about this in a similar fashion by creating a script which we will utilize as a cron job.

1. Open up your favorite text-editor and copy and paste the following snippet:
   #!/bin/bash
   mysqldump –add-drop-table -h internal-db.s99999.gridserver.com -udb99999 -pPASSWORD database_name > database_name.sql

   We will need to replace a few things here though:

   • Replace all the instances of ‘99999′ with your gridserver account number.
   • Replace ‘PASSWORD’ with your SSH password
   • Replace ‘database_name’ with the name of the database you’d like to backup

   If you’ve ever setup Wordpress or some other CMS which needs a database, you’ve likely come across this info before.

   You could test if your command works by first SSHing in and then entering in the command. If an sql file is created, it worked.

   If you have more than one database you want to backup, just create another mysqldump command.

2. Now save this file as something like ‘db-backup.sh’
3. Upload ‘db-backup.sh’ to your /data folder
   I chose to put the script in this folder because when it runs, the .sql files are also dumped into this folder. This folder is nice because it is only accessible by you and you wouldn’t want the public to accidentally stumble upon an entire dump of your database.
4. Now log into your Media Temple Account Center
5. Go to the admin section of your primary domain and click the Cron Jobs link
   
6. Click on the ‘Add a new cron job’ button
   
7. Feel free to fill in your email address in the output email field if you’d like. This will send you emails whenever the cron job runs and will let you know if it was successful or not. However, the important field we need to fill out is Command to Run. In this field, enter:
   /home/99999/data/db-backup.sh

   Again, replace the ‘99999′ with your grid-server account number (it’s actually listed below ‘Command to Run’)

8. In the bottom half of the page, you can specify how often you’d like your script to run. This entire section is up to you. I’ve set mine to run daily at 2AM when traffic on my site is slower
9. Click Save and you’re all done!

Now what about saving these mysql dumps to my local disk?

If you’ve went through part one of this tutorial and successfully set up rsync, these dumps are now part of that synchronization.

Again, if something didn’t make sense or you know of some way to improve these backup processes, please feel free to leave a comment!