Synchronising multiple servers...

[tekknoir]

Hello,

 

I am currently working on developing a CRM system, one of the features requested was a system which allows users to manage their own data online, which has been implemented.

 

However, my employer are reluctant to keep all of their marketing data on a web-facing server, because obviously if the server was hacked, it'd be pretty bad.

 

So, I have a second server in the office, which has permissions enabling it to access the database server on the website server, and a copy of the database structure.

 

My question is to do with synchronising data between the two servers.

 

The problem is not the issue of transporting data between the servers; the internal database server has permissions enabling it to connect to the remote server.  The issue is with reconciling the data..

 

Because the local server can access the database on the remote server (But not the other way around), it's fairly easy for updates made locally to be propagated to the new server, but less so for changes made by the customer website being propagated back to the internal system.

 

I think this can be circumvented by making both servers get their time from the same NTP server, and using timestamps on records to reconcile disparities, but I'm not sure what's the best way to synchronise the data, the current list of options I've come up with for it are:

• Keep a second table on the local server which mirrors exactly the table on the remote server, which is re-downloaded maybe 2 or 3 times a day
  
• As above, but without the cron job, and with a manual reconciliation button instead
  
• Have the local server check if the record exists on the remote server, and if so reconcile both before the record is loaded, and after it is saved
  

 

The third option entails quite a bit of traffic, in a sustained trickle, while the others are more bursts of high traffic a couple of times a day (It's likely to be fairly slow to be honest, taking into consideration the volume of data and the speed of the connection between the servers, maybe about 20 minutes to transfer all the data over).

 

If anyone has any advice on this topic, I would love to hear it

 

Currently I'm thinking option 3 might be the cleanest way to go..

[fenway]

You're talking about building replication.

[JustLikeIcarus]

Google "Mysql bidirectional replication" there are a few sites which explain how to set it up and you wont have to worry about timestamps, etc... since the db will handle everything.