Page 1 of 1

Database Migration - Same platform, different schemas

Posted: Mon Mar 08, 2010 10:03 am
by Kurby
I am trying to find the easiest and most efficient way to transform all the data in one database to a redesigned schema. My attempt so far has been rather frustrating. I have developed the new schema and put it on a new server with a copy of the data from the old schema. Then I created a SQL script for each table that will SELECT from the old one and transform it to its new format before inserting. The tables are quite large, and some of the transformations require cumbersome joins, so the script takes a LONG time to run (3-5 hours). Normally I just can't get through the script without it erroring out somewhere (duplicate key, foreign key reference failed). When it does, none of the data for the table it failed on gets imported. So I have to attempt a fix to the problem but don't know the result until it fails again in a few hours.

There must be a better way. I have looked at MySql Migration Toolkit but it doesn't seem to be supported anymore. Was this replaced by something better? Can workbench do it somehow? Am I just stuck with my current method?

Thanks for the help.

Re: Database Migration - Same platform, different schemas

Posted: Mon Mar 08, 2010 10:24 am
by Kurby
I have found Migration Toolkit, it wasn't installing properly before. I will try this. In the meantime if anyone else has more info they would like to share about migration, I am all ears!

Re: Database Migration - Same platform, different schemas

Posted: Mon Mar 08, 2010 11:11 am
by Weirdan
When we faced this problem where I work, we developed dedicated specialized php script to do the migration for us.

Can't you rethink your migration script and process data in batches? For example we had data about pupils and migrated it school-by-school. When one particular school couldn't be migrated properly the script would mark it for later investigation and move on to the next school.

Re: Database Migration - Same platform, different schemas

Posted: Mon Mar 08, 2010 11:43 am
by Kurby
Thats a good idea. Currently my "migration" script is a SQL file. Maybe I will have to create something in PHP and segment problem records.

Thanks for the suggestion.