Home / server / Questions / 77722
Accepted
Randy L
Asked: 2009-10-24 10:05:29 +0800 CST

Upgrading MySQL 3.23 to MySQL 5

  • 772

Has anyone here tried taking a crusty old MySQL 3.23 database and upgrading it all the way to MySQL 5 or 5.1? I've found a little bit of info on this but not a whole lot. What I've read so far is that it's best to not skip a major version number when upgrading. So I would have to go from 3.23 -> 4.xx -> 5.

If anyone out there has direct experience and some advice, I'd love to hear from you.

mysql

2 Answers

  1. Best Answer

    Tables can be easily migrated. Not a big deal. But the queries are the problem. A lot of changed since 3.23 to 5.x and if you have sqls more complex then select from from bar where 1>2 then they will, most likely .produce different results on 5.x . Operator precedence, embedded functions, et all.
    I recommend you run on some non-productions servers the same databases on mysql 3 and 5.x , run all your selects and verify if you get identical results then go for update/insert/delete and whatever you have and run the selects again.
    I actually did a mysql 3.23 migration to mysql 5.0.45 for some small dbs , with very simple schema and I had to adjust a lot of queries so they would work on the the newer version. And another thing - you can't simply copy my.cnf from to 3 to 5 and expect to work - a lot of things changed in there too. I got burned when moving from 5.0.10-something to 5.0.45 when some default values that were not in the configuration file were different in the higher version.
    Aaand the last thing , if you use mysql replication, you can't have mysql 3.x slaves and mysql 4.x (not sure about 5) masters. The other way around is fine.

    In my shop we like to think the database servers are "holy ground" and should be treated as such :)

    • 2

  2. Yes, the procedure is straight forward, just beware of old-passwords! 3.x and 5.x use a different password() function. So if you have tables that used the password(), you'll have to be aware of that. Try to use your platforms package install or a binary. Compiling is not for new users.

    Make sure to back up all your databases with mysqldump AND make a copy of your /etc/my.cnf prior to the upgrade.

    Also, make sure to read the documentation for the version of mysql you're upgrading to.

    Finally, run mysql_upgrade when you're done.

    • 0