In the final training post of this tutorial I would like to take you through recovering the database archive you created in Backup and Recovery: Backing Up Your Database.

I will discuss the times when you should consider a database restore, what tools to use for the restore and lastly a video showing how to do that restore.

When to Restore Your Database

The times when you recover your database will be very similar to restoring your file base namely

• Migrating your blog
• Hardware or system failure causing data corruption
• Roll back a failed update of WordPress
• Recovering from a hacking attack
• User error – you accidentally deleted yourdatabase for example
• As part of a test of your backup

The only difference is that you must spot where the error lies, if you have corrupt posts, restore the database, if you cannot find theme files restore the file base.

Stop Before You Do Anything Else

Backup the database as it is now.  This gives you a stable poitn to return to if you cause issues with your recovery.  You know it cannot get anyworse than it already is.

If you can, collect metrics, how many posts, comments, tags and categories do you have,  this can be obtained from the dashboard of your blog.  This will be used to help you test that recovery has worked correctly.

How To Restore Your Database

In our backup process we created a SQL archive file.  The contents will look something like this

—
— Database: `wpowners`
—

— ——————————————————–

—
— Table structure for table `wp_wpoc_comments`
—

CREATE TABLE IF NOT EXISTS `wp_wpoc_comments` (
`comment_ID` bigint(20) unsigned NOT NULL auto_increment,
`comment_post_ID` bigint(20) unsigned NOT NULL default ‘0’,
`comment_author` tinytext NOT NULL,
`comment_author_email` varchar(100) NOT NULL default ”,
`comment_author_url` varchar(200) NOT NULL default ”,
`comment_author_IP` varchar(100) NOT NULL default ”,
`comment_date` datetime NOT NULL default ‘0000-00-00 00:00:00’,
`comment_date_gmt` datetime NOT NULL default ‘0000-00-00 00:00:00’,
`comment_content` text NOT NULL,
`comment_karma` int(11) NOT NULL default ‘0’,
`comment_approved` varchar(20) NOT NULL default ‘1’,
`comment_agent` varchar(255) NOT NULL default ”,
`comment_type` varchar(20) NOT NULL default ”,
`comment_parent` bigint(20) unsigned NOT NULL default ‘0’,
`user_id` bigint(20) unsigned NOT NULL default ‘0’,
PRIMARY KEY  (`comment_ID`),
KEY `comment_approved` (`comment_approved`),
KEY `comment_post_ID` (`comment_post_ID`),
KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`),
KEY `comment_date_gmt` (`comment_date_gmt`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

—
— Dumping data for table `wp_wpoc_comments`
—

INSERT INTO `wp_wpoc_comments` (`comment_ID`, `comment_post_ID`, `comment_author`, `comment_author_email`, `comment_author_url`, `comment_author_IP`, `comment_date`, `comment_date_gmt`, `comment_content`, `comment_karma`, `comment_approved`, `comment_agent`, `comment_type`, `comment_parent`, `user_id`) VALUES
(1, 1, ‘Mr WordPress’, ”, ‘http://wordpress.org/’, ”, ‘2009-09-29 04:26:45’, ‘2009-09-29 11:26:45’, ‘Hi, this is a comment.<br />To delete a comment, just log in and view the post&#039;s comments. There you will have the option to edit or delete them.’, 0, ‘1’, ”, ”, 0, 0);

….AND MANY MORE

This extract from my archive files show the comamnds which will recover my comments table.  First the file recreates the tables, then it runs a series of insert commands which will add the data back into that recreated table.

It may not seem very technical, but essentiually, the recovery process is a series of reacreating tables and the placing the data back into the tables one row at a time.

PhPMYAdmin Restore

We used phpmyadmin to build the archive file and we are going to use it again to restore the data.  Load up phpmyadmin acording to the instructions supplied by your hosting company.

Navigate to your database and select the import function.  Browse to your saved archive file and click on go, your import and recovery will now begin.

How Long Will The Recovery Take

That all depends upon how big your blog is.  In the video acompanying this post, it takes less than a minute,  but a production blog with hundrerd of posts and many plugins will take far longer.

PANIC POINT!

When doing the recovery, your system will go off and appear to do nothing, you will panic that your recovery is not working, this is okay, I recommend that you practise your recovery so that you know how long it takes so you will not panic during a real recovery scenario.

Upon completion of the process phpmyadmin will return a status update of how may tables and records were recovered, this is when you know that the process is completed.

Keeping Your File Base and Database In Line

If you have restored your database to a point before a WordPress update you will need to run the update process again.  This can be done by running the following script

http://{YOUR DOMAIN}/wp-admin/upgrade.php

Testing the Recovery

Using the metrics we collected earlier, you should log into your site and ensure you have recovered the correct number of posts, comments etc.

VIDEO: Recovering the WordPress Database

Running time for this video is approximately 3 minutes.

[S3 bucket=wpocrestoredb text=View the video>>]restoreDB.html[/s3]

Discuss this Post

If you would like to discuss this topic, please  leave a comment