SEO

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.

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
>