Had a need to upgrade MySQL 4.0.27 on one of the productions servers I manage to MySQL 5.0. If anyone has any trouble doing that, I have written a small tutorial on how to accomplish this task without many problems.

Note!
A small reminder. Since we’re going to use the ports system to install/upgrade our new MySQL Server daemon, i would suggest you have your ports tree up to date, and specify your MAKE options in /etc/make.conf (can come in handy when you update your port though portupgrade).

Step 1 - Backup your database
Just in case you ran into any problem, I would strongly suggest you to make a backup so you can roll back any time to mysql40-server.

# cd /var/db

# tar cvfz mysql.tar.gz mysql

You will end up with an archive of the whole MySQL database folder.

Step 2 - Stop the service

# /usr/local/etc/rc.d/mysql-server.sh stop

Step 3 - Remove the old MySQL Server.

# pkg_delete mysql-server-4.0.27

# pkg_delete mysql-client-4.0.27

The error you might get here is only the one telling you that some ports require mysql40-client in order to function. These ports might include phpMyAdmin, php4-mysql & php5-mysql, etc. Act accordingly, either remove the ports, or use pkg_delete -f to force removal of mysql40-server.

Step 4 - Install the new MySQL Server.

# cd /usr/ports/databases/mysql50-server && make install clean

This step will take some time, depending on your internet connection speed and your server’s hardware in general.

Step 5 - Start MySQL 5.0 and run the upgrade script

# /usr/local/etc/rc.d/mysql-server.sh start

# /usr/local/bin/mysql_upgrade -u root -p --datadir=/var/db/mysql

Enter your root password on prompt and enjoy the upgrade script output.

If everything went nice and smooth, you should be now running a properply upgraded mysql server 5.0.

As a final note, ports like php4-mysql, php5-mysql and others that have been build according to your previous MySQL version have to be re-installed.