Evaluating MySQL High Availability alternatives
Like many things in MySQL, choosing a high-availability solution for MySQL has traditionally been an exercise in tradeoffs. There are many alternatives to choose from. Many good enough - none perfect?
In this session we'll look at all of these alternatives, we'll present an unbiased view on solutions to increase MySQL availability such as native MySQL Replication, Tungsten Replicator, and DRBD, and on to solutions to provide high availability through synchronous multi-master clusters such as MySQL NDB Cluster and Galera Clustering. Hand in hand with your replication solution, you also need to decide on using (or not using) some automated failover framework such as, mysqlfailover, MMM, MHA, Tungsten Enterprise, Heartbeat, Pacemaker (including the Percona-PRM resource agent) or will you rather go for some form of load balancing?
Students are provided with a spreadsheet they can use for their own evaluations, so that everyone can rate and weigh different features according to ones own needs and preferences. The spreadsheet is usable also for real-world evaluations.
We will cover all of the following:
MySQL built-in replication has evolved over a decade and is by far the most widely used HA alternative. You could say the internet has been built on MySQL replication. Over the years improvements have been developed, and we will cover the benefits of statement based replication, row based replication and semi-synchronous replication. MySQL 5.6 includes several improvements for replication, from checksums that guarantee integrity, to a solution for multi-threaded slaves and most importantly, improved manageability through Global Transaction ID.
Another limitation in MySQL replication is that you can only have one master - that is, one replication source - per slave server. MariaDB 10.0 introduces multi-source replication which removes this limitation, opening up the door for new interesting replication topologies.
MySQL NDB Cluster is a separate product that offers synchronous replication with the NDB engine, but it is not widely used as it replaces the InnoDB engine and therefore gives different behavior (such as performance).
Disk-level DRBD replication has become popular since it offers synchronous replication with no data-loss on failover. The tradeoff with DRBD then is that you only have a cold standby, and the recovery time during a failover can be minutes - again not perfect for today's online requirements.
3rd party solutions like Tungsten and Galera bid to improve on the available solutions. Tungsten brings you global transaction id's and other user friendliness, and more throughput with parallel and multi-master replication streams. Galera adds to that list also synchronous replication, being the first to introduce true active-active clustering for InnoDB.
In most of the above solutions complexity is also increased by the need for some external cluster management software, such as Heartbeat, Pacemaker, Red Hat Cluster, Solaris Cluster... or a MySQL specific one like mysqlfailover, MMM, MHA, Percona-PRM or Tungsten. Load balancers are a good and user-friendly fit for the active-active replication technologies, but are perhaps not yet as commonly understood as is the failover paradigm.
Finally, with the arrival of cloud computing it is easy for anyone with a credit card to have servers on 3 different continents. Geographical redundancy - aka disaster recovery - is slightly different due to the network latency and quality issues so this brings back the discussion of asynchronous vs synchronous replication from a new perspective.