As I build this site I decided to create my own site analytics instead of using Google Analytics. I need to create a solid backup system to save those analytics in case something goes wrong with my database.

This solution has two parts:

• A mysqldump that saves an export of my whole databse into a file
• A PHP script that mailes the export as an attachment to a Gmail mail account.

Both parts are automated using cron.

The mysqldump was pretty easy:

/usr/bin/mysqldump -u x --password=y z | gzip > /backups/export.sql.gz 2> /home/vgemtdpx/backups/export.sql.err

Replace the x with your database username, the y with your password and the z with the name of the database you want to backup.

That command tries to dump an export in /backups/export.sql.gz, if it can’t (if there is any output) store that output (the error) instead in export.sql.err.

You can cron that right away.

For the next part we use PHPMailer so we can attach the export. This is the backup.php:

$address = 'x@gmail.com';
$password = 'y'
$name = 'Mike van Rossum';

$path = dirname(FILE);

require $path . '/path/to/phpmailer.php';

$mail = new PHPMailer();

$mail->IsSMTP();
$mail->SMTPDebug = 2;

gmail settings

$mail->SMTPAuth = true;
$mail->SMTPSecure = "ssl";
$mail->Host = "smtp.gmail.com";
$mail->Port = 465;
$mail->Username   = $addres;
$mail->Password   = $password;

the senders

$mail->SetFrom($addres, $name);
$mail->AddReplyTo($addres, $name);

$mail->Subject = "daily backup of DB: mikevanrossum.nl";
$mail->AltBody = "See attachment";
$mail->MsgHTML("See attachment");

$mail->AddAddress($addres, $name);

add attachments

$mail->AddAttachment('/backups/export.sql.gz');
$mail->AddAttachment('/backups/export.sql.err');

Once you’ve replaced it with your data you can cron this script to using something like this:

/usr/local/bin/php -q -f /path/to/backup.php

Make sure the mysqldump runs first and the PHP runs an interval later (mine is set for 15 minutes), my experience is that both take a couple of seconds to complete but it’s better to be safe than sorry.