Automatic MySQL migration

August 6, 2009 8:06 AM

How can I automate the migration of an existing MySQL database to a newer schema?

I have two MySQL databases which started out with a common schema, but while one has evolved the other has remained frozen. The changes are all either modifications to a column's datatypes, or the adding or dropping of tables and columns -- no columns are renamed, and it's purely one-way so no merging is required.

I can get the schema for both databases with mysqldump -d, and a diff shows up clearly the changes that need to be made to the first DB. It's just that there are a lot of them, and writing the script to ALTER all the tables by hand will be a chore and error-prone.

Is there some of way of automating this? Ideally some tool that allows me to upload both schemas and spits out a script.

posted by fightorflight to computers & internet (5 comments total)

MySQLdiff looks like it might help you.
posted by le morte de bea arthur at 8:32 AM on August 6

Write a little perl or python script that makes one request from the old database and does an insert record on the new one?

Painfully inefficient, but you can go for lunch while it runs.
posted by rokusan at 8:42 AM on August 6

A couple of questions :

You want identical schemas, do you want identical data?

Why not just do a dump & restore of the 'evolved' DB to the 'un-evolved' DB?

If you make all these schema changes manually (or even programatically), you're going to have all sorts of empty tables, empty columns, etc. Is that what you want?
posted by swngnmonk at 8:58 AM on August 6

I want identical schemas but retaining the separate data. I can handle the empty tables and columns -- I have a job to fill them all with defaults.
posted by fightorflight at 9:21 AM on August 6

MySQLdiff does look good, thanks! I'm downloading it just now.
posted by fightorflight at 9:24 AM on August 6