Database Migration - Same platform, different schemas

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Kurby
Forum Commoner
Posts: 63
Joined: Tue Feb 23, 2010 10:51 am

Database Migration - Same platform, different schemas

Post 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.
Kurby
Forum Commoner
Posts: 63
Joined: Tue Feb 23, 2010 10:51 am

Re: Database Migration - Same platform, different schemas

Post 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!
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Database Migration - Same platform, different schemas

Post 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.
Kurby
Forum Commoner
Posts: 63
Joined: Tue Feb 23, 2010 10:51 am

Re: Database Migration - Same platform, different schemas

Post 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.
Post Reply