Strategy on synchronizing database from multiple l

Question

I have several databases located in different locations and a central database which is in a data center. All have the same schema. All of them are changed(insert/update/delete) in each location with different data including the central database.

I would like to synchronise all the data in the central database. I would also like all data in the central database synchronise to all locations. What I mean is that database change in location 1 should also be reflected in location 2 database.

Any ideas on how to go about this?

Answer 1

I went to SymmetricDS

I think it is the top quality also just to mention I found in sourceforge (php mysql sync)

and found many links on the internet.

Answer 2

Unfortunately, MySQL replication capabilities won't allow you to do exactly what you want.

Usually, to synchronize two servers the master-master replication scheme can be used. See http://www.howtoforge.com/mysql_master_master_replication

The problem is that each MySQL server can have ONLY ONE master.

The only way I know to keep several servers synchronized, would be a circular replication (see http://onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html?page=2), but won't exactly fit your needs ("star" configuration)

Maybe this configuration could be close enough : all the "distant" (non central) databases would be only be readable slaves (synchronized though a basic master-slave replication), and writings would only occur on the central server (which would be master).

Answer 3

Just look at SymmetricDS. It is a data replication software that supports multiple subscribers and bi-directional synchronization.

Answer 4

You will have to implement a two-way replication scheme between the databases. Every new record created should have a unique identifier (eg. a GUID), so that data from the different databases does not conflict. (See the mysql replication howto).

MySql only supports one-way replication, so you will need to set up each database as a master, and make each database a slave of all the other database instances. Good luck with that.