Migrating a MySQL 3.23 database to 5.0

I'm trying to move a old server and database into the modern world and that involves migrating a database from MySQL 3.23 to MySQL 5.0. The official MySQL line is to migrate from one version to the next and not try skipping one - but that's not very practical in my situation and as it turns out is not strictly necessary.

I took some of my steps from this post by Paul Williams (for which I am very grateful!). I didn't need to do everything he did but did need to figure out a critical additional step. Note that your mileage may very much vary - my tables are pretty basic. Here are the steps that worked for me:

Migrating FROM MySQL 3.23.54 on Red Hat Linux 7.3 (Valhalla)
Migrating TO MySQL 5.0.75 on Ubuntu 9.04 (Jaunty)
All my tables were in MyISAM format - if they were not I would have to convert them - look at the older MySQL docs for info.

On the OLD system:
1: Dump out the tables

$ mysqldump --add-drop-table --add-locks --all
        --quick --lock-tables mydatabase > mydatabase.sql

2: Convert from Latin1 character encoding to UTF8

$ iconv -f latin1 -t utf8 < mydatabase.sql > mydatabase_utf8.sql

3: Update auto_increment SQL definitions
Williams shows how to update these with this line

$ sed -e "/auto_increment/ s/DEFAULT '0'//" database_utf8.sql > mydatabase_utf8_filtered.sql

In my case this resulted in no changes to the SQL, so it was unnecessary but harmless.

4: Transfer the file to the new system

On the NEW system:

5: Create a short file of SQL header lines with this content:

set names utf8;
drop database if exists mydatabase;
create database mydatabase_inv character set utf8;
use chem_mydatabase;

Brian Williams suggests doing this rather than adding the lines to the top of your SQL file. I put mine into a file called 'sql_headers'.

6: Remove the leading comments on the SQL file
Williams did not include this step but for me it was essential. MySQL barfed on the first few comment lines of my SQL dump so I had to remove these. Basically I stripped everything down to the first real SQL statement. Other comments in the file were not a problem. Here is what I cut out - my hunch is that the long line of dashes is the culprit:

-- MySQL dump 8.22
--
-- Host: localhost    Database: mydatabase
---------------------------------------------------------
-- Server version 3.23.54

--
-- Table structure for table 'table1'
--

7: Load the SQL into your database

cat sql_headers mydatabase_utf8.sql | mysql -u root -p

8: Done! (At least for me...)
Open up your mysql client and poke around to see if everything is there.

... now onto recreating an old Perl CGI app on the new system ... fingers crossed ...