Restore your WordPress Database From Mysql 4 to Mysql 5

March 8, 2010 update

Feel free to read this post and follow it, it is still applicable.   However, there is an easier way to do this.   I just updated a 4.0 to 5.o in three simple steps on GoDaddy.

  1. Login to your Hosting account and make a new 5.0 MYSQL database
  2. While that is being setup go to your existing database and click on the pencil to edit (Select Backup)   THe backup will be in your db-backup folder which you can access from FTP if you need to.
  3. Once the backup is complete click the edit pencil on the New 5.0 database (Select Restore)   Use the file you just backed-up from the 4.0 database.

The final step is to change your wp-config.php file to point to your new 5.0 database.   That’s all there is to it.

If you have a different hosting company the routine might have a different way of backing up and restoring, but the process should work.   Now on to the original post.


Is your blog showing its age. Was it created with mysql 4.0 or earlier? Then you know what it is like to click and wait and click and wait to do anything. Follow these simple none destructive steps and you can turbo charge your blog and bring it into the present century and learn how to backup and restore your MYSQL database while you are at it.

  1. Backup your database Wp-db-backup plugin
  2. Run the backup once it is installed. Check all the boxes to get all the data.
  3. extract the .gz file I use 7-zip open source for this
  4. create a new database on your host 5.0
  5. log into the new database
  6. import function
  7. browse and select uncompressed file
  8. Select the ascii option for character set usually utf8 will be selected
  9. click go
  10. change the wp-config.php file to point to the new 5.0 database
  11. upload the config file to your blog
  12. download and install the search and replace plugin
  13. activate the plugin You will find it under the “Manage” Tab
  14. search for ?? replace with “space character”

You should now be able to view your blog and see it running much faster than before. Here are a couple of screen shots you might find useful.

Restore blog files to new Database

Restore DB

Click on this graphic to make it larger so you can read the words if you have eyes as old as mine.

You should now have a much faster blog and it has a clean compact lay down of the data. And should there be any issues. The original database is still there. You can always go back to it by changing the wp-config.php file back to point to the original database.

If you have to restore the file again it will deactivate the search and replace plugin each time you do. Simply re-activate the plugin and run it again.

I had to use the ascii option to get ?? for the odd characters otherwise I got a question mark inside a solid black diamond and search and replace would not replace that character.

Possible Caveat

I was working on an old blog with just one unpublished post. It appeard that post was not backed up or restored. I would recommend making copies of any unpublished post you have on your blog. One easy way to do this is install Windows Live Writer and set it up. Then copy each of the unpublished posts to Live Writer. You could do the same to a series of Notepad files if you want to keep it simple.

Good Idea Here

Set the backup plugin to auto backup at least once a week. It will be a life saver if a server or your database ever goes down.

A good review post is Single Property Websites – Hosting

Now back to working on how I got the spell checker working when upgrading from 2.0.4 to 2.3.2. I did it, and can do it again, but need to know how I did it.

As always here at the lab any input or questions are certainly welcome.

Comments

  1. Just reading over this post. You have to be some kind of tech junkie to read this stuff.

    If you read all the way to the end you probably should seek out serious help : )

  2. Thanks realestatebloglab.com! I found this post very useful. Where do I seek some help?

  3. I found this post very helpful! Thaks for the info and keep it up. Going to seek serious help now.

  4. Jon,

    You can ask any question you have right here in the comments on this post.

  5. What is being accomplished by search-and-replace of ?? with space character?

    Is it necessary? If that is not done, what functionality is being messed-up? Thanks.

  6. bobby,

    As I stated in the post, if you don’t do that step all of your posts will be filled with question marks where there should be space characters in certain situation.

    If you do this conversion process you will see them.

  7. Hi Dave,
    I did the conversion, and view quite a number of posts. Did not see any aberrant (?). I went ahead and did the search-and-replace anyway.

    Just curious where you read/heard about the necessity for this step? Do you know an example post where the problem would occur?

    Thanks for good article. Very useful info.

  8. bobby,

    It occurred when I did the update on two of my blog sites. If you didn’t see them all the better, they are obvious everywhere if you have them.

    There are so many variable in this process, it could be the hosting company, the version of PHP etc.

    When they did appear in both of my conversions the process outlined removed them. Good to know it doesn’t occur in all instances.

    I don’t think you will see an example because it is really ugly and anyone that does get them will take the steps to remove them, they are very annoying and have to do with certain characters. Those character might be put in by the browser used to create the content in the first place and have nothing to do with MYSQL itself.

    I just ran into an instance where an Apple user was using the Opera browser to write blog posts. It was putting in hundreds of extra apple font formatting code which wasn’t visible till you looked at the post in code view. He switched to FF and the issue was resolved.

  9. Thanks for this post, I followed the instructions and got it done without any problem. Just what I needed.
    .-= w´s last blog ..Trash Metal Mania… =-.

  10. This post worked for me. I used your steps to update blogbaltimore and it worked like a charm. I was surprised by the additional step of searching and replacing ?? with the “space character”. It was needed though. Thanks again.
    .-= n0manarmy´s last blog ..Comics, Games, and Toys “OH MY”! =-.

  11. Seriously, you just changed my life.
    I had been struggling with the WordPress 3.2.1 upgrade and you fixed it for me with this post.

  12. I am actually a tech junkie but usually my host takes care of the super techie stuff for me. I can install WP, FTP and such with no problems but this sounds complicated.

  13. Jackie,

    I wrote this when my main hosting was Godaddy. Actually it isn’t hard at all. It takes about 10 minutes or less for the whole process as outlined in the post. I’ve since switched to Bluehost and it isn’t even necessary. Everything there is up to date, and if there is a newer version, they take care of the upgrades. (Yes, I have a affiliate link for Bluehost, I’ve got one for GoDaddy also, but no one will ever see it).

    Dave

  14. I found this article extremely helpful and it helped me figure out how to update my old wordpress blog.
    It is not very complicated but at first read I was intimidated by all the technical lingo.

    Thank you again!!

    Mariann

  15. Mariann,

    Yeah, MYSQL is kind of hard to describe as “the thingy down at the bottom”

    Dave

Trackbacks

  1. […] unknown article is brought to you using rss feeds.Here is a great article on the latest real estate buying and selling news.Is your blog showing its age. Was it created with mysql 4.0 or earlier? Then you know what it is like to click and wait and click and wait to do anything. Follow these simple none destructive steps and you can turbo charge your blog and … […]

  2. Real Estate - Information on Real Estate » Restore your WordPress Database From Mysql 4 to Mysql 5 says:

    […] your WordPress Database From Mysql 4 to Mysql 5 unknown wrote an interesting post today onHere’s a quick excerptIs your blog showing its age. Was it […]

  3. […] unknown wrote an interesting post today onHere’s a quick excerpt […]

  4. […] you may want to subscribe to my RSS feed. Thanks for visiting!In January of 2008 I wrote the post: Restore your WordPress Database From Mysql 4 to Mysql 5 Recently I had the need to update a blog for a friend trying to come out of the dark ages and […]

  5. […] The source of my happiness is here. […]