A collection of computer systems and programming tips that you may find useful.
Brought to you by Craic Computing LLC, a bioinformatics consulting company.

Tuesday, June 16, 2009

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 ...



Thomas said...

You're a time saver.

I did what you wrote in guide and it WORKED !

Thank you

Best Regards,

mohrt said...

In this file, you use three different database names:

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

I take it those are typos?

Benjamin said...

Thank you ;)

Archive of Tips