0

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.

Improve this question
4
  • 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?
    – Rick James
    Commented Mar 1, 2017 at 16:05
  • @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".
    – zer09
    Commented Mar 2, 2017 at 4:54
  • Will changes only start in the tenant's computer? Or can they start in the central server/cluster?
    – Rick James
    Commented Mar 2, 2017 at 6:19
  • @RickJames changes could be on either way. It can start from the tenant computer or from the central server.
    – zer09
    Commented Mar 2, 2017 at 6:43

1 Answer 1

Reset to default
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.

Improve this answer
5
  • ,a single installation of mysql,separate data directories and my.cnf and Port.for +1
    – Md Haidar Ali Khan
    Commented Mar 2, 2017 at 7:01
  • 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
    Commented Mar 2, 2017 at 7:14
  • BTW on every tenant there could be a multiple users.
    – zer09
    Commented Mar 2, 2017 at 7:18
  • 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)?
    – Rick James
    Commented Mar 2, 2017 at 16:24
  • 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.
    – zer09
    Commented Mar 3, 2017 at 4:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.