Solved

bidirectional synchronisation of databases

Posted on 2009-07-15
16
255 Views
Last Modified: 2012-05-07
Hi

This is my database set up:

I have a development machine on which i may change the database structure and add some data which is normally constant data (e.g. tables that hold data that is typically used to populate drop down lists and the link such as Title [mr, mrs, miss]). It also has some test data that i play around with. I have a production machine that uses the database structure and constant data from the development machine and gets its own 'real data' from people who use my application.

I would like to be able to synchronise the database structure and constant data from the development machine to the production machine but NOT copy across my 'play' data. Sometimes i might want to copy some 'real' data from the production machine to the development database.

What technology do i need to achieve this? I didn't think replication was suitable because i don't really have a master slave relationship. I have seen something called syncman i was going to look into.

Any advice appreciated

thanks
0
Comment
Question by:andieje
  • 7
  • 5
  • 4
16 Comments
 
LVL 23

Expert Comment

by:Tiggerito
ID: 24868132
Do you have a clear definition of which tables go in which direction?

You may be able to set-up replication from devel->production for the subset of tables which will be mastered by devel.

Then, as needed, you could use backup and restore to copy table data from production to devel.
0
 
LVL 22

Accepted Solution

by:
NovaDenizen earned 250 total points
ID: 24870713
You need to settle on a "formal" configuration management process that meshes well with your workflow and provides satisfactory service for the production machine.  There's no magic "make everything sync up" command.

When you change the database structure of your development machine, you should be careful to save the commands that you ran.  Later you run the exact same scripts to update the production database.  You can't just willy-nilly modify your development database.  

0
 

Author Comment

by:andieje
ID: 24874155
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
0
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 24874348
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.
0
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 24874382
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...
0
 

Author Comment

by:andieje
ID: 24900046
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.
0
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 24904221
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.
0
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 24904273
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 23

Expert Comment

by:Tiggerito
ID: 24904427
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.
0
 

Author Comment

by:andieje
ID: 24942829
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 :)
0
 
LVL 23

Assisted Solution

by:Tiggerito
Tiggerito earned 250 total points
ID: 24944761
Ours is in-house and proprietary. You would have to develop your own or find one...

The MySql Workbench can reverse and forward  engineer a database. It looks like it is possible to also synchronise databases with the schema models it makes.

You could reverse engineer from your official database then sync to all the other databases.

The bonus is you get a nice displayable model of your database.

http://dev.mysql.com/downloads/workbench/5.1.html

0
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 24951874
> 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.
0
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 24951991
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.
0
 
LVL 23

Expert Comment

by:Tiggerito
ID: 24952564
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
0
 

Author Comment

by:andieje
ID: 25038724
Thanks for both your help
0
 

Author Closing Comment

by:andieje
ID: 31603849
thanks
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now