I want to synchronize the table structure of two databases and both databases are not in production. Unfortunately, I've only seen SQLyog (a commercial software) to be able to offer this feature. Mk-table-sync is not able to do this as it is only concerned with synchronizing the data of two databases.
I wonder how hard it is to synchronize two databases, assuming that it is only a one-way synchronization. Database A will have the updated table structures and database B needs those 'updated schema sql' to sync itself with database A.
So, if database A has new tables, then the output would be some create table sqls.
If database A has some updated table schemas (eg. new columns added, column type modified), then the output would be some alter table sqls.
If database A has new indexes, then the output would be some alter table sqls again.
If database A removed some tables, then the output would be some drop tables sqls.
Oh, and I thinking of using a mysqldump with no data option as a starting point to compare the database structures.
Following this logic, am I missing anything else to synchronize two databases?
Subscribe to:
Post Comments (Atom)
6 comments:
Some sort of provision for changing column types, e.g., an integer to an enum. My schema alters between releases are often blocks of ALTERS surrounded by updates, migrating old int flags to enums.
Hi there!
I just read a post talking about the very same thing you are looking for,
check in :
http://nhmysql.blogspot.com/
http://www.mysqldiff.org/ may be of interest.
This type of tool has been on the Maatkit todo list for a long time. One of these days a client will need it and I'll be motivated.
PS brace yourself. This will not be a simple tool to write, unless you discover something really elegant and simple -- a higher order programming technique. Maatkit has a great deal of the scaffolding already, so you could borrow components of it (TableParser.pm). IMO this will be the type of thing that seems easy with some rules -- but there will be endless exceptions that will turn the code into hell. Finding that higher-order insight will likely be elusive. If you find it, feel free to contribute it to Maatkit :)
I'll keep you posted. I might use your module to create this script.
"MySQL Workbench includes Schema Synchronization and Comparison utilities" - http://www.mysql.com/products/workbench/
I've used it for this and it does a pretty good job in generating the DDL needed to make one schema look like another.
Post a Comment