In the last 2 blog posts about High Availability for MySQL we have introduced definitions and provided a list of ( questions that you need to ask yourself before choosing a HA solution. In this new post, we will cover what is the most popular HA solution for MySQL, replication.
High Availability solution for MySQL: Replication
This HA solution is the easiest to implement and to manage. You basically need to setup MySQL replication between a master and one or more slaves. Upon failure of the master, one of the slaves is manually promoted to the master role and replication on the other slaves is re-adjusted to point to the new master. This solution works well with all the MySQL storage engines including MyISAM (NDB is a special discussed later) but it suffers from the limitation of MySQL replication. The main limitation, in term of HA, is the asynchronous design of MySQL replication which does not allow the master to be sure the slave has been updated before returning after a commit statement. There is a window in time where it is possible that a fully committed transaction has not been pushed to the slave(s) leading to data loss. Many large websites that are fine with some data loss rely on replication for HA and for read scaling.
In addition to hardware failure, the level of availability of this solution is affected by the availability of the MySQL replication link between the servers. Replication often break for various reasons and while replication is broken, there is no High-Availability. Also, the availability of this solution is affected by how much the slaves were behind the master when the outage occurred. So, if you want to have a good level of availability, you need a good monitoring and alerting system to quickly react to replication issue and you need a rather small write load so that the slaves do not lag behind the master too much. To maximize the level of availability, recovery should be automatic.
Apart of its simplicity, an HA solution based on replication as many interesting properties, no wonder it is so popular. First, if the application is well designed and has specific database handles for read and write operations, this HA solution can scales the read operations to a high level. Using the slaves for reads cause a second interesting side effect, the caches of the slaves are hot so failing over to a slave means no degraded performance associated with caches warm up. Finally, it is well known that with MySQL, altering a table means recreating the whole table and it is a blocking operations. Altering a large table may takes many hours. The trick here is to run the alter table on a slave and then, once done, we let the slave catch up with the master using the new table schema, we failover to the slave and repeat the alter table on the other server. Those online schema change are easier when a master to master topology is used.
The following figure summarize the simplest HA architecture using MySQL replication. All writes are going to the master while reads are spread between the master and the slave. Upon failure of the master, replication is stopped on the slave and all traffic is redirected to the slave which now handles reads and writes.
|Simple||Variable level of availability (98-99.9+%)|
|Inexpensive||Not suitable for high write loads|
|All the servers can be used, no idle standby||read scaling only if application splits reads from writes|
|Supports MyISAM||Can lose data|
|Caches on failover slave are not cold|
|Online schema changes|
|Low impact backups|
Automatic failover with replication
I already mentioned that for best HA levels, failover or recovery should be automatic. There are tools to manage automatic failover with replication like MMM, Flipper and Tungsten. Here, I will quickly describe the most popular one, MMM.
With MMM, you need to add a separate server, the Manager that, like the name imply, manages the availability of the MySQL service. A high availability solution based on MMM requires at the 2 MySQL servers configured in a Master to Master topology. Additional slaves can also be added. A MMM agent runs on all the MySQL servers and it is used to do OS level operations. The principle of operation of MMM is based on VIPs. There is one write VIP, where write operations are sent and as many read VIPs as the number of MySQL servers. For the write VIP, MMM monitors the state of the current master and, upon failure, try to kill all the connections to the failing server and transfer the write VIP to the other master. For the read VIPs, MMM monitors the state of the slaves and remove the read VIP of a slave if it has failed or is lagging behind the master by more than a defined threshold. One of the main limitation of MMM is its lack of fencing capability. It is important to stop all the connections to the failing master and if that server is not responding, maybe because of a network problem, a stonith device must be used to fence it. I am far from being an expert with MMM, other guys on my team are way better than me, but I heard that the MMM v1 code base had some deficiencies. MMM v2 is a complete rewrite that addresses some of the shortcomings of v1. Walter Heck from OpenQuery gave an excellent webinar on it recently.
The architecture of a highly available setup using MMM and Master-Master replication is presented on the figure below. Apart from the minimum requirement of two MySQL servers replicating each other, there is a third server, called the manager, that controls both MySQL server through an agent that is running on each server. The manager controls and monitors the state of the replication and assign virtual IPs for specific roles. There are one VIP where write operations are sent and two or more VIPs where read operations are sent. If replication on one of the MySQL servers lags behind too much, its read VIP will be moved to another server.
As a conclusion, replication can be used in many cases to build effective and scalable highly available solutions but it has some limitations. In my next blog post, I’ll present another HA solution build around Heartbeat and DRBD.