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.

I love my soya burgers, but I don't like them burnt. More than once I've put them on the grill, came to check what's happening on MSN, and totally forgotten about them. Sensible people have egg timers for this type of thing, but we have Debian servers and should frown upon such out dated methods.

Instead, we can send an email to ourselves when we need to turn our burgers over.

We can do that using the at and mail commands. at lets us tell Debian to do something at a particular period of time. We want to have to turn our burgers over in eight minutes, so tell it that.

at now +8 minutes

For some reason, my server lags for a second, but then you go into the at program interface¹. It's here we say what we want to do.

echo "Time to turn your burgers over!" | mail -s "Just a reminder!" shamess@gmail.com

This runs mail, where -s is the subject, and the to address goes at the end. Then, mail expects you to type the body of your message, so we just echo it here (more on pipes later). This sends me an email with "Just a reminder!" as the subject, and "Time to turn..." as the body.

There you go! No more burnt food! Be inventive with other reminders you could give for yourself.

¹When you move away from the standard interface where your CLI all starts with your user@host:/etc$ into the interface where you have mysql: or at:, does that have a name?

After we talked about backing up your MySQL databases last time, I realised it would be much easier if I could just make my server back it up its self, every day. That way I don't have to think about it, and if I lose anything I can just revert to the last day's archive!

To do that, we use a thing call cron. This might have been called "cronjob" or something similar on your shared host, if you ever used it. If you did, then most of this should be obvious for you, since it's exactly the same (at least when I was using Plesk), just in a different way.

If you've ever looked around your /etc/ directory, you'd have noticed the /etc/cron.whatever directories. The files in there are run according to their directory, so the scripts in cron.daily are run once a day.

Each user has their own crontab file, which is where you put all the stuff you want to run. You can see your file with the command crontab -l (that's a lower case L). Chances are you won't have one just yet.

When you do though, it'll be laid out in six, space seperated fields, like this:

*     *     *     *     *  Command to be executed
-     -     -     -     -
|     |     |     |     |
|     |     |     |     +----- Day of week (0-7)
|     |     |     +------- Month (1 - 12)
|     |     +--------- Day of month (1 - 31)
|     +----------- Hour (0 - 23)
+------------- Min (0 - 59)

(Stolen from Debian Admin site, you should check out that article too.)

For day of the week, both 0 and 7 are Sunday, however some versions of Unix don't support 7.

To edit your crontab file, use crontab -e, which will open your crontab file in nano.

The "command to be executed" is the path to the command's binary (sorry if that makes little sense...), which you can find out by using the which command. For instance, if you want to run ls, you'd have to do which ls to find where it's kept. If you wanted to run ls, for some strang reason, you could do:

0   *   *   *   *  /bin/ls

Then, you'd get the output emailed to you, at your local user.

But, that's not what we want to do. We want to do a backup, each night. So, we'd add this:

10   23   *   *   *  "mysqldump -A -u root -p password > /home/shamess/backedup-files/$(date +%s).mysql"

That'll back all the databases up at 10 past 11 each night. The $(date +%s) part will output a Unix timestamp.

You'll probably want to delete really old back ups, and archive the new ones. You can do that by running a bash script, instead of a command, but we'll get to that later.