DB idea: Updates on Connected/Disconnected Clients using MySQL Replication/Sync
I started looking to solve this application using MySQL and Java (but I'm flexible). I'm trying to deal with network-related concerns for the DB.
Here's a picture:
Using MySQL Replication for database transfers
The client-side updates are synced on the server.
When this sync is complete, then
a) A routine on the server runs the update SQL against the server DB
b) The client software switches to using DB (instead of DB Clone)
Client updates SQL is written to UPn, which syncs to server-side UPn,
which triggers updates to server-side DB, which syncs to client-side DB
Create DB Clone on client, and client software switches to using it.
Client updates SQL is written to UPn, and applied to DB Clone on client.
Automation: This site will run without me there, so if there is a problem, then I will be able to remote in to the server, but not the client machines. I am hoping that I will be able to script the handling of when rep/sync goes awry. (Will it go awry?)
Network events: I will need to know when the network connects and disconnects. Can I get notified of this in MySQL? (And can a MySQL procedure connect with a local Java program outside of MySQL to notify of an event?) Or not Java?
Sync events: I will need to be able to fire/catch events when MySQL sync occurs. Overall, I need the syncing to be able to happen pretty quickly (when the client is connected, for example).
So, these are my concerns in trying to handle the needs the above way. This is a small DB, and the size of updates during disconnects is also pretty small. Please let me know if you think this presents (unwanted) concerns!