King of Fools

MT: Automatic Backups

Every blog starts out with a single post. Over time, it grows both in volume and as a history of thought and events. Before long, it becomes a collective work which should be preserved. Many tremble at the idea of losing all or even just a portion of one's posts, comments and trackbacks.

Such an event is rare, but it does happen. It happened to me not that long ago. Fortunately, I had made a backup about a week prior to the server crash. I also used Google's caching feature to capture the content of the missing week. I believe I only lost one post and about one week's worth of comments (translation: roughly 3 comments). Not too bad, but it inspired me to do better. I found a backup script and modified it to meet my own needs. Now, a complete backup of my MovableType database is e-mailed to me each evening.

In case there might be some other folks also interested in having a 'safety-net', this post contains both the script and installation instructions.

Please note that this script backs up a MySQL database. If you are using MT but not MySQL, this will not help you. Also note that if you use any other blogging software that does use MySQL, then this script will also work for you. Finally, your server must have both php support and cron for this to work.

Here are step by step instructions on creating an automated backup:

Step 1: Create a File Containing the Script

Copy the following script, paste it into a text editor (notepad, ultraedit, etc.) and save it as backup_db.php:

<?php

// mySQL backup & mail v1.02
// created by: Justin O. Kirk
// 
// copyrighted � 2001-2002 Justin Kirk

// Enhancements added by: King of Fools
// 
// http://www.king-of-fools.com
// October, 2003
// { Added compression option }
// { Added datestamp option }


// Run Script As:
// php -q /home/yourusername/locationofscript/backup_db.php 
//
// * Must be run using ssh or cron
// * Browser execution will fail
// * Take appropriate security precautions
// (Your DB Password is in this file)


//*** BEGIN variables ***//

// Backup Time Variables
  $backupdate = date("F j, Y");
  $backuptime = date("H:i");

//*** Edit Below ***//

// ==========================
// MySQL DATABASE INFORMATION
// ==========================

  // database host: (blank if localhost)
  $dbhost = '';
  
  // name of the database:
  $database = 'dbname';   
  
  // username with db access:
  $username = 'dbuser';
  
  // password for username:
  $password = 'dbpassword';

// ==============================
// BACKUP FILENAME & LOCATION
// ==============================

  // filename for the backup: (no extension)
  $backupas = "mysql";
  
  // absolute path to backup destination: (no trailing slash)
  $backupto = '/home/username/tmp';

// ===================
// E-MAIL INFORMATION:
// ===================

  // who the mail is from.
  $mailname = 'autobackup';
  
  // reply address (not needed)
  $mailfrom = '';
  
  // email address to send the database to.
  $mailto = 'you@yourdomain.com';
  
  // subject of email.
  $subject = "MySQL Backup - $backupdate";
  
  // message body.
  $message = "Backup Created at $backuptime on $backdate:";

// ========
// OPTIONS:
// ========

  $send_email       = '1';
  // 1=send backup copy via e-mail.     0= backup data, don't e-mail.
  
  $delete_local     = '1';
  // 1=delete local copy when done.     0=leave local copy when done.
  
  $compress_archive = '1';
  // 1=compress archive file with gzip. 0=I like large emails.
  
  $date_stamp       = '1';
  // 1=add datestamp to archive file.   0=Always use same backup name

//*** END of variables. ***//

//*************************************//
//*** Do Not Edit Beyond This Point ***//
//*************************************//
// Resolve Archive Target:
  if ($date_stamp) { $backupas = $backupas.'_'.date("Ymd"); }
  $backupfile = $backupas.'.sql';

// Call Functions
  backupdb();
  if ($compress_archive) { compressdb(); }
  if ($send_email)       { makeandsend(); }
  if ($delete_local)     { removedb(); }


// Functions
function backupdb() {
  global $dbhost,$username,$password,$database,$backupto,$backupfile;
  if($dbhost == "") {
    $backupcommand = "mysqldump -u$username -p$password $database >$backupto/$backupfile";
  } else {
    $backupcommand = "mysqldump -h$dbhost  -u$username -p$password $database >$backupto/$backupfile";
  }
  passthru ("$backupcommand", $error);
  if($error) { echo ("Dump Problem: $errorn"); exit;}
}

function compressdb() {
  global $backupto,$backupfile;
  $compresscmd = "cd $backupto; gzip -f $backupfile";
  passthru ("$compresscmd", $error);
  if($error) { echo ("GZip Problem: $errorn"); exit; }
  $backupfile = $backupfile.'.gz';
}

function removedb() {
  global $backupto,$backupfile;
  if(!unlink("$backupto/$backupfile")) { echo ("Cannot Remove $backupto/$backupas"); exit;}
}

