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.