I guess eventually it would happen, the WordPress SQL database would eventually get corrupted from bad plugins. WordPress plugins are useful, but they also create configuration inserted into the WP_options MySQL table that are difficult to sort out. If the plugins worked well, no need to do anything. But if it misbehaves, just deleting the plugin may not necessary solve your WordPress website issues.

My personal WordPress blog has been around for many years now, and there are hundreds of posts. In between I have tried many plugins to add functionality and more importantly to harden it against would be hackers and make it difficult for comment spammers. The latter two options tend to cause problem for WordPress installation somehow. Most especially the captcha plugins tend to cause my WordPress site to behave weirdly. My recent captcha was causing the entire site to lag and hog CPU resources!

My previous fixed up on the MySQL database was to go directly into WP_options table and look for the offending parameters to allow my WordPress to run smoothly after removing the plugin. However this was time consuming and takes guesswork to figure out which entries were to be deleted. It was risky too as I could accidentally delete legitimate parameters needed by WordPress and other plugins to work.

This time round I figured out another way to clear all the gunk and leftover configurations from various plugins that was used and removed and of course remove parameters from runaway plugin scripts that were still causing the WordPress to lag and hog hosting resources despite deleting it from the installation.

For some reason or another, I tried removing all plugins, but the WordPress was still lagging like a snails’ race. Downloading the SQL database files and restore it to a brand new installation with all plugins running didn’t work either. Because I just moved the WP_options corrupted table from one WordPress installation to another. I tried using the export post feature, but the lag just done it in. It took ages to log in, and when I finally do get in, could not really navigate around due to heavy lag, and when I did get into the tool section the export post just died. Not able to extract my posts meant all my years of blog post would be gone. The serious lag was also causing 404 file not found errors and I was getting warning emails from Google Webmaster too about pages not found!

So I came up with this plan to extract my posts and pages, and it worked flawlessly. Here are my steps and hopefully if you similar issues you could use it too.

How to extract WordPress Posts and Pages from a faulty MySQL database and restore to a new WordPress instalation

• Backup MySQL database file and download a copy to the hard disk. Also backup the WordPress theme and plugins and any other modifications for later restore. (in cPanel, goto the Backup icon to do it, otherwise do it the hard way via phpMyAdmin)
• Create a new temporary WordPress installation in a different location or folder, make sure you use the same WordPress table prefix during creation, for the sake of this explanation, the table prefix is wp_
• Restore the MySQL database into this new WordPress by importing it via phpMyAdmin
• In phpMyAdmin, edit table wp_option, the first entry is the blog URL, change it to match the new location or you won’t be able to see your old posts
• Login to your temporary WordPress installation, navigate to TOOLS, then EXPORT all your posts and pages to a XML file and download it to your PC
• Delete your old faulty WordPress including the old MySQL database and install a new version over it
• Check your hosting PHP configuration for the upload_max_filesize. If the settings is 2MB you will not be able to upload the XML files if larger than this. So you will need to create a PHP.ini file to override this limitation
• Create a PHP.ini file and add these two lines
  

  file_uploads = On ; Whether to allow HTTP file uploads
  upload_max_filesize = 6M ; Maximum allowed size for uploaded files

  In my case, my XML file was 4MB, so I set it to 6MB.
  Place the PHP.ini file into every folder of WordPress that runs PHP script

• Login to your new WordPress installation and navigate to TOOLS and IMPORT
• You will need to install the WordPress import plugin to import the XML file
• After successfully import, check your old post and see if shows up, if yes, success!
• Restore your old themes (and plugins, but carefully) and other modifications and you are done!

The above method strips off a lot of gunk from the MySQL database. Upon checking my database, I found it was now a lot slimmer than before. From a previous size of about 18MB to a neat little 4MB database size. The WordPress site also runs smoother and that should satisfy the search bots and prevent the 404 file not found errors.

Only one problem, for some strange reason, one of my posts still could not be updated and still get 403 file permission error. All other old post I have no problem updated the content. Really weird. As long as I don’t touch it I suppose I can live with that. With this WordPress post extraction and restoration method, I now I have my website back on track!