Technical Exhaustion

First a caveat : the following steps worked well on a lightly used mySQL server in production (~5 queries per second average, heavily biased to select’s - spread over around 30 individual databases) but your experience may vary on more heavily loaded servers. This also uses the ports build and the portupgrade tool. mySQL 5 also brings changes to table structures, new features and changed features within the server. Ensure your applications or other database users are compatible with 5 before proceeding. Test accordingly!

Canonical steps :

- Use mysqldump or whatever other backup process you have in place to take a backup before you begin. Once the backup has been taken move it somewhere safe and mark it as being taken immediately before the upgrade from 4 to 5. The upgrade process from 4 to 5 will examine/alter the structure and elements of *all* your databases so this is a significant backup milestone to keep.

- We have to upgrade the client libraries first. As the mySQL 4 and mySQL 5 ports are completely seperate in the ports tree, we will use portupgrade to install 5, replacing 4 along the way. This is a different process than simply updating a port as you would normally.

cd /usr/ports
portupgrade -o databases/mysql51-client mysql-client

This should install the upgraded console and libraries. You can verify the version with :

# mysql --version
mysql Ver 14.14 Distrib 5.1.30, for portbld-freebsd6.2 (i386) using 5.0

A good idea at this point is to check your server is still happy - run some queries and poke it a little bit.

- The big one! We essentially do the same steps as the client upgrade. This will upgrade whilst the server continues to run. However, for some reason, the port install script stops mysql during the process - so this is the start of your, hopefully short, period of downtime.
cd /usr/ports
portupgrade -o databases/mysql51-server mysql-server

- If all has gone well you can now start your new mySQL 5 server.
# /usr/local/etc/rc.d/mysql-server start
The rc.conf options have not changed so this will start right up. Login and confirm the new servers version. Handily this also checks that you *can* still login - which is an excellent first step to pass!

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2966
Server version: 5.1.30 FreeBSD port: mysql-server-5.1.30

- This updates the mySQL table structure and various other housekeeping tasks to migrate your data from mySQL 4 to 5 format. The official definition is thus :

mysql_upgrade program that checks all tables for incompatibilities with the current version of MySQL Server and repairs them if necessary.

Strangely we do need to have the server running (with the old out of date formatted data) to perform this update - but mySQL seems quite happy to do so. Be warned this does change the data in your main mySQL database and it also parses and updates all your other databases where necessary. Hope you took that backup!
#mysql_upgrade -u root -p –datadir=/var/db/mysql
Obviously amend the datadir path if necessary - although the above is the FreeBSD default. This will grind away for potentially some time depending on the size of your data.

- That’s it, you’re all done! Now test, test and test. Check the data integrity and check your applications are still behaving as expected. You should also take the chance to update the client tools of other servers who may access this to version parity. It would also be worth rebuilding any ‘middleware’ ports you have that interface with mySQL (php5-mysql, p5-mysql etc).

Have fun and good luck!