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 but 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. We'll then progress on to evaluating the solutions to provide high availability through automated failover such as MMM & MHA, solutions to provide high availability through a cluster suite such as Heartbeat and Pacemaker (including the Percona-PRM resource agent), and on to solutions to provide high availability through synchronous multi-master clusters such as MySQL Cluster and Galera.
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.
MySQL statement based replication is widely used, yet error prone. It is easy to end up with slaves having different data from the master (non-deterministic functions).
MySQL 5.1 row based replication was an improvement on the above, but is still asynchronous. This means you can actually lose committed data on a failure - not desirable for a high availability solution.
MySQL 5.5 introduces semi-sync replication, an improvement over MySQL asynchronous replication. The main drawback remaining here is mostly in the complexity and poor manageability, such as lack of a global transaction id.
MySQL Cluster offers synchronous replication with the NDB engine, but is is not widely used as it replaces the InnoDB engine and gives very different behavior (such as performance).
Disk-level DRBD replication then became popular since it offered 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.
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... and some MySQL specific ones like MMM, MHA, and Percona-PRM.
Henrik and Ben worked together on MySQL installations at Nokia. Ben recently started working at Pythian. Between them they have evaluated and benchmarked pretty much all of the above alternatives, and used many of them in production too.
10 April 09:30 - 12:30 @
Ben currently works as a MySQL DBA at The Pythian Group, previous to this he was a DBA at Nokia, where he provided MySQL support for Mobile Services such as Music, Maps, and Messaging.
Senior Performance Architect, Nokia
Henrik works as Senior Performance Architect at Nokia, where he is mostly interested in the performance of MySQL and Galera, but also other SQL and NoSQL databases. He previously worked as Chief Operating Officer at Monty Program and as a Sales Engineer at Sun Microsystems, selling MariaDB, MySQL and MySQL Cluster technologies while being closely in touch with their respective development teams. He is the author of the book "Open Life: The Philosophy of Open Source (http://openlife.cc), fan of Open Source in general and contributor to Drupal in particular. Since 2010 Henrik co-organizes the MySQL Community Awards, a tradition which will again take place as part of the 2013 MySQL Conference and Expo (http://openlife.cc/blogs/2012/february/mysql-hall-fame).