The Day Today

Yesterday my flatmate’s website host upgraded his version of MySQL and didn’t think to bring the tables to the new version. It’s easy to tell him that he should have a recent backup (he did, actually) but it got me thinking about a database I set up for CLEIS recently, which wasn’t being backed up at all…

Within about 20 minutes I had a daily backup running that will e-mail me the compressed SQL for the entire database. This uses a PHP file (stored outside the public_html directory for security reasons) that retreives and e-mails the database backup from the cPanelX control panel provided by my host.

Here is the contents of backup.php:

<?php
// Ignore all headers
#!/usr/local/bin/php -q

function get_db_file($db, $domain, $username, $password) {

  // Construct URL from segments
  $url = ‘http://’ . $username . ‘:’ . $password .’@’. $domain .’/getsqlbackup/’. $db .’.gz’;

  // Get the contents of the file
  return(file_get_contents($url));
}

function email_db_file($to, $from, $subject, $message) {
  global $db, $domain, $username, $password;
  $unique_sep = md5(uniqid(time()));

  // Set the e-mail headers
  $headers .= “From: $from\n”.
  “MIME-Version: 1.0\nContent-Type: multipart/mixed;boundary=\”$unique_sep\”;\n”.
  “charset=\”iso-8859-1\”\nContent-Transfer-Encoding: 7bit\n\n” .
  “–$unique_sep\n”.
  “Content-Type: text/plain; charset=\”iso-8859-1\”\n”.
  “Content-Transfer-Encoding: 7bit\n\n”.
  $message.”\n\n”.
  “–$unique_sep\n”.
  “Content-Type: gz; name=\”$db.gz\”\n”.
  “Content-Transfer-Encoding: base64\n”.
  “Content-Disposition: attachment\n\n”;

  // Use earlier function to get database file
  $file = get_db_file($db, $domain, $username, $password);

  // Attach the file to the e-mail
  $headers .= chunk_split(base64_encode($file)) . “–$unique_sep–\n”;

  // Send the e-mail
  if(mail($to, $subject, $message, $headers)) {
    return true;
  } else {
    return false;
  }
}

// Database name
$database_name = “db”;

// Domain name
// Make sure you include the port (:2082) for cPanelX.
$domain = “example.com:2082″;

// cPanelX username and password
$username = “username”;
$password = “password”;

// Address to send the backup file to
$email = “email@example.com”;

// Address the backup will be sent from
$from = “backups@example.com”;

// Call the function to send the e-mail
send_db_file($email, $from, $db .” Database Backup:”, “The scheduled backup of “. $db .” was performed at “. date(”g:i A F j, Y”));
?>

You’ll then need to set up a cron job in cPanelX to run the command php -q /home/[username] /backup.php on whatever schedule you need. You could even set up a second or third cron job to run on different schedules so that you can easily organise multiple generations of backups.