October 30th, 2009 → 11:07 am @ Stuart // No Comments
When I was setting up my latest virtual server (courtesy of the ever fantastic VPS.net cloud), I wanted to come up with a simple hourly backup policy for the mySQL databases that reside on the machine. Every hour, I wanted to dump the databases, tar/zip them up in a single archive and email it with an appropriate date/time stamp to a backup store email account.
I’m a long time user of Google Apps for email, and an even longer time user of regular Gmail. As Google Apps (or a regular Gmail account) has a lot of storage space, it seems perfect for the job. I thought I’d see how easy it was to use one of these as an SMTP relay for any mail the server needed to send – and in this case – to send and store my backups.
With a lot of hunting around, I eventually pieced together a solution that worked from a few different sources. So in the interests of helping anyone who needs something similar, here’s how I did it. I’m on Ubuntu 8.04.3 LTS. I’m guessing later versions will be very similar (if not identical).
1. Set up Google Apps/Gmail accounts
I set up two – a ‘no-reply’ account I would use to send all email from the server, and a ‘backups’ account for receiving the backup emails.
2. Install and configure Postfix
There are many, many tutorials out there, and believe me a tried a few. Here’s one that actually worked. Well, mostly. The bit about tls_per_site:
smtp.gmail.com MUST comp1.bob.com MUST comp2.bob.com MUST p>
..skip the p>bit – it’s malformed html.
Do also make sure that you follow the information about setting up the Equifax certificate rather than the Thawte one – Google changed this in September and the system will not work if you use the wrong certificate.
Secondly, after following these instructions through any attempt to send mail was met with the following entry in /var/log/mail.log:
postfix/smtp[7275]: connect to 127.0.0.1[127.0.0.1]:10024: Connection refused
After a lot of looking around, I finally tracked it down to the following line in the main Postfix config file, located in /etc/postfix/main.cf:
content_filter = amavis:[127.0.0.1]:10024
Seems that this is a spam content filter which wasn’t installed correctly. In my case, since all I’m sending is backups, I didn’t need it. Commenting out this line and reloading Postfix (sudo postfix reload) sorted this out for me – mails were now sent.
3. Set up the database backup system
Again, there are many examples of this out there. I used this one (requires PEAR for PHP), and adjusted it quickly to work on multiple databases. Make sure your backup location is writable by the server.
4. Schedule the backup via CRON
Dead simple – create a new CRON task for your user account:
crontab -e
and enter the following line (replacing the path to your backup script as appropriate):
@hourly /usr/bin/php -q /home/[yourhome]/bin/backup.php &> /dev/null
My script runs hourly. Adjust as you need.
And that’s it. Hourly backups taken to a secure ‘offsite’ location with bags of storage space. Just what I was looking for.