title: Towards a more highly available MySQL link: https://onemoretech.wordpress.com/2013/10/07/towards-a-more-highly-available-mysql/ author: phil2nc description: post_id: 6446 created: 2013/10/07 10:13:29 created_gmt: 2013/10/07 14:13:29 comment_status: closed post_name: towards-a-more-highly-available-mysql status: publish post_type: post

Towards a more highly available MySQL

As those who visit this blog frequently know, I've recently been busy standing up WordPress sites using the HyperDB plugin with MySQL in a master-slave configuration. But let's be honest, having a multi-master backend would be a lot better. More on the quest for that below. I, along with my buddy Anil, did take a look at MySQL's Multi-Master replication Manager but in general neither of us were impressed. There was still a single point of failure and the its reliance on polling the nodes from that single point didn't offer much comfort to firedrill-adverse sysadmins like us. Enter MySQL-clone MariaDB with its recharged development effort that has resulted in the Galera Cluster, a multi-master replication solution. Overall Galera seems to be better thought-out than MMM and offers much better odds of actually getting back in business quickly in the event one of the master nodes fails. Getting Started with MariaDB Galera Cluster is a good starting point for learning how to set up a cluster. There's a terrific article on the Scout blog (Scout offers server monitoring products) entitled, From MySQL+MMM to MariaDB+Galera: A High Availability Makeover. According to the article, the most basic difference between MMM and Galera is that all write ops are confirmed by the cluster before being simultaneously committed to all masters. This prevents the update lags you'll see in very active master-slave environments, and avoids the single point of failure issues arising from MMM's reliance on polling and a "one server to control them all". There were a couple of paragraphs in the article that made me laugh out loud, like this one from MMM creator Alexy:

I’m the original author of MMM and I completely agree with you. Every time I try to add HA to my clusters I remember MMM and want to stab myself because I simply could not trust my data to the tool and there is nothing else available on the market to do the job reliably.

Best news of all is that all this stuff is available in enterprise Linux now. There are packages for all distributions, including the current Red Hat Enteprise Linux, for both the latest 10.x and older 5.x series on the MariaDB download pages (in fact the best way to handle installing MariaDB is to take some time with the Repository Configuration Tool for your distribution's package system (in the case of RHEL 5+ that would be yum, and even though MariaDB replaces MySQL in Fedora 19 forward you'll still need the MariaDB repo because Galera isn't included in Red Hat's repos). There are a number of important cautions to mention about using Galera clustering. The most important of which is that it is recommended that every cluster have 3 or more nodes to prevent a "split-brain" condition. As demonstrated in Percona Xtradb Cluster: Failure Scenarios with only 2 nodes, making clustering work with only 2 nodes requires that certain "safeties" be turned off that can, and often do, result in data stored in the 2 nodes becoming inconsistent.

Copyright 2004-2019 Phil Lembo