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 --all2: Convert from Latin1 character encoding to UTF8
--quick --lock-tables mydatabase > mydatabase.sql
$ iconv -f latin1 -t utf8 < mydatabase.sql > mydatabase_utf8.sql3: 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.sqlIn 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;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'.
drop database if exists mydatabase;
create database mydatabase_inv character set utf8;
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.227: Load the SQL into your database
-- Host: localhost Database: mydatabase
-- Server version 3.23.54
-- Table structure for table 'table1'
cat sql_headers mydatabase_utf8.sql | mysql -u root -p8: 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 ...