Solved

bidirectional synchronisation of databases

Posted on 2009-07-15
16
263 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:Tony McCreath
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 
LVL 23

Expert Comment

by:Tony McCreath
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:Tony McCreath
Tony McCreath 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:Tony McCreath
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
xampp tool 12 56
How many transactions can mysql handle? 3 44
mysql database, schema and table creation 13 60
MYSQL responding very slow 3 28
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

821 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