4

I am creating this web application in Node.js and MySQL and I am struggling on data synchronisation.

Here's the scenario:

Suppose we have 1000 customers that all have a local webserver-database in their office/site. A user can login, edit data, datalog sensors, etc. All these activities contribute to, for the sake of the example, around 500KB of table data per day, per customer/site. So each of the 1000 customers have a local database that will always constantly get updated with new data many times in a day.

At the same time for all 1000 customers there is a cloud service that allows them to login and see what all of their data, as if they were locally present on-site. They can also make changes from the cloud, edit/create/delete data.

Now the issues I have are:

  • Every site needs to be bidirectionally synced with the cloud server. What do you recommend? (Multi-master replication?, Clustering?, write a custom one?) What are the pros-cons?

  • What about when the customer's site is offline and there are new data added to both the local database and also in the cloud one? How are conflicts and merging usually done?

Thanks in advance!

opcode
  • 419
  • 5
  • 11

3 Answers3

1

Imagine a parallel reality where ATMs don't have a link with the bank, they work with a copy of the database and sync only by the end of the day.

You would be able to drive to every ATM in the city and repeatedly withdraw all money you have, multiple times. They would only notice the fraud when you are already millionaire and leaving the country.

This is just an exemple, but you see, even if you figure out this syncing mechanic you can still have unexpected issues, not only with values being changed but also different records being created with the same primary key etc. This can be a major problem.

I believe there is no generic technology of solving this, at all. You would have to carefully study the synchronization aspects of every single action your application could take in the database. Possibly generating a runnable SQL script in the process that will be used to sync, I don't know.

Havenard
  • 27,022
  • 5
  • 36
  • 62
  • Hi, Yes are absolutely right, I guess in the case of the ATM it is mandatory to be online all the time. I think in my case, loosing some sensor samples(due to id conflicts) is fine while allowing the office/site to continue running offline. Thank you for pointing this out, I am now temped to write the whole thing from scratch so I can manually deal with all the cases. :-) – opcode Nov 03 '13 at 04:04
  • It would be safe to do something like this if only one of the peers is capable of changing information. You could make the site read-only for instance. – Havenard Nov 03 '13 at 04:08
1

Opinion, from personal experience: MySQL replication does not scale well. If your sync fails, you will need some mechanism to notify and recover. If you have a thousand of these to manage, you will be doing a lot of operational work.

Another opinion: consider tracking changes. When either end makes a change, commit that change to a change control system. At a pre-defined time, have both ends lock out changes, have both ends run a process to cull duplicates from change log, and then have both ends update their database with non-duplicates. Finally, unlock changes. In the mean time, both ends are written to consult the change system to list pending changes.

This is not an easy situation: you have the large scale, distribution to deal with. As much as possible, minimize the moving parts so as to reduce maintenance points.

bishop
  • 37,830
  • 11
  • 104
  • 139
1

Mysql replication is not something that can be relied upon for this purpose .

However, if you are okay to go for an all-document-based storage, Couch DB solution will be able to solve this problem. We have been using couch database for some time and it has pretty much solved this problem where we sync the local couch database to the server couch cluster. I know this is an old post - but I hope someone will find this useful. Good luck!

code_kbd
  • 450
  • 4
  • 11