function makeandsend() {
  global $backupto,$backupfile,$message,$mailto,$subject,$mailname,$mailfrom,$database;
  $mail_boundary = '--=nextpart_' . md5(uniqid(time()));
  $mail_head = "From: $mailnamernReply-to: $mailfromrn";
  $mail_head .= "MIME-Version: 1.0rn";
  $mail_head .= "Content-type: multipart/mixed; boundary="$mail_boundary"";
  $mail_head .= "rnrn";
  $mail_head .= "This is a multi-part message in MIME format.";
  $mail_head .= "rnrn";

  $db_file = $backupto.'/'.$backupfile;
  $fp = fopen($db_file, "r");
  $file = fread($fp, filesize($db_file));
  $file = chunk_split(base64_encode($file));

  $mail_body = "--$mail_boundaryrn";
  $mail_body .= "Content-type: text/plain; charset=us-asciirn";
  $mail_body .= "Content-transfer-encoding: 8bitrnrn";
  $mail_body .= " $messagern";
  $mail_body .= "--$mail_boundaryrn";

  $filename = basename($db_file);

  $mail_body .= "Content-type: application/octet-stream; name="$filename"rn";
  $mail_body .= "Content-transfer-encoding:base64rn";
  $mail_body .= "Content-Disposition: attachment; filename="$filename"rnrn";
  $mail_body .= $file. "rnrn";
  $mail_body .= "--$mail_boundary--";

  mail($mailto, $subject, $mail_body, $mail_head);
}

?>

Step 2: Modify the Script Variables

The variables define how to access the database and also where to mail the backup. The most important variables are as follows:

$dbhost Specifies the database host. It can be left blank if the database is hosted on your own domain. This value should be in the DBHost line in your mt.cfg. If there is no DBHost line, then it should be left blank.
$database Specifies the database name. This value is required and should be in the Database line in your mt.cfg.
$username Specifies the user with access to the database. This value is required and should be in the DBUser line in your mt.cfg.
$password Specifies the password for the username. This value is required and should be in your mt-db-pass.cgi.
$backupto Specifies the location where the backup should be created. This value is required. Running mt-check.cgi should give you a clue as to what the path structure of your site is. I would suggest placing the database in a directory called tmp just of the public_html.
$mailto Specifies the location where the backup should be sent. This value is required if $sendemail is set to '1'. It should contain the e-mail address you want the backup e-mailed to.
$send_email Determines if the backup should be e-mailed or not. '1'=send e-mail, '0'=do not send.
$delete_local Determines if the local copy should be deleted after it is e-mailed. '1'=delete local copy; '0'=leave local copy. This option is a good way to save space, but if you are not e-mailing the backup anywhere, then you do not want to delete the local copy.
$compress_archive Determines if the backup should compressed or not. '1'=compress backup; '0'=no compression. If your server does not have gzip installed, this must be set to '0'.
$date_stamp Determines if the backup filename should have a date-stamp in it. '1'=use date in filename; '0'=standard backup name.

Step 3: Load the Script onto Your Server

The script needs to be loaded onto your system. Because this script does contain your MySQL database password, you should put it in a non-public directory.

Step 4: Create a Cron Job for the Backup

A 'cron job' is a script which the server executes based on a schedule. Hopefully your host has a nice control panel based cron manager. If not, then you need to read up on the crontab command. My job is defined as follows:

30 23 * * * php -q /home/userdir/scripts/backup_db.php > /dev/null

The first five numbers define the minute, hour, day-of-month, month-of-year, day-of-week. The remainder of the definition specifies the shell command to execute. An astrisk means all values are permitted. Thus, my backup is performed at 11:30pm every evening.

Other Notes

I had some difficulty getting this script to work properly on a site hosted by 1and1. I finally got it functional using the following workaround:

I had to make a shell script which called the backup script. Here it is:

PATH=/usr/local/bin:/usr/bin:/usr/X11R6/bin:/bin:/usr/local/msql/bin
export PATH
cd /homepages/99/d99999999/htdocs/cron

echo "Executing Backup Script..."
echo "==========================n"
php4 -q backup_db.php
echo "n==========================n"
echo "Backup Script Complete!"

If you use this script, the path specified in the cd command will require modification. The cron command I used for this script is as follows:

50 23 * * * sh /homepages/99/d99999999/htdocs/cron/backup.sh > /dev/null

Again, the path is generic and would require modification.

The only part left to cover is how to use this backup file to recover from a blogtastrophe. That, I'm afraid, will have to be the subject of some future post.

If you want automatic backups but this looks impossible to pull off by yourself, I would be willing to set it up for you (for a small fee). Contact me via e-mail if you are interested.

King of Fools | 01/26/04 - 08:35 AM
Blogging

Trackbacks

The trackback URL for this entry is:
http://king-of-fools.com/blog/trackback/478/6MwZY9jp/

Blogging Stuff Todo
Just caching these links:Full-screen archive calendarsAutomatic backups...
Tracked by: : mindtangle on 03/22/2004 at 2:16 pm

Comments

