Free offsite backup via Google

Offsite backup is one of those luxuries that most small websites and bloggers will never have, mainly because of cost, but sometimes because our hosting solutions do not support or offer them.

So this week I lost the server that my sites sit on for a few days, and I realised that I have no real backup of the databases on there. I have a copy of the code as it’s within SubVersion, so there was never really any chance of losing that part of my site, but what about my database. This got me thinking, is there a way I can sort out a form of offsite database backup?

Initially I was thinking about what I’ve done in the past, set up a simple cronjob to run mysqldump on my database on a weekly basis to a file on disc. I would then log into the server and download this file (if I remembered) every now and again. Some of you would now say, OK good, you’ve solved your backup issue, mysqldump and download, so why go further. You’re right though, if you remember to download the backup files, and I can be sure that most of you reading this would not download every backup. You’d forget to, or be too busy to, and then BANG, your site falls over and you only have a back up from 4 weeks ago, which is when you last remembered to download that backup.

One thing you can do is set up a second hosting solution somewhere with ssh and scp access, which you could scp the backup to after running mysqldump. This is a great solution as you can then add a script that end to delete the really old backups and not use up loads of disc space. The problem is, that costs more money, and this is post is all about Free offsite backup.

At this point I assume you all know where I’m going with this one. Email. Yes, once you’ve made your database backup with mysqldump, why not send it to a googlemail email account? Googlemail kindly provides all their accounts with over 7GB of storage, so why not use it as offsite backup? Gmail support attachments of up to 25 megabytes, so why not send your database backup via email?

So the steps to create free offsite backup with Google are:
▪    Sign up a new GoggleMail account (or use your current one if you like)
▪    Make the script to run mysqldump on your database on a periodic basis
▪    Have that script email the backup file to your GoogleMail account.
▪    Then finally delete your backup file from disc so not to fill your hosting with backup files.

Example code:

<?php

$username = '';
$password = '';
$database = '';
$filename = sprintf("/tmp/backup-%s.sql", date('Ymd'));
$command  = sprintf("mysqldump -u%s -p%s %s > %s", $username, $password, $database, $filename);

exec($command);

if (file_exists($filename)) {
        $boundary = md5(time());
        $email    = 'me@example.com';
        $subject  = sprintf("DB Backup: %s", date('Y-m-d'));
        $data     = chunk_split(base64_encode(file_get_contents($filename)));

        // boundary
        $semi_rand = md5(time());
        $mime_boundary = "==Multipart_Boundary_x{$semi_rand}x";

        // headers for attachment
        $headers  = "MIME-Version: 1.0\nContent-Type: multipart/mixed;\n boundary=\"{$mime_boundary}\"";

        // multipart boundary
        $message  = "This is a multi-part message in MIME format.\n\n";
        $message .= "--{$mime_boundary}\n";
        $message .= "Content-Type: text/plain; charset=\"iso-8859-1\"\n";
        $message .= "Content-Transfer-Encoding: 7bit\n\n";
        $message .= "DB Backup Attached..\n\n";
        $message .= "--{$mime_boundary}\n";
        $message .= "Content-Type: {\"application/octet-stream\"};\n name=\"backup.sql\"\n";
        $message .= "Content-Disposition: attachment;\n filename=\"backup.sql\"\n";
        $message .= "Content-Transfer-Encoding: base64\n\n" . $data . "\n\n";
        $message .= "--{$mime_boundary}\n";

        @mail($email, $subject, $message, $headers);

        unlink($filename);
}

echo "DONE\n";

You should also make your script a little smarter and have the sql dump zipped into files less then 25MB and send in multiple emails if you have a large database, but I would assume that most small blogs would be under 25MB of core data (unless you include all your comments/logs in the backup).

4 thoughts on “Free offsite backup via Google”

  1. How easy is it to restore the database dump should you need to rebuild your database?

    I assume you just download the attachment, upload it to your hosting and then pull it into your database? Or is there a way to pull it straight out of your email into the database?

  2. Hi Adam,

    It’s very easy. All you need to do is use the following to restore the database from the file:

    mysql -u [username] -p [password] [database_name] < backupfile.sql As for getting the file from the email onto the server, I do not know of an automatic way of doing that. I would just download the file attachment and scp / ftp the file onto my hosting to use. Carl.

  3. @Adam:
    “Or is there a way to pull it straight out of your email into the database?”

    Yes, kinda. Google supports the IMAP interface. phpinfo() command will let you know if your php on your host also supports IMAP.. if it does, you are in luck!
    google “php imap google” for more info.
    …..

    Your best bet is to have a *real* backup solution not dependent on the ‘cloud’ or google’s policies. (depending on the database data, you may not want to expose your private data to google -or- anyone with access to that email account)

    The email is good though, as you have a happy timestamp of the backup transaction, and the key thing is, by its very nature, its READ ONLY. except for the email admins, nobody can tamper with your backup.
    HOWEVER:
    Your backup/security scenario MUST INCLUDE TESTING YOUR BACKUPS!
    A hosting account change on the backend may make your mysql db unrestorable. A glitch when backing up may make the resulting .sql file garbage.

    After you run the extract, you can run some sanity checks against the data, i.e. make sure the resulting file size is within limits. Because your backup is critically important when you need it, THE TIME TO VERIFY YOUR BACKUP IS WHEN IT IS MADE, NOT WHEN YOU ARE TRYING TO USE IT!

    Recommended approach:
    Obtain a second, mostly comparable hosting account on another server: You may go full on paranoid mode and use a different host (i.e. 1and1 for one account, and GoDaddy for the other)

    so even if the host goes down, you can be up and running quickly, pointing your domain records to the new location.

    If this site’s admin likes, i can run you through the method i have implemented for my own sites.

  4. I fully agree that testing the backup file is a MUST. Personally I test mine every other week by way of truncating my dev database on my laptop and importing the latest backup file.

    I would advise that you use the same connection details for your backup script as your website. This means that should something change with those details it will be very obvious when your site does not work properly.

    You could add some validation to the backup script above by way of a quick check that the dump contains all the database tables you want backed up, and some content. If there are more / less then you could trigger a different kind of email, or some kind of notification.

    Setting up secondary hosting is nice, but not always something we can all afford for smaller sites. In an ideal world you’d push your backup file via scp rather then email, and then have a hook set up on the other box to import the new dump and test it.

Leave a Reply

Your email address will not be published. Required fields are marked *