MySQL H.A with automatic failover

Question

MySQL has a lot of different highly available solutions, I would like to know what are the differences between all the current implementations, here are the implementations that look currently up to date :

• MySQL Group Replication (by Oracle)
• MySQL NDB Cluster (by Oracle)
• Galera Cluster (by Galera)
• XtraDB Cluster (by Percona)
• MySQL Fabric Cluster (by Oracle)
• MySQL Orchestrator (by Github)

What would be the recommended options (or any others) for a high available setup with automatic failover ? (For something equivalent of the SQL Server DB Mirroring with synchronous replication)

Answer 1

Galera == PXC == MariaDB w/Galera -- These are close to the same. For HA, you need 1 (or more) node in each of 3 datacenters. (Think earthquakes, tornados, floods, even datacenter outages -- which do happen.)

Oracle's Group Replication is quite new, but has a lot of promise.

Oracle's "Fabric" is probably being abandoned. (Probably because of a hard-to-solve single-point-of-failure.)

Orchestrator is very good at failing over arbitrary trees (think relay-servers) of lots (even hundreds) of slaves. Not sure about Master failover. (See lectures from Booking.com.)

MHA is pretty good -- But when it discovers a "split brain" situation, it requires manual intervention.

NDB Cluster has a number of different things in its "engine". That is, it is not a 'drop-in' replacement for InnoDB, which all of the rest support. For one thing, it employs "eventual consistency", wherein you need to specify how to resolve conflicts.

I think the Galera-based solutions, plus Group replication are the only ones that will automatically repair a failed server when it is brought back up. (I could be wrong here.)

My preference today: Percona's PXC or MariaDB with Galera -- each builds in and supports Galera. But I am keeping my eye on Group Replication.

Answer 2

Few words more to other answer (comments not well formatted):

• Orchestrator - it is not HA technologies, it is tool for visualise topology and resolve issue, but still based on under-layer of other technologies
• Galera vs NDB, main (for my point of view) difference it is supported engines - NDB vs InnoDB
• Group Replication is new, and I not work with it

Just as remark:

• Keep in mind for both of them (Galera and NDB) - have a huge amount of "in some case ...", "could be ..." and etc in documentation. All possible to make properly, but when You try to transfer legacy application to the cluster, make a proper tests before. I meet several time cases on projects, when bad application still work on single node (master-Slave) and die on Galera for example. The problem in wrong design, but client move live application to "modern" technologies after simple tests.

especial for last reason I very sceptic about 1 node per datacenter, depending from business needs You can choose what better - couple of seconds delay Slave behind Master, or slowdown per each changes. It could not be affected on very small transactions, and could be killer on more complicated.

Answer 3

A simple solution you may want to consider is an active/passive Pacemaker/Corosync setup with DRBD. The biggest downside is that a failover involves a crash recovery, but if your ib_logfiles are not huge and you can stand a little bit of time in the event of a failure (however long crash recovery takes -- could be a few seconds, could be much longer depending on how much needs to be replayed from the redo logs), it can simplify your life.

With DRBD, the data replication happens outside of MySQL and at the block level, so you avoid any problems with duplicate keys and data consistency across multiple masters. This can also be combined with MySQL replication slaves which read from the active server in the pair.

There's no one HA solution that is best for every case, and there are many conflicting opinions. This one is not a "write anywhere" solution, so if that's what you're looking for you can probably scratch it off the list.