bidirectional synchronisation of databases

Hi

Thank you for your answers but I am not any clearer about how I would achieve what I want to do. I am not a database person so i would appreciate more specific advice. If I have not provided enough information, please ask me for the details you need

NovaDenzien, why can't i modify the development database and then synchronise with the production database to propagate the changes to the production machine. Your use of the term 'willy nilly' makes this sound inherently bad but I would like to understand why

There is no magic propogation stick you can wave at the systems to make the changes happen.  You make changes X, Y, and Z to your development database, then later you have to make the exact same changes X, Y, and Z to the production database.  

I say "willy nilly" because sometimes developers get in an informal mindset where they make multiple small tweaks to their data models, then later forget the exact series of changes they made.  So when it comes time to update the production server they just make changes that approximate the state of the development server, which leads to inconsistencies.

The best way to do it is to record (through a SQL script or notes) the exact series of changes you make to the development database as you make them.  Then when it comes time to update the production server, follow the exact same procedure.

But I infer that you've already passed that point.  The "SHOW CREATE TABLE" statement is very useful for determining the precise differences between your dev and prod table structures.  The best way to run it is like "SHOW CREATE TABLE TableName\G", since the \G disables the ascii-art grid that the mysql client uses by default.  "SHOW CREATE TABLE" gives you the exact command you would need to run in order to exactly recreate the table, and it shows all the options for all the fields you have.

Also, the ALTER TABLE family of commands will come in very handy for changing the production table.  And of course, make a full backup of your production server before you touch a single thing on it.

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
ALTER TABLE mytable CHANGE COLUMN ...
ALTER TABLE mytable ADD COLUMN...

I was rather hoping there was a magic change propagation stick i could wave.

I understand the value of keeping track of my create and alter table commands and then applying these scripts to the development server. However, i was hoping there was something that could keep track of all my changes for me and then apply these changes in one deft swish if its wand to the other database.

The database are currently in synch because i do already do as you advise. With some many tools around i thought it could be automated.

Since the changes can be embodied in a .sql script, an automation tool is not really needed.  You just need to run the same script.

In general, automation tools are created when a similar process needs to be repeated many times by many people over many different systems.  With database applications, typically there are only two or a couple more databases that need to be managed, and it generally isn't worth the effort to create an automation tool that would be significantly more capable or reliable than a sysadmin manually running a script.

Also, database table configurations are rather finicky beasts and there is not a theoretically good way to automatically diff two databases to generate a patch.  So all an automatic database update tool could do would be to essentially run a pre-prepared script.

It _would_ be useful if a database permanently logged all the data definition changes that were made to it.  Then the changes could be automatically extracted and duplicated at other sites.  I haven't used a wide variety of databases, so it's possible this feature is out there and I've just never heard of it.

I didn't realise this was more about syncing the structure of a database.

In our system we have a meta data file that defines the databases structure (XML). We also have a tool that creates SQL to convert a database so that it matches the meta data structure.

We find this very powerful when trying to keep many instances of a database in sync with the application.

Tiggerito, is that a bespoke tool or something you can buy?

Nova regarding thsi comment

It _would_ be useful if a database permanently logged all the data definition changes that were made to it.  Then the changes could be automatically extracted and duplicated at other sites.  I haven't used a wide variety of databases, so it's possible this feature is out there and I've just never heard of it.

That's exactly what i'm after :)

> That's exactly what i'm after :)

Unfortunately, that was just a pie-in-the-sky idea and not based on any actual database that I know about.  

This is the kind of thing that you have to do on your own, through a manual clerical process or an automatic tool that you create.

I believe it's theoretically impossible for an automatic diff-patch style solution to work.  Consider this scenario.

There are two servers, 'dev' and 'prod'.

initialization of dev:
    CREATE TABLE mytable ( `a`  INT );

Then you copy this table out to the production server.

Then on dev:
    ALTER TABLE table CHANGE COLUMN `a` `b` DOUBLE, ADD COLUMN `a` INT FIRST;

Now you want to automatically synchronize the dev and prod servers.  The synchronizer looks at the dev server and sees (`a` INT, `b` DOUBLE) and it looks at the prod server and sees (`a` INT).  So it makes the obvious change to the prod server:
    ALTER TABLE table ADD COLUMN `b` DOUBLE;

Which is, of course, the wrong thing.

I forgot the step after the copy where the dev and prod servers are populated with data which diverge, but I hope it's still clear.

The step we used separates definition/schema from the actual  data structure so it get around the issues related to diff'ing SQL.

Our system queries the current database structure and create sql that will convert it to conform with the current schemas.

Another possible solution is SQLyog. It used to have a diff tool, however I can't see it on its latest feature list!

http://www.webyog.com/en/sqlyog_feature_list.php

Thanks for both your help