We have set up an IOT device which records a lot of data. This data always makes it into the local gateway mysql database, and if there is an internet connection then it also goes into the cloud database.

The way we are doing it now, is by making connections to both databases and inserting the data into both at the same time. We do not have a master-slave type setup. I don't think we can do a master-slave setup since you have to specify which databases to sync in my.cnf (not possible given our setup, because we have many databases in the cloud per local device).

So my question is, if connection to the internet is lost on the local device, and the cloud misses some data, what is the best way to update the cloud database once it re-establishes a connection?

My current answer: I was thinking I could compare the local and cloud databases primary keys and then update the missing ones on the cloud -- but then I would have to ensure that the cloud primary keys are the same as the local (which means I would have to SELECT the auto_incremented value on the local database and use that same one on the cloud).

I am not a great database admin, but understand enough to get by. Any help or thoughts would be appreciated. Please let me know if I can clarify anything.


Since I can't comment, I ll put up a suggestion : Instead of playing with auto increment, can you think of something on line of timestamp preferably, (DD-MM-YYYY HH:MM:SS)? You can then compare the timestamp values of local and cloud.


Sharing my thoughts to resolve this issue.

  1. Firstly there is no need of DB side changes.

  2. From device application end you need to have a logic of retry to update in cloud once internet is back.

  3. Until it finds the connectivity, log the entries serially into local of that device.
  4. Once it finds the connectivity, re-spawn the logged entries from device and let it go as similar as cloud normal update.

Why do you think you need to compare the data? Anyways it has to go to DB I guess. You can have a current_timestamp as one of the columns to DB and value can be derived when the Device generates its local data using device NTP.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.