MySQL Master High Availability and Failover: more thoughts

Some months ago, Shlomi Noach published a series about Service Discovery.  In his posts, Shlomi describes many ways for an application to find the master.  He also gives detail on how these solutions cope with failing-over to a slave, including their integration with Orchestrator.

This is a great series, and I recommend its reading for everybody implementing master failover, with or without Orchestrator, even if you are not fully automating the process yet.  Taking a step back, I realized that service discovery is only one of the five parts of a full MySQL Master Failover Strategy; this post is about these five parts.  In some follow-up posts, I might analyze some deployments using the framework presented in this post.

UPDATE 2019-03-03: as pointed out by Shlomi in the comments, his post "MySQL High Availability tools" followup, the missing piece: orchestrator is a complement discussion about some of the topics covered in this post.

I consider this a Request for Comments.  I do not claim my thoughts are fully mature or that I am covering all aspects of MySQL master high availability and failover.  I just think that this presentation is more complete than everything I read so far on the subject, so let’s dive in.

I already mentioned the MySQL Master Failover Strategy/Framework at the GrowIT Conference (Novi Sad, Serbia) in my talk MySQL Scalability and Reliability for Replicated Environment (slides & recording).  In slide #27, I present the five parts which are 1) plan, 2) when, 3) tell, 4) protect, and 5) fix.  Being a little more explicit, we can name them as follows:

1. Failure detection (when)
2. Reaction to failures (plan or how)
3. Service discovery (tell)
4. Fencing (protect)
5. Dealing with split-brain (fix)

You can see that the plan and the when are in a different order.  The list above presents things as they happen during a failure.  In my talk, I put plan first because it is something that strongly impacts the rest of the strategy, and for this reason, I also describe it first below.

Reaction to Failures

The way we react to a failure is a very structuring part of implementing MySQL master high availability.  It can be as simple as restarting the mysqld process (or rebooting the server hosting the MySQL database), or as complex as failing-over to a slave in another datacenter.  Neither of the two allows to recover from dropping a table or schema, so restoring a backup or using delayed replication should also be included in a complete data availability strategy, but I am not covering these two in this post.

The way of reacting to failures is the most important factor impacting the Recovery Point Objective (RPO).  Said otherwise, it structures significantly how much data is lost/missing after recovery.  Restoring a backup implies losing a lot (potentially hours), and this can be mitigated by saving and applying the binary logs (maybe with Binlog Servers).  Promoting a slave as the new master loses very little (only the last few committed transactions that were not yet replicated).  Failing-over to another node in a XtraDB Cluster (Galera) or a Group Replication deployment has zero data lost, and it is the same for semi-synchronous replication when rpl_semi_sync_master_wait_point is set to AFTER_SYNC (also known as lossless).

Another important property of a failover strategy is the Recovery Time Objective (RTO).  Waiting for a server to reboot, or for MySQL crash recovery to complete (including InnoDB), can take a long time and this can be a major part of the RTO (moreover, and unrelated to the RTO, a MySQL instance has a cold cache after a restart, and this leads to degraded performance until all the data is back in RAM).  However and compared to other factors impacting the RTO, a failover to a slave (or to another node in a cluster) is relatively quick: another node is usually ready to accept write with minimal reconfiguration, and failing-over to a slave in a small or well-configured replication setup takes only some seconds for repointing other slaves (and in both cases, the cache of the new master is warm).  Other factors impacting the RTO in a failover solution are Failure Detection, Service Discovery and Fencing, and we will come back to these in the next sections.

Finally, to conclude this section, we have to highlight that the reaction to failures impacts the requirement for dynamic service discovery (or the absence of such requirement).  If, after a failure, we restart MySQL or reboot the server hosting the database, there is no need for dynamic service discovery.  But if we fail-over to a slave, the higher layers must be informed that the master has changed.  As we will soon discover, all the parts of a MySQL master high availability strategy are interconnected, and an adjustment in one impacts the requirement and implementation of the others.

Failure Detection

In the previous section, we discussed the subject of reacting to a failure.  But having a plan is useless if we do not know when a failure happens.  And this is where Failure Detection comes into play.

Reliable failure detection is hard

