0

We are looking into options for our MySQL replication architecture, the relevant details of our current setup:

  • We manage several branches on different cities.
  • Every branch has the same database structure.
  • Every primary key on all tables are prefixed by a branch identifier.
  • We need that a branch keep working if it has a network outage and it must sync with the main branch once the connection is restored.

As we don't have any chance to get a duplicate index on any table I'm thinking on something like MySQL multi master, or maybe Percona XtraDB Cluster or Tungsten but I can't find documentation on what happens if a single node is isolated from the others and what happens with the data that it received once the connection is restored.

Is there any proven method that suit this kind of setup? I would appreciate any advice, thanks.

kR105
  • 864
  • 9
  • 15
  • At my old job we used sql server replication to avoid getting isolated. Each of our customers had his "own server" and we had a copy on our datafarm. So if by anycase(which happened a lot) we fixed in the code to point to our server instead of theirs. – Gino May 22 '17 at 22:07

2 Answers2

0

In the case of tungsten, how it behaves depends on how you tell it to behave

You seem to be describing a relaxed consistency model. But no off the shelf clustering solution is going to solve all your problems. Certainly if you ensure that each record is only ever modified at its "home" database then you shouldn't run into many problems, but this model also requires you to replicate all the data to all of the locations. Bandwidth might not be an issue and it does provide a good DR capability, but storage and scalability may become an issue.

If you do have a centralized, well managed datacentre then another approach would be to have each branch run off an asynchronous dual master - one located at the branch and one at the datacentre then roll your own scripts for consolidating the dataset.

symcbean
  • 47,736
  • 6
  • 59
  • 94
0

Multi master replication would work, as long as your not performing UPDATEs on the same data when the nodes are out of sync. In that case, it will apply the changes silently, so your data would become inconsistent.

If you're not doing that, I think it's the best solution, as MySQL takes care of the binary logs pointers and handles reconnections. Just be sure that the auto_increment_offset setting is properly configured amongst the masters. Anyway, I've just tested this deployment with just 2 master servers (7 years in production with little issues).

charli
  • 1,700
  • 1
  • 13
  • 21