Jeff Atwood’s recent blog failure has probably served as a timely reminder to website owners across the ‘net – and I’m pleased to see that Jeff got his site back (some of us would be less lucky).  At the time, I tweeted this:

“RT @edbott: Every webmaster’s nightmare: ^MW Feeling sorry for @codinghorror and thanking @alexcoles for my backups”

To which the response from my hosting provider came back as “er… what backups?!”.  Thankfully, that was an attempt at humour (either that or Alex had forgotten that he’d helped me set some backups last summer – it helps to be on first name terms with the guys who look after your Internet presence)!

In common with many blogs, markwilson.it runs on self-hosted WordPress and the key to its existence is a MySQL database.  Themes, etc. can be recreated… the data cannot (at least not easily).

This is where the following script comes in.  Run as a cron job on my host’s server, I have it set up to make a copy of my WordPress database and e-mail it to me (which means I have archive copies in my Google mail).  It took some tweaking (by Alex) to run without errors and I’d like to link to it’s original source but the link I have appears to be dead – I’ve left the original author’s comments intact though – hopefully he won’t mind:

#! /usr/bin/php -q
dev/null

*/

// configure your database variables below:
$dbhost = ‘localhost’; // Server address of your MySQL Server
$dbuser = ‘db_username’; // Username to access MySQL database
$dbpass = ‘db_password’; // Password to access MySQL database
$dbname = ‘db_name’; // Database Name

// Options You May Optionally Configure

$use_gzip = “no”; // Set to No if you don’t want the files sent in .gz format
$remove_sql_file = “yes”; // Set this to yes if you want to remove the .sql file after gzipping. Yes is recommended.
$remove_gzip_file = “yes”; // Set this to yes if you want to delete the gzip file also. I recommend leaving it to “no”

// Configure the path that this script resides on your server.

$savepath = “/home/username/wpbackup”; // Full path to this directory. Do not use trailing slash!

$send_email_w_file = “yes”; // Do you want this database backup sent to your email? Fill out the next 2 lines
$to = “emailaddress”; // Who to send the emails to
$from = “emailaddress”; // Who should the emails be sent from?
$senddate = date(“j F Y”);
$subject = “markwilson.it WordPress backup”; // Subject in the email to be sent.
$headers = “From: $from”;
if ($send_email_w_file == “yes”)
{
$message = “Attached is a MySQL dump of sitename dated $senddate.”;
// Brief Message.
}
else
{
$message = “Your MySQL database has been backed up and placed in $savepath.”; // Brief Message.
}

$use_ftp = “no”; // Do you want this database backup uploaded to an ftp server? Fill out the next 4 lines
$ftp_server = “localhost”; // FTP hostname
$ftp_user_name = “ftp_username”; // FTP username
$ftp_user_pass = “ftp_password”; // FTP password
$ftp_path = “/”; // This is the path to upload on your ftp server!

// Do not Modify below this line! It will void your warranty!

