Database Migration - Same platform, different schemas
Posted: Mon Mar 08, 2010 10:03 am
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.
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.