Clean up WordPress Database

WordPress has gone through many updates over the past year or so and if you have gone through the updates along with using and updating multiple plugins, you may find your WordPress blog or site to be running pretty sluggish. You could even experience database errors after an upgrade, especially with version 2.7. What I am going to attempt to explain in this WordPress guide is not for the total newbie and some technical knowledge will be needed if you want to attempt to do this.

These are the steps I took to clean up my WordPress database well not really clean up the database but move the old WordPress content to a fresh install on a new database. If you started your WordPress blog on an older version, around and just a guess version 2.2, the database tables were named differently that the current version 2.7, this might be causing some of the database errors after an upgrade. If you have a decent understanding of WordPress and how to move files with an FTP client or within your hosting account then you should be able to do this or use some of the ideas to move your WordPress blog.

wparena assumes no responsibility for what happens to your WordPress blog if you follow any of the steps outlined below. Make sure to make a full backup of your files and database before proceeding!

The first step was to do a new fresh install of WordPress in a new directory on your domain. Copy the wp-content folder from the original blog to this new one. After this is done don’t do much configuring on it other than the general settings, permalinks, and necessary plugins to get your theme functioning correctly. If you use the Akismet or Stats plugin don’t activate it yet. I made this mistake and when I added the WordPress API key after the move WordPress.com treated this like a new blog and my stats got reset. I am not sure if it matters and that still may happen regardless of when you activate those plugins.

Probably the hardest part is importing the old posts, and you may want to try this part early on to see if you can do it. Depending on your hosting accounts server options you will probably need to edit the php.ini file or .htaccess file to allow for uploading the large export file from the old blog. Again this is what took me while to get fixed and it took some Google time to get it figured out. Since all hosting accounts are different the way you go about it will vary. So please don’t ask me about this part here, I will tell you all I know now:

First, export the old content from the original WordPress blog. In the dashboard go to Tools, Export and save the file on your computer. Then on the new WordPress dashboard, you can try to import the posts. You might get lucky and this will work the first try if not then here are some suggestions:

If you have a php.ini file on your hosting account you can place a copy of it in the new blog’s wp-admin folder. Open the file to edit it, find the code shown below and increase the memory limit to 20mb and add the post_max_size = 20M section as well.

; Maximum allowed size for uploaded files.
upload_max_filesize = 20M
post_max_size = 20M

If you don’t have access to the php.ini file you can try to place the following code in the .htaccess file.

php_value memory_limit 20M

You can also try to edit two files in the wp-admin folder to increase the memory size as explained here:
WordPress Error Allowed memory size

You may need to experiment with the limit size for your particular file and you should set it back to the original size after you successfully imported the posts.

Another option is to contact your hosting account and ask they increase the memory limit for you. Or as a last resort, you could cut the export file into smaller files. More on the subject here: WordPress Support Forum

Once the new blog is running you can toggle back and forth from the current blog to the new one to get your sidebar widgets and any other items that are visible on the front side of the blog setup. Take this as far as you want, if you want the move to be as transparent as possible then you are trying to duplicate the look of the old blog with this new one. So now you should have a pretty close duplicate blog running on your site in a different directory

Now it’s time to make the switch and this step is optional but I suggest you do it as a safety net and that is to move the original blog to another new directory. Create a new directory and copy all the WordPress files from the original blog into that directory.  Login to the original blog, go to the general settings menu and change both the WordPress address (URL) and the Blog address (URL) to the new directory. The login for the original blog will change to the new URL: domain.com/newblogdirectory. I suggest moving the old blog instead of deleting it at this point to be able to get into the old dashboard and check and copy any settings to finalize the new one.

Next, move the new blog into the old blogs directory. In the new blogs dashboard change both the WordPress address (URL) and the Blog address (URL) to the old original blogs directory. Don’t do or click anything else at this point until you move the files! Next, copy all the files from the new blog and overwrite all the files in the old original directory. That should finalize the move. Now you have the old original blog still running and stashed away as a backup to work from and new fresh blog running where the old one once was. You should be able to delete the directory where you setup the new blog at this point.

Now you can finalize the new blog that is now in the old directory, and once you are sure the move is complete delete the old original blog that is in its new directory. If anyone is familiar with moving WordPress or files within a hosting account you may wonder why I say to move all the files instead of just renaming the directories right? Well, there are shortcuts you can make with the process and that is one of them if the move is from one directory to another. But you may be moving the new blog from a directory to the root of the domain. So, in that case, I suggest copying over the old files with new ones or at least the wp-config.php file. Remember the single most important file to move is the wp-config.php file, it determines what database to use.

Update! If you moved a blog from one directory to another make sure to change the Uploads settings under the Miscellaneous module to reflect the correct directory your blog is in. Failure to do this will drive you nuts when you attempt to upload images into a post.

Editorial Staff

Editorial Staff

Editorial Staff at WPArena is a team of WordPress experts led by Jazib Zaman. Page maintained by Jazib Zaman.

Leave a comment

Your email address will not be published. Required fields are marked *