16

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?

ian
  • 1,505
  • 2
  • 17
  • 24

4 Answers4

7

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

Sunil
  • 2,885
  • 5
  • 34
  • 41
3

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.

RobinG
  • 196
  • 6
2

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.

OliveNice
  • 21
  • 1
0

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).

Maxime Pacary
  • 22,336
  • 11
  • 85
  • 113