MySQL Forums
Forum List  »  MySQL Workbench

Synchronizing database structure and/or data
Posted by: Brice B
Date: October 20, 2008 09:03AM

Hello,

I'm looking for a fully automated way to synchronize just the structure and just the data of two databases (preferably FOSS).

The script I've used previously works something like this:
mysqldump dev_db --no-data > dev_structure.sql
mysqldump live_db --insert-ignore --no-create-db > live_data.sql
mysql -Dlive_db < dev_structure.sql
mysql -Dlive_db < live_data.sql
(the actual query has more options but I think it's irrelevant to post here.)
When a column is removed from the dev database and this script is run, it fails when it tries to insert values into the removed column. INSERT IGNORE doesn't ignore this that error.

I've asked the #mysql IRC channel for help. I was asked to look at Maatkit (http://www.maatkit.org/). I haven't figured out a way to fix my problem with it yet.

While searching the forums I've come across MySQL Workbench, SQLyog, ModelRight, http://www.artfulsoftware.com/infotree/queries.php?&bw=1276#624
The first three are GUI tools, however I need a way to synchronize the databases in an automated way.


== Background ==
We're using a development, test and live environment. Each environment has its own version of the database.
In the development version we make changes to the database structure. When it works, the same changes are made to the test db and eventually to the live db.

While testing we also import the data from the live database to work with 'real' data.

This means the structure goes this way:
Dev => Test => Live

And the data goes the other way:
Live => Test => Dev


Does anybody have suggestions?

Thanks in advance.

Options: ReplyQuote


Subject
Views
Written By
Posted
Synchronizing database structure and/or data
4522
October 20, 2008 09:03AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.