Upgrading MySQL from 3.23 to 4.x+, How the heck do I do it? Options

[John Pozadzides]

When I say I'm really frustrated at the moment, that's an understatement...

I've been running Wordpress on my personal blogs for quite some time now. I have my own dedicated Linux server and I use Plesk 8.x to manage it.

Today they just announced the general release of the latest Wordpress 2.1, which I was very excited about because it is a major update. So, after spending quite a bit of time backing up my data and stuff I proceeded to install the update - only to then learn that the system would NOT update because it now requires MySQL version 4.x or higher!

AARRggh! They didn't bother stating that requirement change in the upgrade documentation and that caused a bunch of people, including myself, a huge panic!

Back to the story now (thanks for letting me vent), my server is running MySQL 3.23.58-16.FC2.1. I have 16 databases (all Wordpress databases except for one Invision Power Board DB). Now I need to have MySQL 4 or 5 on the machine in order to do the Wordpress upgrade.

The problem is, I haven't got a clue how to perform this upgrade!?! I've googled and searched MySQL.com but there is just nothing clear.

• Can anyone give me an idea of how to accomplish this? Please use small words... as I am NOT a Linux guy.
• Once MySQL has been updated, do the databases have to be re-built or something like that?
• Will later versions of MySQL offer a performance increase or decrease?
• Would I be correct to assume that if I have to make an upgrade at this point it would be wisest to go to ver. 5?
• How can I protect myself from catastrophic failure? These databases are for my personal blog as well as friends and family blogs and a non-profit organization. If I screw this up it will be difficult to recover from...

I've approached my web host, but they won't perform just an upgrade since I have a self-managed server. They only offered to wipe the entire box and then put fresh versions of everything on it. But then I'd be in even worse condition I think...

I would appreciate all the wisdom and guidance anyone can provide with regards to this issue. It's pretty depressing to be faced with something that seems so complex even though you know you've got to do it.

Thanks,

John

[Liam Quinn]

The MySQL manual discusses upgrading here:

http://dev.mysql.com/doc/refman/4.1/en/upgrade.html

It's not clear to me that you really need to upgrade to the latest version of Wordpress. I would reconsider whether the new Wordpress features are worth the effort and risk of upgrading.

If you do choose to upgrade, one way to minimize the risk would be to purchase a new dedicated server (which should come with MySQL 4.x or higher) and migrate over to that server when you have it stable.

[John Pozadzides]

It's not clear to me that you really need to upgrade to the latest version of Wordpress. I would reconsider whether the new Wordpress features are worth the effort and risk of upgrading.

Well, I held off for a couple of weeks, and during that time I went through every option I could think of, find, and learn to see if I'd be able to just stick with my existing server.

• I cranked up my Cache settings in Wordpress
• I removed functions from my site which I liked but which caused additional calls to the DB server.
• I turned on persistent connections to the server.
• I optimized my PHP files as much as possible, removed images, and javascripts.

After all of this work, the site now validates, and it does run quicker, but it's still a little slow occasionally. On top of all that, I'm now noticing that plugin developers have moved on to support the Wordpress 2.1 branch and they are abandoning support for previous versions.

Combine all of this with the facts that a) they promise Wordpress 2.1 runs faster b) I can't understand the MySQL upgrade instructions c) I can't understand how to install a PHP or MySQL accelerator...

If you do choose to upgrade, one way to minimize the risk would be to purchase a new dedicated server (which should come with MySQL 4.x or higher) and migrate over to that server when you have it stable.

So, after all of that I took your advice and ordered a new server yesterday. And I supersized it. I got the $199/mo one with the following specs:

• Dual Core AMD Athlon 64 X2 4200+
• 2 GB DDR RAM
• 250 GB (2X SATA drives in RAID 1 configuration)
• 250 GB FTP backup
• 3,000 GB monthly transfer volume

I know it was overkill, but I figured this would prevent me from needing an upgrade for another few years (my last server lasted a couple of years).

The new server came with MySQL 4.1.20-1.FC4.1, so my question now is... do I need to do anything special with all of my previous databases which were using 3.23 to get them to work in 4.1?

It seems that I read conflicting information that they should work, but also that some kind of processing might be required. All I can really do is go for the trial and error method unless someone has suggestions.

Thanks again,

John