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.

The default mail app that ships with Debian (... called 'mail') can't handle MIME standards, which makes sending and receiving attachments pretty hard. So, I asked around and found mutt (sudo apt-get install mutt).

It's actually a lot prettier than mail, as pretty as command line can get anyway. Other than the way it looks, it works pretty much the same, and the on screen help is good too. So, go and switch to mutt.

What other mail applications are there?

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?

If you're using mail functions on PHP after just installing it, you won't get any error message, even though the mail won't get sent yet. Don't worry, we'll fix that now.

Debian comes with Exim4 which is a mail server with a bunch of features. By default it's set to only send emails locally (to other users on your system). If you tried to send any emails to external users at the moment, you'll see something like "** www-data@debian R=nonlocal: Mailing to remote domains not supported" in your error logs.

We just need to reconfigure your Exim4 options, which you can do by typing the command "dpkg-reconfigure exim4-config".  Then you'll get a screen, which you'll have to answer a few options. A lot of them are easy but they can be a bit off putting.

I don't think there's much reason to split configuration files (comment if I'm wrong). Your system mail name should be your URL, including the subdomain if that's where you've pointed your VPS. For instance, mine is "trinity.allroundnews.co.uk", so all email addresses will be 'user@trinity.allroundnews.co.uk'. Let the 'listener daemon' listen to all IPs, by just leaving it blank. Say no to 'Dial-on-Demand'. The 'Root and postmaster mail recipient' is basically the user that you want mail to 'root@trinity.allroundnews.co.uk' to be redirected to (since you shouldn't stay logged in as root). So for me, that's just 'shamess'.

If you've tried to send any mail so far, you'll want to remove it from the queue (sorry if it was important). Do that by getting up the mail queue (using the mailq command). Then you can remove mail by using the command Exim -Mrm <message id>. You can put as many message ID's in the list as you like, space separated. The message ID is the weird string that looks something like ’1JOJBI-0000ag-IH'. Yup, you have to type that.

I'll talk about actually reading mail later, but at least now you can send mail with PHP!