Yesterday I mentioned that I was working on a script that would backup my Wordpress database and email it to me. Well, I finished setting it up but ran short on time to post about it.

I came across this while surfing one of my favorite Linux forums and it made absolute sense to me. See, even though I had a backup plugin running in Wordpress itself, this would sort of back that up in the event of one failing to go through.

So, after making the necessary adjustments for it to work on my database (yours may differ depending on your hosting provider), I uploaded the file to a specified folder in my public_html directory and then proceeded to create the cron job to run it.

“Please note that all scripts should be with a .php extension, the file should have 755 permissions”

It works perfectly and I couldn’t be more satisfied with the results. Now I have one backup emailed to me on a Monday and another on a Thursday. Seems a bit overkill to have two but one never knows just what kind of problem lies in wait when it comes to servers and issues out of the customer’s control.

I’ve pasted the cron job and the script here so if you want to give it a try yourself, all you need to do is to copy and paste it into your favorite text editor like gedit (Linux) or Notepad (Windows). Keep in mind that the path to the script has to be correct or it’ll fail to work.

This first one is the Cron Job itself:

php -q /home/username/public_html/cron/db_bu_script.php

Save as a .php file when finished editing the above cron job. Keep the paths to the back up script exact or it won’t work.

The second one is the actual script that I named “db_bu_script.php“. You can name it whatever you wish for yours.

<?
$datestamp = date(”Y-m-d”); // Current date to append to filename of backup file in format of YYYY-MM-DD

/* CONFIGURE THE FOLLOWING SEVEN VARIABLES TO MATCH YOUR SETUP */
$dbuser = “XXXXXXXX”; // Database username
$dbpwd = “XXXXXXXX”; // Database password
$dbname = “XXXXXXXX”; // Database name. Use –all-databases if you have more than one
$filename= “backup-$datestamp.sql.gz”; // The name (and optionally path) of the dump file
$to = “youremail@yourmail.com”; // Email address to send dump file to
$from = “youremail@yourmail.com”; // Email address message will show as coming from.
$subject = “MySQL backup file”; // Subject of email

$command = “mysqldump -u $dbuser –password=$dbpwd $dbname | gzip > $filename”;
$result = passthru($command);

$attachmentname = array_pop(explode(”/”, $filename)); // If a path was included, strip it out for the attachment name

$message = “Compressed database backup file $attachmentname attached.”;
$mime_boundary = “<<<:” . md5(time());
$data = chunk_split(base64_encode(implode(”", file($filename))));

$headers = “From: $from\r\n”;
$headers .= “MIME-Version: 1.0\r\n”;
$headers .= “Content-type: multipart/mixed;\r\n”;
$headers .= ” boundary=\”".$mime_boundary.”\”\r\n”;

$content = “This is a multi-part message in MIME format.\r\n\r\n”;
$content.= “–”.$mime_boundary.”\r\n”;
$content.= “Content-Type: text/plain; charset=\”iso-8859-1\”\r\n”;
$content.= “Content-Transfer-Encoding: 7bit\r\n\r\n”;
$content.= $message.”\r\n”;
$content.= “–”.$mime_boundary.”\r\n”;
$content.= “Content-Disposition: attachment;\r\n”;
$content.= “Content-Type: Application/Octet-Stream; name=\”$attachmentname\”\r\n”;
$content.= “Content-Transfer-Encoding: base64\r\n\r\n”;
$content.= $data.”\r\n”;
$content.= “–” . $mime_boundary . “\r\n”;

mail($to, $subject, $content, $headers);

unlink($filename); //delete the backup file from the server
?>

The first 7 variables are all that may need editing to reflect your set up. Actually, the first 3 and the last 3 may be all you really need to edit. Experiment on yours to see.
Now remember to set the time(s) and day(s) that you want it to run and the email address to send it to and you’re set. I created an email account just for the sole purpose of sending the backup file to my GMail account that I have set up for IMAP. This way I get it in Thunderbird and can copy it to a folder on my hard drive or to one in my external hard drive for safe keeping.

Here’s an example:

Keep in mind that this works quite well on a Linux server (may or may not work on a Windows server) and you may need to contact your web hosting provider for assistance if the need should arise.