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.
| Pros | Cons |
| 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.
Hi,
Thanks for this informative article.
I am confused between choosing master-master(MM) & master-slave(MS).
What is the difference between MM and MS if in both architectures, writes go only to master and reads can happen from both?
Also in MM, why do the two masters replicate each other when writes are happening on only one?
Hi Mahesh,
master-master and master-slave are very similar, master-master is basically 2 master-slave setups. Doing writes will no improve performance since both servers will need to perform the writes and will likely lead to collisions. The goal with the MM setup is to ease the failback.
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.
—
Please describe this a bit more. What does “failover to the slave” mean? How will master get synchronized with new slave scheme?
We have test servers, beta servers and evaluation servers along with the production DB server. Will I be able to use just one server as the monitor and group these servers? I mean a single manager should manage master-master DB server for test server. Then another group in the same manager managing another master-master DB pair for the beta server. Will this be possible? I do not want to waste an entire server for just the manager software.
Thanks.
Hi sv,
the manager process is very light so there is no problem managing more than one pair of servers from a single manager server. Another option is to use your monitoring/alerting server (like Cacti/Nagios) to host the manager process.
I work as an DBA where my company had 15-20GB of mysql database, we need performance to increase 500%.
currently we are using free mysql version thats comes under centos linux, we are had enough resource left, RAM, CPU in server.
My question is:
Does mysql enterprise edition helps us? How?
What are the other options we can use?
What is the difference between cluster and replication in databases?
-ashokgtm