You may want to try replication. Setup your replication server as
5.0. That server gives you a chance to play to get things right
without affecting the master server. You'll still need to do a dump to
get the slave up to speed. Once you get everything right, you can
switch over and the slave becomes the master.

Very simple in theory, a bit more complicated in practice.

Brent Baisley
Systems Architect


On Apr 23, 2008, at 2:28 PM, Paul Choi wrote:

> Does anyone have experience with upgrading large databases (~500GB
> each)
> from MySQL 4.1 to 5.0? The tables are in InnoDB format. We are using
> the
> Community version.
>
> I've read that it's recommended that you use mysqldump and then
> restore,
> but this is not possible for us, as we cannot have our databases down
> for long, nor can we have our tables locked while doing dump.
>
> I've tried doing the following steps:
> ibbackup --restore
> copy over mysql table dirs.
> set default char set to latin1 (or will default to utf8) in my.cnf
> because that's the original char set in 4.1
> Upgrade only mysql database (user and privilege tables)
> mysqlcheck --check-upgrade --auto-repair mysql
> mysql_fix_privilege_tables
>
> I've written a script to compare data between the original 4.1 and the
> new 5.0. Looks like certain rows have different numerical data... so
> this is not good.
>
> I didn't want to do mysql_upgrade on all databases in this instance of
> MySQL because that resulted in 2 things happening:
> 1) Don't set default char set to latin1. Run mysql_upgrade
> Some rows had data truncated in certain columns.
> 2) Set default char set to latin1. Run mysql_upgrade
> Copies to TMP table. Takes forever... This is unacceptable for
> us.
>
> What is the recommended way to upgrade from 4.1 to 5.0? Or are we
> stuck
> using 4.1 forever?
>
> -Paul Choi
> Plaxo, Inc.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=brenttech@gmail.com
>