The first thing to realize when implementing failure detection is that this is a very complex and hard subject: anyone saying to have a simple, reliable and efficient failure detector is naive.  The same way you would not take seriously someone claiming to have solved the halting problem, you should dismiss people saying they have a reliable failure detector (they might have solved failure detection for what they think is their class of failures, but they probably forgot something, and they surely could not have solved it for the most general case).  Implementing failure detection is always a trade-off between:

• time taken for failure detection
• probability of false positive
• probability of false negative

We previously talked about the RTO saying that the way we react to failure is one of the factors impacting the total time taken for restoring availability of the master; the time taken to detect a failure is also a major part of the RTO.

Failure detection is, in most cases,
the biggest impacting factor of the RTO

Sometimes, the consequence of a false positive is so high that it is ok to spend a lot time detecting a failure.  In other cases, the cost of being down is so high that we do not want any false negative and we want to minimize the RTO, so the flip-side is coping with false positives.  And false positives, combined with imperfect service discovery, introduces the need for fencing and dealing with split-brains, but I am getting ahead of myself.

Some failures are easy to detect: an example is a mysqld process that crashed.  And depending on the way things are configured, we can make sure that it does not come back (fencing).  This assumes we have a view on the Linux server running MySQL, which is not always the possible (like in Cloud environments or other database hosting solutions), or because the failure prevents connection to Linux.

Sometimes, MySQL comes back from the dead…

Some failures are harder to ascertain: an example is when a server is unavailable due to a CPU overload, a network problem, etc.  In this case, and after triggering a failover, the old master might come back from the dead if the failure was transient.  We will discuss the consequence of this in more details in the sections about service discovery, fencing and dealing with split-brains.

Some failures depend on the observer: an example is a network partition.  Some nodes in the partition are still able to access the master, but others out of the partition are not.  This is one of the most complex situation to deal with in terms of recovery procedure for ensuring data consistency.

Service Discovery

Service discovery is the process by which the application knows how to connect to an endpoint/service, and the service that we are discussing in this post is the MySQL master.  Service discovery can be static (an IP address in a configuration file) or dynamic (a DNS name updated when switching master).  Shlomi is already doing a very good Service Discovery Inventory in his series, so I am referring you to his posts for more details about different ways of implementing this.

In this section, I cover the properties of service discovery.  As it involves many components (application nodes, network, DNS, proxies, service registries, …), service discovery is a complex distributed system on its own.  And as for all distributed systems, service discovery implies many choices and trade-offs in their implementation.  Those choices impact some fundamental characteristics of service discovery:

• Global or local
• Centralized of distributed
• Atomic or convergent (eventual consistency)
• Performance behavior (introducing latency or not)
• Impact on RTO
• Failure modes
• Ease of debugging

As we pointed out in the section Reaction to Failures, some strategies do not need dynamic service discovery.  If your plan is to restart a crashed mysqld process, to reboot the server hosting MySQL, or to restore a backup, then dynamically informing the application of the location of the new master is not needed.  In those cases, you can easily do with an IP address in a configuration file, and in the unlikely situation where this IP changes (restoring a backup on another server), you can change the configuration using your normal (and potentially slow) deployment procedure.  However, this usually means that the RTO is high and that the master is unavailable for a significant time after a failure, which is not always satisfactory.

To get a very short downtime when a master fails (small RTO), you need to fail-over to another node, which implies dynamic service discovery.  The way you implement service discovery also impact your overall failover strategy.  Taking a Local Service Discovery example, you can use a floating IP (VIP) for the master, but then you are limited to failing-over to a node in the same network segment (both old and new master must have the same gateway as their first IP routing hop).  A floating IP in the same Ethernet segment also usually mean that you cannot fail-over to another datacenter, so this does not solve disaster recovery (also, it does not solve unavailability caused by a network equipment failure).  But floating IPs are very simple to understand, which means they are easy to debug, but it comes at the risk of misbehaving if two nodes are claiming the same IP.  And to prevent this problem, we could avoid claiming the IP on boot, or maybe disconnect the old master at the switch level.  This operation at the network level is one way to fence the old master, but we will come back to fencing later in this section and in the following section.

To be able to do the most flexible failover, including to another node in a remote datacenter, we need a Global Service Discovery.  The most common example of such system is DNS.  I do not want to get into the details, but usually, updating a DNS entry is done on a master server, and this change propagate rapidly to all other DNS servers.  This means that a DNS change is not atomic, which implies that some application servers might connect to the new master while others still connect to the old master.  In this regard, DNS is a distributed and eventually consistent way of implementing service discovery.  Moreover, there is the TTL property of DNS entries that can get in the way, but now I am diving too much in the details of DNS implementation.

