As I've started to use MySQL databases more and more for varying priority stuff I decided that I should probably be backing up my database everyday. Around the same time I also noticed that I'm still only using 3% of my Gmail account, and what better way to fill it up than with backups?

To do what I wanted, I had to use a mixture of articles I've written in the past. I knew I was going to need to be emailing myself attachments, which required mutt. And a way to backup my databases which was where mysqldump came in. Also, a bit of a crontab job too. Basically what I want is to make a file with my mysqldump, and then have it mailed to me, then delete the file from my server.

I couldn't just put the command into the cron file like we would usually, since I have a few commands I'll need to be running, so it'll be cleaner to put it in a bash script. Bash is just a simple language to proform basic tasks.

Lets get started then...

#!/bin/sh

This line goes at the start of every bash script, to tell it where it should be looking to be compiled and ran. It's pretty standard but it may be in a different place in really strange situations (I'll get to that later).

DATE=`date +%Y%m%d`
FILENAME="$DATE.mysql"

Here I'm setting up my filename. DATE and FILENAME are both variables. It's just convention to put variables in upper case. I'm putting the date (using the date function) into the DATE variable, and then adding that to the .mysql suffix (so it acts as a filename).

mysqldump --all-databases --user=root --password="yourpassword" > /home/youruser/$FILENAME

Then we just do the backup, like we did in the earlier article on mysqldump. Just switch out yourpassword and youruser to the right details. This'll just put the backup in your home directory (though, it won't be there for long).

echo | mutt -a /home/youruser/$FILENAME -s "Scheduled MySQL backup" you@gmail.com

You should recognise sending an attachment from the article before. Usually, you'd be promted to enter the body of the email once you've entered that, but we won't have any interaction with this script so the echo | just puts a nothing into the body. You can change that if you like.

rm /home/youruser/$FILENAME

Then delete the file!

All you have to do is save that to a file somewhere, and then set up your cronjob. Open it by running crontab -e and putting in the data. I want to back mine up at 5am, every morning so my cron line looks like this:

0 5  *   *   0,2,4,6  /home/shamess/backupMySQLtoEmail

That file name is where I've saved my script to. Now I get emailed my database backup every night. I just have to remember to log into gmail and delete a few old ones every now and then.