In this post I will describe a database backup solution that I’ve been using for some time. Here’s what happens: this PHP script calls mysql to do a db_dump — this gets dumped to a text file — then it zips all this information up as a gunzzip file, then it emails the txt.gz file as an attachment to a gmail account (where you have unlimited storage). Easy!

Before you make this script — you need to have a location where these files can be stored: for this example, it is /backup/site_backups/

Also - you will need the PHPMailer class. You can read a nice tutorial about it here.

OK - Here’s the context of the script:

  • mysqldump
  • sleep — wait 2 seconds
  • gzip
  • sleep — wait 2 seconds
  • create the mailer object and add this txt.gz to the attachments, then send the email.

Easy.

Here’s the code:

<?php
$filename="backup-".date("d-m-y-h-ia");
//I assume you are running this on localhost - use your own username and password
exec("mysqldump -h127.0.0.1 -uusername -ppassword dbname > /backup/site_backups/".$filename.".txt");
//wait 2 s
sleep(2);
//zip it up
exec("gzip /backup/site_backups/".$filename.".txt");
//wait 2 s
sleep(2);
//you need to have this class.
require("class.phpmailer.php");
$mail = new PHPMailer();
$mail->FromName = "from@yoursite.com";
$mail->AddAddress("where@tosend.com");
$mail->AddAttachment("backup/site_backups/".$filename.".txt.gz");
$mail->IsHTML(true);
//set up the subject/body
$mail->Subject = "Nightly DB Backup!";
$mail->Body = "Backup to ".date("D M Y h:iA").".";
if (!$mail->Send())
{
//if the message did not work we echo out why
echo "Message could not be sent.";
echo "Mailer error: ". $mail->ErrorInfo;
echo "
";
//exit
}
?>

Lastly, we need to add this to the crontab of the root user (so we have permissions, and we’re guaranteed it runs). So, let’s say my script is in /var/www/siteIwant.com/htdocs/db-backup.php. Get to the crontab location via the command “crontab -e” as root. Now you’ll be where you can enter something. Here’s what mine says:

42 0 * * * /usr/bin/php -q /var/www/siteIwant.com/htdocs/db-backup.php

Let’s dissect this a bit: I don’t know why I have it at that time, but it works =P. This script will run at 12:42AM every day of every month of every year. (The asterisks mean every.) You can read more on crontab here.

Hope you enjoyed this! Let me know if you have questions.

Cheers,

–Justin