DNS is not an atomic way to implement service discovery

Back to the service discovery properties of DNS, one of its failure modes is network partition.  If a DNS slave server is co-located in a partition with the MySQL master, the DNS update cannot reach the slave, and when the partition is solved (a router or a firewall come back online), there is a short period of time during which the DNS slave still points to the old master, and this can lead to writes happening there instead of on the new MySQL master (I have seen that happening in production: not fun !).  This again looks like a situation that could be solved by fencing, and it also introduces the split-brain problem where writes are happening on two unsynchronized nodes: we will come back to both later in this section and in the following sections.

So we saw that DNS is a way to implement global service discovery in a distributed way, what about doing it in a centralized way ?  One architecture achieving this is using proxies (and this is something I am implementing right now with ProxySQL: I will surely blog about this in the future).  Obviously, using a single proxy would not work as it would be a single point of failure, but with being smart, I think two proxies can be enough for managing the simple failure of a master, and four additional proxies are enough to manage complex network partitions.  This centralized deployment (if we can call two to six servers a centralized solution) can totally avoid the need for fencing and for dealing with split-brains, but at the cost of higher latency for statement routing in the proxy layers.  Proxies also hide the source IP of the application server making the connection to MySQL, which makes things harder to debug.  This could be fixed using connection attributes, but I do not know of any proxies implementing this yet.

There are intermediary solutions involving service registries like ZooKeeper, Consul or etcd, and also sometimes involving proxies.  Shlomi describes some of these in his series, so I do not give details about them here.  I only mention that those have the benefit of reducing the latencies introduced by the centralized solutions, but at the drawback of reintroducing the need for fencing and dealing with split-brains.  They are also much complex and harder to understand, which make their failure modes harder to predict and debug.

I have already mentioned fencing and split-brain many times in this section, and before describing these in detail in the next two sections, I pause here for introducing them.  The need for fencing and dealing with split-brains is introduced by imperfect failure detection combined with imperfect service discovery.  If we would have perfect failure detection (and this is impossible — remember the halting problem — but let’s do this thought experiment), we would never need to make sure the old master does not receive write (fencing) and we cannot have a split-brain.  The same applies for perfect service discovery, and we saw above that it is possible in the centralized proxy solution, for which case fencing and dealing with split-brains are not needed.  But when false positive in failure detection might happen (in most cases), and if service discovery is not perfect, there is a risk of some application servers writing to the old master while some others are writing to the new master, and this is a split-brain.  To avoid a split-brain, one solution is to make sure the old master is unavailable, and this is called fencing.  And when fencing is not perfect, we might need to reconcile data from both the old and the new master, and this is what I call dealing with split-brains.  Those subject are discussed in the next two sections.

Fencing

As quickly explained in the previous section, the need for fencing the old master comes from the combination of imperfect failure detection and imperfect service discovery.  Improving failure detection is very hard to do, and perfect service discovery introduces latencies that might be unacceptable in some cases.  So sometimes, when we want to avoid these latencies, fencing is a good way to prevent split-brains.  Best effort fencing might be quick, but fencing with high confidence could take time, so depending on the effort invested in this, fencing might be a non-negligible part of the RTO.

There are many ways for fencing a MySQL master, one of which is avoiding restarting the mysqld process after a crash, or always starting MySQL in READ-ONLY (or SUPER READ-ONLY) or OFFLINE mode.  But this would not cover the case where a server is unreachable and could come back from the dead.

For an unreachable operating system (Linux), powering off the hardware via a LOM device might be possible (iLO for HP servers, DRAC for Dell servers, IPMI for the generic interface).  An option for virtual machines is to shut them down at the hypervisor level (or using APIs for Amazon Web Services, Google Cloud Platform or Microsoft Azure).  Those solutions are known as STONITH (Shoot The Other Node In The Head).  Both API and hardware solutions are not fully satisfying because they are not 100% reliable and they might not work for network partitions.  Disconnecting a server at the switch level has the same problems.

