Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #
- # 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
Add Comment
Please, Sign In to add comment