I just upgraded our TLC development/staging/small-deployment server from MySQL 4.0 to 5.0.22. I'd never upgraded a MySQL server before (always just installed a fresh copy on a new box, or updated along with MacOSX) so I wanted to do some testing before making the plunge on a deployment server. We've got a bunch of databases on that box, running everything from weblogs.ucalgary.ca to some custom apps written here.
I did a quick RTFM , but the MySQL manual recommended not jumping right from 4.0 to 5.0 using the normal upgrade process. It said to go to 4.1 first, then to 5.0. So, I did some more Googling, and realized that a full mysqldump and restore would do the trick, without requiring the milk run through 4.1.
I installed a fresh copy of MySQL 5.0.x on my desktop box, did a full mysqldump from the server using:
mysqldump -u root -p --opt -Q --add-drop-table > mysqldump.sql
That gave me a 400+MB SQL file, handy for a backup, or for migrating between copies of MySQL. I copied the file to my desktop, and ran this:
mysql -u root -p < mysqldump.sql
mysqladmin -u root -p --flush-privileges
And, it Just Worked™ - so the data should safely migrate this way. I did some testing with apps - Drupal etc... and thinks behaved as expected. It takes longer to dump/restore than just updating the MySQL server, but it's safe. And all database users and privileges were intact, so everything should hook up just fine.
Then, I did the same on the server. I killed MySQL, which was still running version 4.0. I ran the installer (both MySQL 5.0 and the StartupItem) via SSH (sudo installer...) and it took care of setting stuff up. But, the old MySQL didn't shutdown cleanly, so I had to do the unthinkable - reboot the box. I know there's a cleaner, more "official" way to clean stuff up, but the box had been up for a couple months, so a reboot wasn't the end of the world.
When the server came back up, MySQL was running as expected. Then, it was a simple. First, I told my shell to use the new copy of MySQL, then imported the data:
alias mysql=/usr/local/mysql/bin/mysql
alias mysqladmin=/usr/local/mysql/bin/mysqladmin
mysql -u root -p < mysql.sql
mysqladmin -u root -p --flush-privileges
And all of our apps are up and running on a shiny new copy of MySQL. The previous version of MySQL is still on the server (but not running) with all data still safe in its own directory, so if something catastrophic has happened, reverting will be trivial.
Now, to upgrade my copy of Moodle to 1.6, which is what prompted the whole MySQL upgrade process in the first place...