//———GZIP CLASS
class zipfile
{

var $datasec = array(); // array to store compressed data
var $ctrl_dir = array(); // central directory
var $eof_ctrl_dir = “\x50\x4b\x05\x06\x00\x00\x00\x00”; //end of Central directory record
var $old_offset = 0;

function add_dir($name)

// adds “directory” to archive – do this before putting any files in directory!
// $name – name of directory… like this: “path/”
// …then you can add files using add_file with names like “path/file.txt”
{
$name = str_replace(“\\”, “/”, $name);

$fr = “\x50\x4b\x03\x04”;
$fr .= “\x0a\x00”; // ver needed to extract
$fr .= “\x00\x00”; // gen purpose bit flag
$fr .= “\x00\x00”; // compression method
$fr .= “\x00\x00\x00\x00”; // last mod time and date

$fr .= pack(“V”,0); // crc32
$fr .= pack(“V”,0); //compressed filesize
$fr .= pack(“V”,0); //uncompressed filesize
$fr .= pack(“v”, strlen($name) ); //length of pathname
$fr .= pack(“v”, 0 ); //extra field length
$fr .= $name;
// end of “local file header” segment

// no “file data” segment for path

// “data descriptor” segment (optional but necessary if archive is not served as file)
$fr .= pack(“V”,$crc); //crc32
$fr .= pack(“V”,$c_len); //compressed filesize
$fr .= pack(“V”,$unc_len); //uncompressed filesize

// add this entry to array
$this -> datasec[] = $fr;

$new_offset = strlen(implode(“”, $this->datasec));

// ext. file attributes mirrors MS-DOS directory attr byte, detailed
// at http://support.microsoft.com/suppor…s/Q125/0/19.asp

// now add to central record
$cdrec = “\x50\x4b\x01\x02″;
$cdrec .=”\x00\x00″; // version made by
$cdrec .=”\x0a\x00″; // version needed to extract
$cdrec .=”\x00\x00″; // gen purpose bit flag
$cdrec .=”\x00\x00″; // compression method
$cdrec .=”\x00\x00\x00\x00”; // last mod time & date
$cdrec .= pack(“V”,0); // crc32
$cdrec .= pack(“V”,0); //compressed filesize
$cdrec .= pack(“V”,0); //uncompressed filesize
$cdrec .= pack(“v”, strlen($name) ); //length of filename
$cdrec .= pack(“v”, 0 ); //extra field length
$cdrec .= pack(“v”, 0 ); //file comment length
$cdrec .= pack(“v”, 0 ); //disk number start
$cdrec .= pack(“v”, 0 ); //internal file attributes
$ext = “\x00\x00\x10\x00”;
$ext = “\xff\xff\xff\xff”;
$cdrec .= pack(“V”, 16 ); //external file attributes – ‘directory’ bit set

$cdrec .= pack(“V”, $this -> old_offset ); //relative offset of local header
$this -> old_offset = $new_offset;

$cdrec .= $name;
// optional extra field, file comment goes here
// save to array
$this -> ctrl_dir[] = $cdrec;

}

function add_file($data, $name)

// adds “file” to archive
// $data – file contents
// $name – name of file in archive. Add path if your want

{
$name = str_replace(“\\”, “/”, $name);
//$name = str_replace(“\\”, “\\\\”, $name);

$fr = “\x50\x4b\x03\x04”;
$fr .= “\x14\x00”; // ver needed to extract
$fr .= “\x00\x00”; // gen purpose bit flag
$fr .= “\x08\x00”; // compression method
$fr .= “\x00\x00\x00\x00”; // last mod time and date

$unc_len = strlen($data);
$crc = crc32($data);
$zdata = gzcompress($data);
$zdata = substr( substr($zdata, 0, strlen($zdata) – 4), 2); // fix crc bug
$c_len = strlen($zdata);
$fr .= pack(“V”,$crc); // crc32
$fr .= pack(“V”,$c_len); //compressed filesize
$fr .= pack(“V”,$unc_len); //uncompressed filesize
$fr .= pack(“v”, strlen($name) ); //length of filename
$fr .= pack(“v”, 0 ); //extra field length
$fr .= $name;
// end of “local file header” segment

// “file data” segment
$fr .= $zdata;

// “data descriptor” segment (optional but necessary if archive is not served as file)
$fr .= pack(“V”,$crc); //crc32
$fr .= pack(“V”,$c_len); //compressed filesize
$fr .= pack(“V”,$unc_len); //uncompressed filesize

// add this entry to array
$this -> datasec[] = $fr;

$new_offset = strlen(implode(“”, $this->datasec));

// now add to central directory record
$cdrec = “\x50\x4b\x01\x02″;
$cdrec .=”\x00\x00″; // version made by
$cdrec .=”\x14\x00″; // version needed to extract
$cdrec .=”\x00\x00″; // gen purpose bit flag
$cdrec .=”\x08\x00″; // compression method
$cdrec .=”\x00\x00\x00\x00”; // last mod time & date
$cdrec .= pack(“V”,$crc); // crc32
$cdrec .= pack(“V”,$c_len); //compressed filesize
$cdrec .= pack(“V”,$unc_len); //uncompressed filesize
$cdrec .= pack(“v”, strlen($name) ); //length of filename
$cdrec .= pack(“v”, 0 ); //extra field length
$cdrec .= pack(“v”, 0 ); //file comment length
$cdrec .= pack(“v”, 0 ); //disk number start
$cdrec .= pack(“v”, 0 ); //internal file attributes
$cdrec .= pack(“V”, 32 ); //external file attributes – ‘archive’ bit set

$cdrec .= pack(“V”, $this -> old_offset ); //relative offset of local header
// echo “old offset is “.$this->old_offset.”, new offset is $new_offset
“;
$this -> old_offset = $new_offset;

$cdrec .= $name;
// optional extra field, file comment goes here
// save to central directory
$this -> ctrl_dir[] = $cdrec;
}

function file() { // dump out file
$data = implode(“”, $this -> datasec);
$ctrldir = implode(“”, $this -> ctrl_dir);

return
$data.
$ctrldir.
$this -> eof_ctrl_dir.
pack(“v”, sizeof($this -> ctrl_dir)). // total # of entries “on this disk”
pack(“v”, sizeof($this -> ctrl_dir)). // total # of entries overall
pack(“V”, strlen($ctrldir)). // size of central dir
pack(“V”, strlen($data)). // offset to start of central dir
“\x00\x00”; // .zip file comment length
}
}

