0

reading about it online, it appears we can use both Galera replication and native MariaDB/MySQL replication together. Can anyone confirm that this is correct?

I'm thinking of a scenario where Data Center A has 3 Galera nodes synchronously replicating with each other, and Data Center B also has 3 Galera nodes synchronously replicating with each other. Then between one node in Data Center A and one node in Data Center B we have MariaDB/MySQL asyncoronous replication, in master-master configuration so going both ways.

As I understand it we now have:

  • Nice fast clusters for reads and writes in both data centers, which have redundancy within themselves if one node goes down.

  • Redundancy between data centers, so even if all of A or all of B goes down the other data center will stay functioning.

  • Under normal circumstances all nodes in both data centers have consistent data, or will within milliseconds of each other.

Have I missed anything - any potential problems? Thanks in advance for your advice.

David Cunningham
  • 31
  • 3

1 Answers1

0
  • AUTO_INCREMENT is probably not usable.
  • UNIQUE need care to avoid accidental dups.

How far apart (in ms) are the datacenters?

Rick James
  • 2,463
  • 1
  • 6
  • 13
  • AUTO_INCREMENT is already a concern for simple MySQL replication between two servers, and is solved using `auto_increment_increment` and `auto_increment_offset`. In my example above `auto_increment_increment` would be 6, and each server would have a different `auto_increment_offset`. Would there be any reason that wouldn't work here? Let's say in this hypothetical scenario the data centers are 20ms apart. What implications might this have? – David Cunningham Oct 19 '20 at 04:41
  • @DavidCunningham -- I expect Galera would insist on auto_increment_increment being 3. That is, if you set it to 6, it will change it to 3. Furthermore, if you take a node out of a cluster (or it crashes), the incr will be changed to 2? – Rick James Oct 19 '20 at 04:44
  • Regardless, you should set up a testbed with, say 6 VMs on one machine -- to see what happens. – Rick James Oct 19 '20 at 04:45
  • If Galera automatically changes the auto_increment_increment that could be a problem, since the cluster will be bigger than it realises. We'll have to test it, and testing is certainly something we'll do. I was checking to see if anyone else had used the above scenario and had experience to share. – David Cunningham Oct 19 '20 at 22:30
  • I note Galera has a wsrep_auto_increment_control option we can disable to control the auto_increment_increment and auto_increment_offset manually. – David Cunningham Oct 20 '20 at 03:42