Another way for fencing a master is to put a circuit breaker in front of it.  This circuit breaker can be a smart proxy, deployed on the same server as the database, which holds a lock in Zookeeper (or other distributed synchronization solutions like Consul or etcd).  This smart proxy also monitors the health of the database, and if the database misbehaves, the proxy blocks connections and releases the lock allowing another node to become the master (this is similar to STONITH to make sure a dead MySQL stays dead).  In the case of a server failure, the lock expires after some time (typically 30 seconds), so this adds to the RTO, but this guarantees that there is only one master.  But in the case of a network partition, this 30-second timeout keeps the circuit open and allows writes to reach the master.  In this case, this is not a full split-brain as the two masters are not alive at the same time, but those writes on the old master are lost on failover (not on the slave), so the RPO includes those 30 seconds, or data needs to be reconciliated (details about this in the split-brain section).

A good way to fence the master, and this is known to be used by Facebook (see “node fence dead master” at Slide #22 of the talk Binlog Server at Facebook), is to use lossless semi-synchronous replication to prevent commit from succeeding on the old master.  This is a good way to prevent writes from completing, but this does not avoid reads, and this blocks clients in commit (they eventually time-out).  We need to realize/remember that a commit timeout implies an undefined state in MySQL, so clients cannot know if they should retry or not (maybe data has been committed: the OK packet was lost; or maybe not: the commit order was lost).  This bubble-up the failure at the application level, allowing to present the user with an error message, but without clear ways to move forward in handling the problem.

Semi-sync has even a more complex behavior on crash recovery when there are no semi-sync slaves and when the master is configured to require semi-sync ACKs (rpl_semi_sync_master_wait_for_slave_count > 0 and rpl_semi_sync_master_wait_no_slave = ON).  In this case, the commit hangs, but the transaction are rolled forward if MySQL crashes and restarts.  I wrote about this behavior on the Percona Community Blog in the post Question about Semi-Synchronous Replication: the Answer with All the Details, and I also mention the Facebook trick of avoiding this roll-forward.

Back to fencing with semi-sync, the Facebook implementation does not block writes in the case of a network partition because their semi-sync slaves are co-located in the same datacenter as the master (they might even be co-located in the same rack as the master to minimize commit latency).  If we are willing to accept a longer commit latency (20 to 50 milliseconds), we could put the semi-sync slaves in other datacenters.  This avoids the higher latencies of sending statements to the database via proxies, and reliably fences the master in case of network partitions, but at the cost of a higher latency on commit (probably acceptable if you are sending statement to the master from another datacenter).  I have not yet implemented this myself, but I plan to experiment with this in the near future to make master accesses faster than via proxies.  This is the only 100% reliable fencing mechanism that I know which can deal with imperfect service discovery in network partitions.

A section about fencing would be incomplete without mentioning XtraDB Cluster (Galera) or Group Replication.  Those solutions have fencing built-in as they need at least the majority of nodes to agree on committing a transaction (I think Group Replication needs a majority and Galera needs all non-failed nodes to agree on commit, but I did not fully study those technologies, so I am not 100% sure).  The flip-side of this property is that commit can return an error, which is not possible in normal MySQL (commit taking forever, or losing the connection to the master, is different than commit returning an error).  Because this is an unusual behavior, a lot of code might not manage commit returning an error, and for that reason, I do not fully agree that Group Replication or XtraDB Cluster are drop-in replacement of MySQL.  Also, unless you deploy those clustering solutions across multiple datacenters (and I am not sure that it is supported or well tested, see this post on Percona Blog: How Not to do MySQL High Availability...), you still have to manage disaster recovery with failing-over to another datacenter, in which case the built-in fencing does not prevent split-brains.

Dealing with Split-Brains

And now, we can cover the worst thing of all, the nightmare of all data engineers: data inconsistencies (in this case, introduced by split-brains).  Providing consistent data is highly valued by data geeks.  It is so highly value that when we cannot guarantee consistency, our intuitive and first reaction is to make a data-store unavailable for writes (and also potentially for reads).  But this means low availability, which is conflicting with business continuity, so a good trade-off needs to be found.  But let’s go back to the specific subject of the split-brain.

Having a MySQL master split-brain means that two independent (or unsynchronized) servers are accepting writes at the same time.  The consequence of a split-brain is data inconsistency: the data on each of the servers is not the same because of different writes, and this can lead to disastrous situations (think bank account…).

In the context of MySQL master failover, split-brains happen when combining false positive in failure detection, with imperfect service discovery, and with improper fencing.  Because getting better at any of these implies increasing the RTO or degrading performances, split-brains cannot always be prevented.  To cover this risk, a good strategy is knowing beforehand what to do when facing a split-brain (a bad strategy would be ignoring this possibility when we know it can happen).

The first way to deal with a split-brain is to discard one of the brains.  This is equivalent to dropping committed transactions, which is an abomination for data engineers.  But this might be the right solution when recovering data would cost more than the value provided by this data.  Other situations where dropping transactions is acceptable are when the unavailability of the system costs more than losing data or when it does not imply a significant increase in existing compensating activities.

To extend a little on the last point above (existing compensating activities), if you are an online merchant, dropping 100 orders because of a split-brain obviously sounds bad.  But if you have 1000 orders that are lost daily because of stolen packages or bad delivery, maybe dropping those 100 orders once in a while is not such a big problem (maybe losing 5000 orders because of a split-brain would even be acceptable).  Because you already have a customer service department dealing with this type of problem, making them deal with 100 lost orders because of a split-brain is probably better than not accepting orders for 30 minutes.  As long as you know what you lost and it is in the acceptable range for your business, things are fine(-ish).

To have an idea of what you lost, you can check the binary logs of the discarded MySQL node.  There are probably ways of structuring your data that would make this easier to check (think transaction log), but I do not have good advice to give here.

In some other cases, you might want to reconcile the data of the two brains.  This can be very challenging in the general case, and the only way you can achieve that is by knowing your data well.  I cannot give you advice here either because I do not know your data.

But one thing I know and experienced the hard way — I mentioned it in my talk Autopsy of an automation disaster (slides & recording) — is that AUTO_INCREMENTs (also known as SEQUENCEs in other databases, or as their alias SERIAL) makes data reconciliation much harder.  Because auto-increments are allocated by the database, when you have two masters, they might allocate the same values.  In this case, you have two rows sharing the same identifier but with different data.  When you use this identifier in another table, things become messy, and data reconciliation becomes very hard (you might even show the wrong data to users of your system depending on the behavior of the split-brain).  My advice here is to stay away from all identifiers that are allocated by the database, and to use UUIDs instead (in my talk, I mention the Percona post about Storing UUID in an optimized way).

An example of a split-brain situation where data reconciliation was challenging is in the GitHub 21st of October 2018 post-incident analysis.  In this case, when the split-brain was discovered, the service was interrupted/degraded for hours to allow data reconciliation.

It is better to avoid split-brain with perfect service discovery or perfect fencing, but if you cannot because of some reasons, and if you end-up in a split-brain situation, hopefully you thought about it beforehand and you know what to do.  If you have not planned for this, hopefully you can think quickly.  And if not, then you have to push the big red button and then…

Good luck !

Some Closing Remarks

As I wrote at the beginning of the post, this is the current state of my thoughts about the very complex subject of MySQL Master Failover Strategies.  If we want to summarize:

• Perfect failure detection is very hard, and to provide a small RTO, there are always false positive.
• Service discovery is hard (but not impossible), but perfect service discovery impacts the RTO on failover and performance of normal operations.
• Imperfect service discovery can lead to split-brains, which could be prevented by fencing.

I do not claim this whole discussion is completely mature, I just think it is better than what I read so far.  Also, I did not describe all the ways of implementing each of the five parts of a MySQL Master Failover Strategy, I probably forgot some, and new one will be invented.  This post can be considered a Request For Comment, so please share your thoughts in the comments below, or on the MySQL Community Slack (I am @jfgagne there), or via email (jfg DOT mysql AT gmail.com).

I am planning follow-up posts.  The two I have in mind so far are:

• Circular/ring replication: living in constant split-brain
• XtraDB Cluster, Galera and Group Replication: a partial way of doing fencing but not a full availability solution

Moreover, I did not cover how to make failing-over the master to a slave efficient, fast or simple.  This has partially been covered in my previous post Abstracting Binlog Servers and MySQL Master Promotion without Reconfiguring all Slaves.

Finally, I want to write a post about the Rules of MySQL Replication for explaining how to organize a MySQL replication topology for making sure failing-over the master to a slave does not have adverse consequences, including when dealing with replication filters, multi-source replication and other edge cases.

As you can guess, I am not done writing on this very complex subject...