High Availability in MySQL

Henrik Ingo (10gen)
Data
Location: D139/140
Average rating: ***..
(3.75, 8 ratings)
Slides:   1-PDF    2-ODP    3-ODS    4-FILE 

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, and 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?

The goal of this tutorial is for students to understand the tradeoffs inherent in choosing between the different options for database high availability. 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.

QUESTIONS for the speaker?: Use the “Leave a Comment or Question” section at the bottom to address them.

Photo of Henrik Ingo

Henrik Ingo

10gen

Henrik Ingo is active in the MySQL and Drupal communities. Professionally he has worked at MySQL AB, Sun and Monty Program on MySQL and MariaDB and prior to that in the Finnish mobile cluster on Symbian, Maemo and VoIP projects. He currently works for Nokia as a MySQL and performance architect. He is the author of the book “Open Life: The Philosophy of Open Source” (http://openlife.cc) a book on open source community ethics and business models.

Comments on this page are now closed.

Comments

07/11/2013 12:18pm PDT

Particularly interested in methods for MariaDB as well, such as Galera clustering replicating over local and cloud servers.

Sponsors

Sponsorship Opportunities

For information on exhibition and sponsorship opportunities at the conference, contact Sharon Cordesse at (707) 827-7065 or scordesse@oreilly.com.

Contact Us

View a complete list of OSCON contacts