Replicate Master DB to Different Slaves

Question

I have a master database which would be the cloud server that consisted of different schools.

Dashboard type that has the details of each school. Can edit their information and other data.

Now those schools are deployed to their corresponding school location which would be the local server.

Dashboard type that can only edit the specific school deployed in the local server. Can edit their information and other data.

Now what I want to happen is, to synchronize the cloud to local server on their corresponding school if something is changed. That also goes for local to cloud server.

Note: If you guys ever tried Evernote, that can edit the notes information on whatever device you're using and still be able to synchronize when you have internet or manually clicked synchronize.

When the local server doesn't have internet connection and edited some data in school. Once the internet is up, the data from local and cloud server should be synchronize.

That's the logic that I'm pursuing to have.

Would anyone shed some light for me where to start off? I couldn't think of any solution that fit my problem.

I also think of using php to foreach loop all over the table and data that corresponds to current date and time. But I know that would be so bad.

Edited: I deleted references / posts of other SO questions regarding this matter.

The application pegs that I found are

• Evernote
• Todoist

Servers:

• Local Server Computer: Windows 10 (Deployed in Schools)
• Cloud Server: Probably some dedicated hosting that uses phpmyadmin

Not to be picky but, hopefully the answer would be you're talking to a newbie to master to slave database process. I don't have experience for this.

Answer 1

When we used to do this we would:

1. Make sure every table we wanted to sync had datetime columns for Created; Modified; & Deleted. They would also have a boolean isDeleted column (so rather than physically delete records we would flag it to true and ignore it in queries). This means we could query for any records that have been deleted since a certain time and return an array of these deleted IDs.
2. In each DB (Master and slave) create a table that stores the last successful sync datetime. In the master this table stores multiple records: 1 for each school, but in the slave it just needs 1 record - the last time it synced with the master.

In your case every so often each of the slaves would:

1. Call a webservice (a URL) of the master, lets say called 'helloMaster'. It would pass in the school name (or some specific identifier), the last time they successfully synced with the master, authentication details (for security) and expect a response from the master of whether the master had any updates for the school since that datetime provided. Really the point here is just looking for an acknowledgement that the master available and listening (ie. the internet is still up).

2. Then, the slave would call another webservice, lets say called 'sendUpdates'. It would again pass in the school name, last successful sync, (plus security authentication details) & three arrays for any added, updated and deleted records since last sync. The master just acknowledge receipt. If a receipt was acknowledged then the slave to move to step 3, otherwise the slave would try step 1 again after a pause of some duration. So now the Master has updates from the slave. Note: it is up to the master to decide how to merge any records if there are conflicts with its pending slave updates.

3. The slave then calls a webservice, lets say 'getUpdates'. It passes in the school name, last successful sync, security authentication details, & the master then return to it three arrays for any added, updated and deleted records it has which the slave is expected to apply to its database.

4. Finally once the slave tries to update its records it will then notifies the master of success/failure through another webservice, say 'updateStatus'. If successful then the master will return a new sync date for the slave to store (this will exactly match the date the master stores in its table). If it fails then the error is logged in the master and we go back to step 1 after a pause.

I have left out some detail out about error handling, getting the times accurate across all devices (there might be different time zones involved), and some other bits and pieces, but that's the gist of it.

I may make refinements after thinking on it more (or others might edit my post).

Hope that helps at least.

Answer 2

I will suggest you to go with the Trivial Solution, which according to me is:

1. Create a SQLlite or any database (MySQL or your choice) in local server
2. Keep a always running thread which will be pinging (makes an API call) your Master database every 5 minutes (depends on how much delay is accepted)
3. With that thread you can detect whether you're connected to the internet or not.

4. If connected to internet

   a) Send local changes with the request to master server, this master server is an application server, which will be capable to update changes of local machines in school (you received this changes by an API call) to the master database after certain validations according to your application usage.

   b) Receive updated changes from the server after the API call, this changes are served after solving conflicts (like if data in school server was updated earlier than data updated in master database so which one you will accept based on your requirement).

5. If not connected to internet, keep storing changes in local database and reflect those changes in Application which is running in school, but when you get connected push those changes to master server and pull actual changes which is applicable from the master server.

---

This is complicated to do it by your own, but if the scale is small I will prefer to implement your own APIs for the database applications which will connect in this manner.

---

Better solution will be to use Google Firebase, which is a real time database which is asynchronously updated whenever there is change in any machine, but can cost you higher if its really not required. But yes it will really give you Evernote type realtime editing features for your database systems.