#
# MIGRATE MySQL4 DATABASES TO MySQL5 - Steps for dumping and converting
#
# Uses mysqldump and patches output to be compatible with MySQL 5.5+ (? - no sure
# at which specific release of MySQL 5 the old style syntax support ended).
#
# Conversion is most likely incomplete. It does some essential converting where
# I regularly experienced problems during migration.
#
# Use on own risk, always try with test databases first. No warranty at all!
#
# Feel free to ask, improve, contribute!
#
# (c) 2013 Matthias Lienau

# 1. Use mysqldump do dump schema of the MySQL 4 database you want to migrate.
# I recommend doing this using two files: One for the schema and one for the data dump.
# Assumes your existing old database name is "mydb".
# (Never forget to provide --username=[user] and --password=[pass])
$ mysqldump [--username=user --password=pass] -d mydb > mydb-schema.sql
$ mysqldump [--username=user --password=pass] -t mydb > mydb-data.sql

# 3. Replace old style comments ("--") with new style comments ("#") from both files
$ sed -r -i -e 's/^--(.*)$/#\1/' mydb-schema.sql
$ sed -r -i -e 's/^--(.*)$/#\1/' mydb-data.sql

# 2. Replace old storage type declaration keyword from "TYPE" to "ENGINE" (e.g. "TYPE=MyISAM" => "ENGINE=MyISAM")
$ sed -i -e 's/) TYPE=/) ENGINE=/' mydb-schema.sql

# 4. Replace simplified timestamp field definition with full 5.x syntax
# Maybe here are more cases with other on update values - didn't check this!
$ sed -i -e 's/timestamp(14) NOT NULL,$/timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,/' mydb-schema.sql

# 5. Go and import the modified schema and data files to your new and fresh MySQL 5 database
# Assumes your new database is created and named "mynewdb".
# (Again don't forget to provide --username/--password)
$ mysql mynewdb < mydb-schema.sql
$ mysql mynewdb < mydb-data.sql