How do I sync local and remote databases (both MySQL)?

I have a web app for managing Camping Program.
I need to allow users to register, obtain registration numbers, and upload their bio before the camp begins. They will do all these online which will be stored on the remote server hosted on the internet.

When the camp eventually begins, the local server will take in same data as above from those present in the camp. Changes are also made on it by the users.

I am confused on how to synchronise both servers to effect the latest changes on both, so that the user viewing the app locally within the camp without internet will have same data as the one who is outside the camp accessing the hosted app through the internet.

Thank you.

Database replication aside, if the user within the camp has no internet, how do you imagine the two can sync with each other? Or do you mean something else by “without internet”?

Replication is a complex subject, and something you need to consider while designing the database table layout rather than try to shoe-horn into the system later. Just stuff like how to decide which system assigns unique ids to a new user sign-up, for example. MySQL does support some replication features but I haven’t read into them in any detail.

1 Like

I’m guessing it’s something like a laptop, that could have a connection and be synced before going to the camp. Then would work off-line without a connection while at the camp.
If the local server is static, in a remote place with no connection, the data would have to be transferred by some other means. For example saving an sql file to a memory stick and taking that to the local server.
The OP will have to clarify the situation.

1 Like

Reading the topic at hand, I think the OP wants to actually have it synced while the users are at the camp. This is impossible to do without Internet unless you have some sort of inferred technology where the user can go to and scan with their device or maybe even a QR scanner to scan the user’s account QR code you generate to them upon registration. Another thing is if you have Wi-Fi there. Those are the only two possible answers. I mean or you could also do it manually like @SamA74 was suggesting.

You Guys are great. You replied quickly.
What I actually wanted is to allow users on and off camp to update data at the same time(so it will appear). For camp, I plan using WiFi as the network. Campers will connect to local server on a laptop and view and update info as happening on camp.
Then, the Admin will through some way sync these data to remote server so that those off camp can interact with them too. Now the main challenge is,
I don’t want just to duplicate local data on remote server. I want any changes like comments, posts, replies made by off camp users through the internet on the remote server to persist and not overridden during syncing.

I will be so glad to get some help on this.

Thanks.

@droopsnoot has already identified the main problem:

So campers connect to the local server via wifi. But does the local sever (at the camp) have an internet connection?
If not, I don’t see how this can be possible.
If it does have an internet connection, then I would not complicate things with duplication and keep everything central to the host server. Though it is still a good idea to keep regular backups of your data of course, but that’s something else.

Internet is not needed to create a wireless LAN with WiFi. I have used it in my app eWA to distribute lesson notes to over 30 different laptop on which each student sent own comments.
No internet at all.
It is costly in this part of the world.

Then it’s unlikely that you can sync your databases at all.

Please,
I have access to internet within the camp.
What am saying is, campers can browse the local server without internet. Saving great cost.

If your local server is connected to the internet, then you don’t even need a local copy. Just proxy it from your laptop to the remote server, avoiding any need for replication.

I wouldn’t proxy it to the remote because the users in camp can’t afford to access it through the internet.
That is why I need a local server which hold main data. The remote is there just for extensibility of data interaction with those off camp.

The users in the camp would be using your servers internet connection via the wifi, just the same as you may connect to a public wifi some place, it would not cost them anything. Or is the data charge to the camp from your ISP a problem?
Either way, without using an internet connection, you can’t automatically sync both ways.

That’s the whole point of a proxy … access the remote server without having to connect to the remote server yourself.

Ok thanks.
I will find a way round this.
Most probably will fall back on download and upload of SQL backups.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.