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.