How to sync two or more collocation database server?

Question

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.

Thanks.

• Rick James: Each tenant would have his own machine? Plus you would have centralized server or two? That is, there is a different "cluster" for each tenant?
• zer09: @RickJames Yes each tenant would have their own local server machine. For now there is only a single centralized server. If the single server can't handle the load, then there should be a "local only clustering".
• Rick James: Will changes only start in the tenant's computer? Or can they start in the central server/cluster?
• zer09: @RickJames changes could be on either way. It can start from the tenant computer or from the central server.

Answer 1

Suggest you have multiple instances in the central server. It could be a single installation of mysql, but separate data directories and my.cnf and Port (or use VMs).

After doing that, you can make lots of pairs of "Dual Master" setups where either Master can be written to. And they can get into serious trouble with auto_increment keys, unique keys, and other things that plague M-M.

No, I really think, you should start over on your 'requirements'. Currently you are asking for a maintenance nightmare.

• Md Haidar Ali Khan: ,a single installation of mysql,separate data directories and my.cnf and Port.for +1
• zer09: Can I ask advice? Is there other strategy I can do, that even the internet is down the tenant can still use the application offline or just on the local network only. The sole purpose of the local database server is that, they can still do things even the internet is down. If I can find solution of that, I can eliminate setting up local database server. So everything will be centralized.
• zer09: BTW on every tenant there could be a multiple users.
• Rick James: It simplifies life for the users if they are told only about connecting to the local (not central) server. But then, why have the central server(s)?
• zer09: To give them a centralized database server, example if there warehouse is 10 miles away or the owner want a real-time report, but he is out of the country.