Wednesday, October 26, 2005

Upgrading from older versions to MySQL 5.0

I sometimes read the posts in the MySQL Newsgroup (mailing.database.mysql) and take a look if there are any questions where I can help other MySQL users. That's a good think for many purposes: I can help people, I can learn new things for myself and last but not least - there are often questions that give me interesting material for this weblog. Such as this:

There was a question, how to update to the latest MySQL 5.0 version - not only from 4.1 (which is described perfectly in the MySQL manual), but also from older versions like 3.23. Here's how I would do it:

* make a dump of all your data except the mysql database

* make a separate dump of the mysql database

* uninstall this old MySQL version

* install the latest MySQL version from scratch

* create a separate database for the previously dumped mysql database (e.g.
mysql_old) and restore this dump

* sync the old mysql database with the new one (this might require some
manual work, because the structures will be slightly different)

* when the new mysql database is OK, restore the dump of the data

If you have a chance, try to install the new version on a separate machine,
before you uninstall the old MySQL version to make sure that everythink works.

5 comments:

Frank said...

Hello Markus,
Thanks for the post. Just wanted to mention that I recently upgraded to MySQL 5 . My experiences are available:
here
and
here

Thanks

Markus Popp said...

Thanks for the links to your articles ;-).

Anonymous said...

dear sir,
how can i upgrading to mysql 5.1..
from older version in redhat as enterprise 4?
i always get error crashing with mysql older version.
reply please!

best regards,
mapays indonesia

Phuff34 said...

would just like to say the differences from one MySQL version to the next is painful. The process is not great. MySQL is great for small web projects, for anything else go with Postgre if you *really* want open source or pick MS SQL Server. If you have terabytes upon terabytes of data with millions of transactions go with Oracle.

You get what you pay for.

Markus Popp said...

Phuff34, do you think that if MySQL wasn't great for very large applications, Google, Yahoo, Wikipedia etc. would be using it?