I can't tell if mysql clustering could be a solution(I am thinking about galera or xtradb clustering). But my biggest problem is about the unstable internet connection.
Actually it is planned as multi-tenant setup with a single online mysql server and the tenant is separated by schema, and a tenant can also have their own local database on their location.
But I want to solve this problem first.
For example I have a tenant, but this tenant has a multiple collocation, and want to sync the data on every location and also on the online server.
They can read/write on the first, second, etc collocation server using a desktop app(respective to their location) and they can also read/write on the online server using a mobile app.
Now my problem is how could I sync them, when internet is available? So data written and updated on the first location will propagate to other server, on the online server on the second server and so on, and vice-versa.
P.S. Data deletion will be soft-delete.