How to design data synchronization architecture for MySQL?

Question

MySQL

Initial data:

• central mysql running on Amazon RDS (no access to binlog)
• there are 10-20 local physical servers in different parts of the world
• local servers are used for a short time - up to two weeks
• the connection between local and central is unstable, local can go offline
• data on the local and central server can be updated simultaneously (the keys will not intersect at the same time)

The task is to synchronize all local and central servers in real time with minimal delay.

We think to implement the solution with our own scripts.

Maybe there are standard solutions?

Answer 1

I have already implemented this. Truth on MSSQL 2000, but the essence does not change.
All database entities contained the following fields:
OBJECT_ID
OBJECT_NAME
OBJECT_DATE_INSERT
OBJECT_USER_INSERT
OBJECT_DATE_UPDATE
OBJECT_USER_UPDATE
DELETED
Above are, as it were, the parameters of the Object class, from which all other classes are inherited.
OBJECT_ID - unique for all database objects.
One main server allocates ID ranges for local servers once a day, and, in fact, distributes them during a new synchronization.
Synchronization takes place in 4 stages:
1. Select new objects created on the local server:
LOCAL_SERVER.OBJECT_DATE_INSERT >LAST_SYNC_DATE
2. Select objects that have been changed on the local server since the last synchronization:
LOCAL_SERVER.OBJECT_DATE_UPDATE > LAST_SYNC_DATE
3. Select new objects created on all other local servers except ours:
REMOTE_LOCAL_SERVER.OBJECT_DATE_INSERT > LAST_SYNC_DATE
4. Select changed, respectively, on all other local Object servers:
REMOTE_LOCAL_SERVER.OBJECT_DATE_UPDATE > LAST_SYNC_DATE