Restoring a large Wordpress Database

This article applies not only to people who use WordPress technology for their websites or blogs, but to anybody who uses any kind of CMS, for example, Joomla and Drupal, which uses a MySql database . However it may not apply to bloggers who use blogging software on third party servers such as wordpress.com or Blogger.

If you have a blog or website on your own server that uses a MySql database as WordPress does, and you update your blog or website frequently, your database has probably grown to several 10s of megabytes over the years. If so, unless you know how to backup your database and uploaded files to your PC, and also how to restore them to your server when needed, you are in jeopardy of losing hundreds of hours of hard work if the server crashes!

I’ve been using WordPress for this blog since the beginning of 2009 and the database has grown to nearly 60 megabytes. I ran into trouble yesterday and needed to restore a very recent backup of the database to my server. I learned I cannot do this through phpMyAdmin as I did with databases of smaller WordPress sites because phpMyAdmin does not allow importing of large files! I kept getting error messages saying I had exceeded the memory limit.

The answer was to bypass using phpMyAdmin entirely and use a script called “BigDump“. I learned about BigDump from http://educhalk.org/blog/2009/01/how-to-import-a-large-wordpress-database-an-alternative-to-phpmyadmin/ and followed the instructions. First I tried to use it to import the database I got using the phpMyAdmin’s database export process, but BigDump aborted in an error. I tried to follow the instructions on the above website how to fix that error, but it didn’t work for me. I then tried to import a different database backup file, one created by On Line Backup for WordPress, a plugin I recently installed. This time BigDump did its job! This blog with all of its posts, pages and theme tweaks, was restored! Only some of the graphics and plugins where missing. I restored them from the file backup which On Line Backup for WordPress created for me. Note that the reason I could do that was because I uploaded via FTP again a downloaded backup, not just one left on the server.