tekknoir Posted March 23, 2010 Share Posted March 23, 2010 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.. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 23, 2010 Share Posted March 23, 2010 You're talking about building replication. Quote Link to comment Share on other sites More sharing options...
JustLikeIcarus Posted March 25, 2010 Share Posted March 25, 2010 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.