Recently our wordpress database got corrupted and needed to be repaired. In another article, What To Do When Your Wordpress Blog Vanishes, I wrote about how to using phpMyAdmin to repair tables that were corrupted .
But sometimes repairing tables does not fix the problem. You try to the repair tables command and get a bunch of error message like something pointing outside the table. The only thing left to do is restore the database from a recent backup.
How to Restore the Database
You can restore the wordpress database using the same phpMyAdmin control panel. Restoring the database should be a simple matter. If you have a newer version of phpMyAdmin there is a tab called Import that lets you browse for a MySQL backup file to upload. The backup file is a text file that contains all the MySQL query commands to build your database. The backup file I had was 14,007 lines long and was 12MB.
But life never simple. You often find that your server software is not up to date or is not configured the optimal way. For instance, we had an older version of phpMyAdmin, 2.6.3, that lacks the import command. With older versions of phpMyAdmin you have to do the restore by running SQL queries
A newer version phpMyAdmin could not be run because it requires PHP 5.2 and the server has PHP 4.3.11 installed. PHP 4 dates from 2005! You can learn these things by executing the phpinfo() command in a php script.
File Sizes & Timeouts
Here is the second obstacle you are liking to encounter when doing restores. If you have a small blog with a small backup file, there should be no problem executing all the SQL query commands in the backup. But if your backup is more than 2 or 3 MB you might run into problems.
Check upload_max_filesize to make sure your file size is not too large. Then check the timeout parameters. Typically you server administrator might have these set at 60 seconds. Unfortunately, it might take 5 or 10 minutes to run the restore operation, so you will get a timeout without completing the restore.
The solution is to split up the file using the unix split command. You can split text files by line or byte. In this case we would like equal-sized files of maybe 1 or 2 MB. But you have to split by line because each sql query is on one line. Open up the unix terminal on your Macintosh and type:
split -l 500 wordpress.sql
This command writes however many 500 line files are needed. They are named xaa, xab, xac, xad, xae, xaf, etc. You might have a lot of small files. I think I had about 30 files when I did this. Only a few were large. You can combine them with unix cat. Keep each file smaller than about 2MB so it doesn’t time out.
cat xaa xab xac xad >x1
Once you have the split up files, here are the steps to restore the MySQL database:
- Login to phpMyAdmin and select the wordpress database
- Observe the tables. Check All to select all the tables. Then under with selected choose Drop action to delete all the tables permanently.
- You will be prompted to confirm the Drop action in case you want to change your mind. Choose Yes, and all the tables are gone PERMANENTLY.
- If your phpMyAdmin has an Import tab you can use it. If not you must use the SQL tab. I’ll assume no Import tab. Click the SQL tab.
- You can either browse for your files or cut-and-paste into the text box. Browsing is easier. Run each of your split backup files in order.
- Once all the files are run, the restore is complete. Open up your blog in a web browser and make sure everything is there.
Here is a restore tutorial using SQL with nice illustrations with circles and arrows: Restore You Database
Here is tutorial that uses the Import tab:
How To Backup and Restore WordPress Database