This sounds like a great idea but my blog has gotten rather large--Entries: 4699 Comments: 9387

How big a file are we talking? It could seriously eat up my bandwidth, not to mention clog my server, if it’s huge.

Posted by: James Joyner - 10:18 AM - 01/26

I love this, however, I host multiple blogs (12 at last count). Will this backup the entire DB or would I have to modify the script and set cron jobs for each blog?

Posted by: Matt - 10:23 AM - 01/26

My current backup is 512k compressed (1.75GB uncompressed). That is for a fairly modest amount of data. Backup includes 5 blogs, the main one having a mere 478 entries and 360 comments. The other blogs are all very minor.

My current host does not count e-mails toward my bandwidth total. (You would have to check and see how your host treats pop3 traffic.)

It is very possible to disable the e-mail feature. You could either pick it up manually via ftp. Another option would be to have a script automatically ftp it to a second server (a data vault if you will).

Posted by: King of Fools - 10:27 AM - 01/26

Matt, if they all appear when you run mt.cgi, then they are all in the same database. This method dumps the entire database (structure and content) so it would backup everything.

Posted by: King of Fools - 10:29 AM - 01/26

Thanks!

Posted by: Matt - 11:23 AM - 01/26

Images and all???

By the way, just in case you’ve forgotten, I haven’t… thanks for installing MT for me!!!

Phillip Swindall
BrotherPhil
http://www.brotherphil.com

Posted by: BrotherPhil - 11:28 AM - 01/27

Hmmm...no, it doesn’t do images. It just dumps your entire MT database including all blogs, their content (comments, posts, trackbacks) and your templates into a file.

Posted by: King of Fools - 11:31 AM - 01/27

Yes, I’m a noob, but I want to know how do you save a crontab file via SSH. I do crontab -e, type in the lines needed, but I can’t exit from it. The only way is doing CTRL+Z that exits without saving…
Thanks

Posted by: AJ - 11:04 AM - 03/15

I’m trying to get this to work on a 1and1 hosted domain and am having trouble with the crontab. If I run the program from the command line it works, but with crontab it doesn’t. I’ve tried your shell script and am not having any luck with it. Can you explain what it is about 1and1 that required a workaround in the first place? With that information I think I could solve my problem. Thanks for the great post!

Posted by: - 10:39 PM - 04/27

It is working on one 1and1 host. If the script works then it is coded correctly and has the right permissions. (All good!)

The only thing I can think of with it not working through cron is a pathing issue in the cron statement or perhaps an incorrect path in the script. (When you ssh over, you have those values set but cron doesn’t get them when it tries to run something for you.)

I would start by making a very simple script which echos a statement and redirect it into a file. Then set up a cron job for it and let that time elapse. Once you get that working, then try the backup.sh once again.

If you really need assistance, I may be able to help. Time has been very short as of late, but just let me know. I may be able to eke out 15 minutes or so.

Posted by: King of Fools - 10:50 AM - 04/28

I did get it working shortly after I posted my question (that’s how it usually happens).

So it works, but I’m not certain what problem the script solves. Don’t worry about it if you’re busy, it works and that’s the important thing right now.

Incidentally, I’m extending the script to provide the option of sending the dump file to an ftp site rather than having it emailed.

Posted by: - 11:05 AM - 04/28

What the script does is it adds the necessary PATHS that are required to run the program.

When you log in, your .profile usually sets your paths so you can run programs like ‘l’ and ‘pwd’ etc.

Cron doesn’t execute your profile so most cron scripts must explicitly set those values (like where the php executable is).

Posted by: King of Fools - 11:59 AM - 04/28

Ok, I got it working! Looks great, but if my blogs ever go kaput, how do I restore everything? I’d like to try it, but I’m a bit terrified.

Posted by: Victor - 10:19 PM - 05/10

Victor, there are a few ways to do this. The file can be uploaded and executed as an SQL using ssh. Even better is if you have PHPMyAdmin access. (If you don’t have access, it is also possible to just install it yourself!)

Using that access, start by deleting all tables so the database is completely empty. Then click the SQL tab. Open your backup in a text editor and copy the entire content. Paste it into the box and click Go. It will reconstruct each table and the data that was within it.

This means all posts, comments, templates, everything. If you have multiple blogs defined in MT, all of them will be completely recreated.

The only potential drawback to this issue is if the size of the text box does not allow the entire backup to be pasted within it. The version of PHPMyAdmin I am using allow me to use a local file but it states that the file size limit is 51MB.

If that is still a problem, it would be possible for a discerning individual to break the file into parts and recreate the database piecemeal.

Posted by: King of Fools - 05:05 AM - 05/11

The nice Windows solution for this purposes is Backup Watcher for MySQL.
here is it - http://www.dswsoft.com/mbw_features.php

Enjoy!

Dmitry.

Posted by: Dmitry - 10:25 PM - 08/05

Post Your Comment

Commenting is not available in this weblog entry.