Tuesday, December 05, 2006

Upgrading MySQL 3.23 to 5.0

I recently had to upgrade a crufty old MySQL database on one of our Solaris machines (rainier) from 3.23 to 5.0. Here's the process I came up with. This is all in the MySQL documentation, but you have to hunt here and there for it, and what is outlined below allows you to do, or at least, test the upgrade while the original server is still running.

Preparation

Downloaded and installed latest 4.0, 4.1, and 5.0 into a directory.

Copied data directory into /usr/local/mysql-data.

Upgrade from 3.23 to 4.0

Started 4.0 pointing at this data directory, on port 3307.

Edit /usr/local/mysql-data/my.cnf to use port 3307 and socket file /tmp/mysql2.sock

cd mysql-4.0
export PATH=$PWD/bin:$PATH
mysqld_safe --defaults-file=/usr/local/mysql-data/my.cnf --user=emysql --datadir=/usr/local/mysql-data --basedir=$PWD --pid-file=/usr/local/mysql-data/rainier.pid

Check the databases:

mysqlcheck --all-databases -u root -p -h rainier -P 3307

mysql_fix_privilege_tables --user=root --socket=/tmp/mysql2.sock --password=xxxxxx

Lots of warnings and errors, but supposedly this is okay.

Didn't need to upgrade ISAM to MyISAM storage engine.

mysqladmin -u root -P 3307 -p -h rainier shutdown

Upgrading from 4.0 to 4.1.

cd ../mysql-4.1

export PATH=$PWD/bin:$PATH
mysqld_safe --defaults-file=/usr/local/mysql-data/my.cnf --user=emysql --datadir=/usr/local/mysql-data --basedir=$PWD --pid-file=/usr/local/mysql-data/rainier.pid

Check the databases:

mysqlcheck --all-databases -u root -p -h rainier -P 3307

mysql_fix_privilege_tables --user=root --socket=/tmp/mysql2.sock --password=xxxxxx --basedir=$PWD

mysqladmin -u root -P 3307 -p -h rainier shutdown

Upgrading from 4.1 to 5.0


cd ../mysql-5.0

export PATH=$PWD/bin:$PATH
mysqld_safe --defaults-file=/usr/local/mysql-data/my.cnf --user=emysql --datadir=/usr/local/mysql-data --basedir=$PWD --pid-file=/usr/local/mysql-data/rainier.pid

mysql_upgrade didn't seem to work, but I think that's because I had run it in an earlier attempt to upgrade from 3.23 to 5.0. So I did the individual steps:
mysql_fix_privilege_tables --user=root --socket=/tmp/mysql2.sock --password=xxxxxxxxx --basedir=$PWD
mysqlcheck --check-upgrade --all-databases --auto-repair -u root -p -h rainier -P 3307

I ran upgrade again, anyways, this time with the force option
mysql_upgrade -p -S /tmp/mysql2.sock --datadir=/usr/local/mysql-data --basedir=$PWD -u root --force


Additional notes:

Setting up the mysql init script. I set the datadir and the basedir, and then I added a --defaults-extra-file=$datadir/my.cnf to the line that invokes mysqld_safe.

2 comments:

Chad said...

I owe you a beer. Thanks a bunch for posting this. I have been pulling my hair out trying to migrate an old bugzilla app using mysql 3.23 to the latest versions. But that's what happens when you inherit an unmanaged network.

Thanks again!

michael_krigel said...

Thankx a lot Marcus!!! Chad you are not alone with such networks and software :-)
Best regard you guys, Michael