how to synch DBs

kante

This is a common problem I have and I still didin't find a quick and painless solution:

I have two Databases (MySql) one on the production server one on the development server.

On production server happens that people update data in tables.
On development eserver happen that developers update DB structure (relations , fields and so on).

The problem is : every time the development is stable and i want to upgrade the DB
i 'm in trouble since i cannot simply dump and restore with new data.
But i have to do it kind of manual way ... and I don't like this at all...

Anyone can show me the light?
Many thanks

kante

A note:
Replication is not a solution for me since i cannot overwrite the development Db wi the updated data on production server . It may drop the structure changes made on development environment...

kante

UP... no ideas?

bradgrafelman

If others have no ideas like myself, it might be because we don't really understand what your goal is.

How can something automated sync DBs that can have arbitrarily varying schemata without sync'ing the schemata as well? Sounds like you're asking us how to invent artificial intelligence.

cretaceous

I agree with Kante that it would be good if a piece of software could do this and it doesn't need AI

What is neded is a tool that compares tables and their columns and when data is different or a column is missing there are comprehensive options on what to do

I have written little scripts to assist myself in similar tasks and I have a nice comparison script that show me differences on screen - but they are ad hoc..

There are commercial tools that provide what you want - google 'sql compare'

stackoverflow have a discussion here:
http://stackoverflow.com/questions/225772/compare-two-mysql-databases

kante

Many thanks... at least what I asked wasn't so "NOT understandable".
it 's clear there's not (until now) a free tool that can perform this intelligent synch Update.
But I believe this is not so utopistic to realize...

The tools suggested in stackoverflow are a good start point.