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.
