Because internet is not always available i cannot just use one central database. So there are is a DB in every location. I need to have them all in sync, but there can be a delay of 30 minutes or maybe even 1 hour between syncing. I use mysql 5.5.62 but i could probably upgrade if needed. I could also have a server that every location can reach in a different place. Would that help if one DB goes down?

What would be my best strategy?

Thank you very much

  • 1
    Use mysql-5.7 or mariadb-10.2+ multisource with a each node replicating from every other node. Have a strategy that ensure that updates of one data item occur on one particular node.
    – danblack
    Apr 27 '21 at 6:50
  • @danblack - I think "multisource" is aimed at "many-to-one", not "many-to-many". Multi-master has a lot of potential problems; adding multisource smells like a nightmare for the admin.
    – Rick James
    May 3 '21 at 17:33

There is no "perfect" solution to your requirements, but see if MariaDB with Galera is "good enough":

  • Have 4 or more geographically separated nodes.
  • If a "quorum" of the nodes (more than 50%) stay connected to each other, they stay in sync -- usually within seconds.
  • If, say, 3 nodes are talking to each other, but you are connecting to the 4th node (which can't see the other 3), you won't be able to write. Having a proxy try to reach a writable node will sometimes solve this situation.
  • If there are multiple network problems, the "cluster" will fix itself after the network is healed. Meanwhile you can't write.

NDB Cluster replicates via "eventual consistency". This allows different nodes to be written independently, as you have with a flakey network.

  • Goal is to have the local node always running. If it goes down, then it is going to be fixed on the spot. Not being able to write is no option. :(
    – Huppel
    May 4 '21 at 12:59
  • No good answer.
    – Rick James
    May 4 '21 at 19:24
  • What do you mean? :)
    – Huppel
    May 5 '21 at 11:16
  • @Huppel - There is probably no good way to do what you want via MySQL. I added another MySQL offering (NDB), but I am not sure that even it will work in your case.
    – Rick James
    May 5 '21 at 14:18
  • It should be possible that all 4 locations write to the central database / cloud in like 95+% of the time. But what would be my solution when internet goes down? Rather work on a good internet backup solution and work with like HAProxy? Any more ideas? Thank you
    – Huppel
    May 11 '21 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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