//——— end class

$date = date(“Ymd-Hi”);
$filename = “$savepath/$dbname-$date.sql”;
passthru(“/usr/bin/mysqldump –opt -h$dbhost -u$dbuser -p$dbpass $dbname >$filename”);

if($use_gzip==”yes”){
$zipfile = new zipfile();

// add the subdirectory … important!
$zipfile -> add_dir($savepath);

// add the binary data stored in the string ‘filedata’
$file6 = fopen($filename,’rb’);
$filedata = fread($file6,filesize($filename));
fclose($file6);
//$filedata = “(read your file into $filedata)”;
$zipfile -> add_file($filedata, $filename);

// OR instead of doing that, you can write out the file to the loca disk like this:
$filename3 = $savepath.”/”.$dbname.”.”.$date.”_sql.tar.gz”;
$fd = fopen ($filename3, “wb”);
$out = fwrite ($fd, $zipfile -> file());
fclose ($fd);

//$zipline = “tar -czf “.$dbname.”.”.$date.”_sql.tar.gz “.$dbname.”-“.$date.”.sql”;
//echo shell_exec($zipline);
}

if($use_gzip==”yes”)
{
$filename2 = $filename3;
}
else
{
$filename2 = “$savepath/$dbname-$date.sql”;
}

if($send_email_w_file == “yes” )
{
$fileatt_type = filetype($filename2);
$fileatt_name = “”.$dbname.”-“.$date.”_sql”; // change to _sql.tar.gz if gzip file

// Read the file to be attached (‘rb’ = read binary)
$file = fopen($filename2,’rb’);
$data = fread($file,filesize($filename2));
fclose($file);

// Generate a boundary string
$semi_rand = md5(time());
$mime_boundary = “==Multipart_Boundary_x{$semi_rand}x”;

// Add the headers for a file attachment
$headers .= “\nMIME-Version: 1.0\n” .”Content-Type: multipart/mixed;\n” .” boundary=\”{$mime_boundary}\””;

// Add a multipart boundary above the plain message
$message = “This is a multi-part message in MIME format.\n\n” .”–{$mime_boundary}\n” .”Content-Type: text/plain; charset=\”iso-8859-1\”\n” .”Content-Transfer-Encoding: 7bit\n\n” .
$message . “\n\n”;

// Base64 encode the file data
$data = chunk_split(base64_encode($data));

// Add file attachment to the message
$message .= “–{$mime_boundary}\n” .”Content-Type: {$fileatt_type};\n” .” name=\”{$fileatt_name}\”\n” .”Content-Disposition: attachment;\n” .” filename=\”{$fileatt_name}\”\n” .”Content-Transfer-Encoding: base64\n\n” .
$data . “\n\n” .”–{$mime_boundary}–\n”;
}

// Send the message
$ok = @mail($to, $subject, $message, $headers);
if ($ok)
{
echo “Database backup created and sent! File name $filename2”;
}
else
{
echo “Mail could not be sent. Sorry!”;
}

if($use_ftp == “yes”)
{
$ftpconnect = “ncftpput -u $ftp_user_name -p $ftp_user_pass -d debsender_ftplog.log -e dbsender_ftplog2.log -a -E -V $ftp_server $ftp_path $filename2”;
shell_exec($ftpconnect);
echo “

$filename2 Was created and uploaded to your FTP server!

“;
}

if($remove_sql_file==”yes”)
{
exec(“rm -r -f $filename”);
}

if($remove_gzip_file==”yes”)
{
exec(“rm -r -f $filename2”);
}
?>

All you need to do is: save this on a web server somewhere; edit the variables at top of the script; and set up a cron job to run it at whatever schedule suits.

For many people, that should be enough to ensure that their WordPress installation is safe from any catastrophe (note: no warranty is implied and I’m not responsible if things do go wrong for you).  For me though, I have an additional consideration as my images are not in the database… maybe that will be the subject of a future blog post.  In the meantime, I can sleep soundly knowing that the database behind my site is backed up regularly.