Skip to content

Instantly share code, notes, and snippets.

@dakira
Forked from atufkas/migrate_mysq4to5.sh
Last active August 29, 2015 14:23
Show Gist options
  • Save dakira/a948806b97988be89175 to your computer and use it in GitHub Desktop.
Save dakira/a948806b97988be89175 to your computer and use it in GitHub Desktop.
#
# 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment