Hello, 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.
Hi @davidc Everything you said is correct. One issue to be aware of is that if node1 in DC1 goes down, and that node was your source for DC2 replication, you will have to manually move this connection and restart async replication.
As with any source/source replication, I would highly advise you to not write to both DCs at the same time. Keep the passive DC in read-only mode. This will amazingly reduce the chances for write conflicts over the async channel.
Thank you for that. We also discussed this with a Galera consultant who suggested including all the nodes in both data centres in the Galera cluster. Apparently selects will not be affected by the latency between data centers, and writes will be slowed by the latency just once (ie not latency * nodes in other data center). That would be simpler and might meet our requirements.
CON: By including all 6 nodes in to one Galera cluster, you can create a split-brain situation if the network link between DCs goes down. You’d have 50% of the nodes in DC1 and 50% in DC2. No quorum; both sides offline. Not an ideal configuration. If you wanted to do this, I suggest your primary DC have 3 nodes and DC2 only have 2 nodes, giving DC1 the majority in event of network failure.
CON: With a WAN-style cluster, because writes in Galera are synchronously replicated and ack’d, your max write speed is now determined by the slowest link between any two nodes. All 6 nodes must receive and acknowledge each replication set. In a 3-node LAN, only 3 nodes receive and ack. Then after the txn is committed, it will async replicate to DC2.
Thanks for that. They did suggest using an additional arbiter node to prevent the split-brain situation. I do agree that the maximum write speed will be determined by the slow link, but our application isn’t write-heavy, and as long as selects are fast we will probably be okay.
You could do an arbiter but that just increases your write latency even more because the arbiter participates/acks all messages as well; it just simply doesn’t store data. You could also change the pc.weight of one of the nodes in DC1 to be 2. That would give DC1 2+1+1 (4) and DC2 1+1+1 (3) so DC1 would remain online if network fails between DCs. DC2 would go non-primary state.
Selects will be fast, but remember that Galera/PXC is synchronous replication, but asynchronous apply. Eventual consistency. If you write to node1 and then try to read from node3, there’s no default guarantee that you will read that write. (unless you change wsrep_sync_wait)
Hmm, the consultant told us that write confirmations by arbiter nodes are asynchronous. Was that wrong information?
In our particular application it’s not really a requirement to guarantee the latest data is returned by selects. If a select could occasionally return information that’s some milliseconds old, that’s not a problem.
One of the down-sides of doing it Galera-only is that in a net-split situation the lower quorum side will go offline or read-only. In our combination solution above both sides will remain writeable, and although there is certainly a concern about unresolvable conflicts, our application has been used a LOT in master-master mysql replication and written in such a way that conflicts don’t occur. The one qualifier is that we use statement based replication, which is a little more forgiving than row based replication.
A follow-up question about “One issue to be aware of is that if node1 in DC1 goes down, and that node was your source for DC2 replication, you will have to manually move this connection and restart async replication”.
Since MySQL 5.7 we’ve had multi-source replication. Would that allow us to avoid losing connection if one node in the other DC goes down?
@davidc Multi-source replication from a 3-node PXC into a single MySQL would create an unsurmountable amount of issues. You’d be facing constant duplicate record and ‘record not found’ issues as each member of the PXC would be sending its binlog contents to the async replica. Utilizing GTIDs helps here because it would then be simple to move the source if it goes down without messing with binlog coordinates.
Thank you for that reply. Yes, using GTIDs would probably be essential with any attempt to combine Galera and asynchronous MySQL replication. If we use GTIDs, auto_increment_increment, and auto_increment_offset then in theory it should work, right?
auto_increment_increment and auto_increment_offset are managed by the Galera protocol. Check those variables on each member of the cluster. If you want “master/master” between the two clusters, you’ll have to make sure that these variables are set manually on every member of all 6 nodes (disable wsrep_auto_increment_control). a_i_i would be 6 and a_i_o would be 1 thru 6, incrementing for each member. This way, a write on an A_I PK shouldn’t cause conflict when being replicated.