Should I upgrade to Mysql 5?

[wzhao6898]

Hi there,

I'm trying to figure out if it's going painless to upgrade our oooold
mysql 3.23 to the latest MySQL 4 or 5.
Any suggestions?

Thanks in advance!

David

[Bill Karwin]

wz*******@gmail.com wrote:

Hi there,

I'm trying to figure out if it's going painless to upgrade our oooold
mysql 3.23 to the latest MySQL 4 or 5.
Any suggestions?

It would be very advisable to back up your database(s) before installing
any new version of the software, and then restore the databases after
installing the software. I wouldn't rely on the database file format
remaining seamlessly compatible between such a wide range of software
versions. Restoring a backup basically recreates the database, so it
would be sure to recreate it in a format compatible with the new
software. Plus it's always good to have a backup.

I would recommend trying some tests on a separate machine, install MySQl
4.1 or 5.0 on another machine (even a laptop) and restore your backup
there. Run a few tests to make sure the data looks like it is
compatible, before installing the new software on your live server. The
reason I suggest using a different machine is that it is a complex
process to run two versions of MySQL on one host.

One particularly significant issue is that MySQL 4.1 and 5.0 use a
different algorithm for encoding passwords. You'll probably want to
start the newer servers with the --old-passwords option, at least
temporarily. If you don't, none of the existing passwords will work,
and you'll have to use GRANT to assign privileges to the databases.

See http://dev.mysql.com/doc/refman/4.1/...d-hashing.html for a
full explanation of the difference in the password algorithm.

There are also changes to the handling of character sets that could
affect your database.

You can also read the change history for 4.0, 4.1, and 5.0 at these
locations:

http://dev.mysql.com/doc/refman/4.1/en/news-4-0-x.html
http://dev.mysql.com/doc/refman/4.1/en/news-4-1-x.html
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-x.html

Regards,
Bill K.

[Markus Popp]

Hi,

take also care of the structures of the tables in the mysql database (where
all the privileges are set), they have changed significantly between MySQL
3.23 and MySQL 5 (also to MySQL 4.x), because there are a lot of new
privileges that were added between these versions.

If you install the new MySQL version on a testing machine like Bill
suggested (I'd also suggest that), take these tables as reference.

Good luck!

Markus

[Good Man]

"Markus Popp" <mf*@gmx.li> wrote in
news:43**********************@newsreader02.highway .telekom.at:

Hi,

take also care of the structures of the tables in the mysql database
(where all the privileges are set), they have changed significantly
between MySQL 3.23 and MySQL 5 (also to MySQL 4.x), because there are
a lot of new privileges that were added between these versions.

If you install the new MySQL version on a testing machine like Bill
suggested (I'd also suggest that), take these tables as reference.

Good luck!

Also note that MySQL documentation suggests that you upgrade without
skipping versions:

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

[Markus Popp]

I would do it this way:

* make a dump of all your data except the mysql database
* make a separate dump of the mysql database
* uninstall MySQL 3.23
* 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 MySQL 3.23 to make sure that everythink works.

Markus