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.

Today I needed to rename a database in MySQL. I’m not sure if it’s possible but I decided to create a copy of the database instead with a new name and then drop the old one. This is really easily done using the following commands:

mysqladmin -h <hostname> create <new_dbname>
mysqldump --opt -h <hostname> <old_dbname> | mysql -h <hostname> <new_dbname>

The first command creates a new database on <host> called <new_dbname> and the second dumps <old_dbname> from <hostname> and sends the output directly into the third command, which simply executes the SQL statements into <new_dbname>.

Note that other parameters such as -u and -p are needed when security is enabled, and that the -h parameter can be excluded if both target and destination are the local machine.

The official documentation for mysqldump can be found here.