Mysql Administrator - HA

Availability is usually measured in percentages that indicate the amount of downtime per year. 

MySQL HA: So Many Options...

Traditional (async) MySQL replication Master-Slave or Master-Master Manual Failover Yes, this can be an HA solution. External manager frameworks / applications An alphabet soup of options: PRM, MHA, MMM, VIPs, keepalived, Pacemaker, Heartbeat

Non-traditional MySQL replication

MySQL Semi-sync replication (MySQL 5.5+) 
Tungsten Replicator
Galera (Percona XtraDB Cluster, MariaDB/Galera) 
MySQL Cluster (NDB) 

Non-MySQL replication
Shared storage – DRBD, Lustre, NFS, SANs, Other Esoterica – Clustrix, Xeround, etc. 

Consider three perspectives (often related)
Business 
Philosophical 
Technological

A primary objective of MHA is automating master failover and slave promotion within short (usually 10-30 seconds) downtime, without suffering from replication consistency problems, without spending money for lots of new servers, without performance penalty, without complexity (easy-to-install), and without changing existing deployments.

MHA also provides a way for scheduled online master switch: changing currently running master to a new master safely, within a few seconds (0.5-2 seconds) of downtime (blocking writes only).

MHA provides the following functionality, and can be useful in many deployments where requirements such as high availability, data integrity, almost non-stop master maintenance are desired.

• Automated master monitoring and failover

MHA has a functionality to monitor MySQL master in an existing replication environment, detecting master failure, and doing master failover automatically. Even though some of slaves have not received the latest relay log events, MHA automatically identifies differential relay log events from the latest slave, and applies differential events to other slaves. So all slaves can be consistent. MHA normally can do failover in seconds (9-12 seconds to detect master failure, optionally 7-10 seconds to power off the master machine to avoid split brain, a few seconds for applying differential relay logs to the new master, so total downtime is normally 10-30 seconds). In addition, you can define a specific slave as a candidate master (setting priorities) in a configuration file. Since MHA fixes consistencies between slaves, you can promote any slave to a new master and consistency problems (which might cause sudden replication failure) will not happen.

• Interactive (manual) Master Failover

You can also use MHA for just failover, not for monitoring master. You can use MHA for master failover interactively.

• Non-interactive master failover

Non-interactive master failover (not monitoring master, but doing failover automatically) is also supported. This feature is useful especially when you have already used a software that monitors MySQL master. For example, you can use Pacemaker(Heartbeat) for detecting master failure and virtual ip address takeover, and use MHA for master failover and slave promotion.

• Online switching master to a different host

In many cases, it is necessary to migrate an existing master to a different machine (i.e. the current master has H/W problems on RAID controller or RAM, you want to replace with faster machine, etc). This is not a master crash, but scheduled master maintenance is needed to do that. Scheduled master maintenance causes downtime (at least you can not write master) so should be done as quickly as possible. On the other hand, you should block/kill current running sessions very carefully because consistency problems between different masters might happen (i.e "updating master1, updating master 2, committing master1, getting error on committing master 2" will result in data inconsistency). Both fast master switch and graceful blocking writes are required. MHA provides a way to do that. You can switch master gracefully within 0.5-2 seconds of writer block. In many cases 0.5-2 seconds of writer downtime is acceptable and you can switch master even without allocating scheduled maintenance window. This means you can take actions such as upgrading to higher versions, faster machine, etc much more easily.

Traditional MySQL Replication

• PRO: It's cheap and well-understood. Both automated and manual failover options are available. Read-scaling is easy, and it works with any storage engine.

• CON: It's MySQL replication. Data drift is obscenely easy. Active-active replication topologies are brittle, and without some serious voodoo, one slave can still only have one master. 

Simple slave promotion.

Not too many moving parts unless you have multiple slaves that need to be re-homed.

Master-Master (1 active) w/Manual Failover
Change DNS, move a VIP, build failover awareness into your application, etc.
Still reasonably simple as long as writes go to one place.
Additional slaves can complicate matters; binary log coordinates differ between the master servers. 

M-S or M-M with automated failover
Keepalived: VIP management
HAProxy: L4 traffic director
Pacemaker or Hearbeat: VIP management
PRM (Percona Replication Manager):

• ●  Automated solution for master promotion and slave re-homing. Can be used manually, too, but not really designed for such.
  
• ●  Generally works very well, but because it's a Pacemaker resource agent, it's subject to the whims of the Pacemaker developers, which has been an issue recently with CentOS 6.4.
  
• ●  Does not make any guarantees about node consistency after a failover, but can be paired with semi-sync replication. 
  

●M-S / M-M with automated failover, continued MMM – Multi-Master Replication Manager

   - Agent-based system. Unreliable agent communication   - Not sure if it's even still actively being developed. Don't use.

●MHA – Master High Availability for MySQL
   - Tries very hard to ensure data consistency when promoting a new slave into the master role.
   - Can be dropped into an existing MySQL topology without extensive reconfiguration.

   - The preferred choice of Percona's Remote DBA team. MySQL Utilities

● New tools from Oracle designed to work with MySQL 5.6 and GTID-based replication. 
    Have yet to see this in the wild. 

Traditional replication is the most generic HA solution out there. If it runs fine on a single MySQL server, there's almost certainly a way to make it work reasonably well with replication. But...

• ●  Pacemaker/Corosync solutions can have trouble with high- latency networks.
  
• ●  None of the automated solutions handle extremely high load very well. MHA requires SSH connectivity; Pacemaker/Corosync solutions can lose messages and trigger spurious failovers.
  
• ●  Under the hood, it's still replication. Periodic data consistency checks (think pt-table-checksum) should be de rigueur.
  
• ●  Oracle's new MySQL Utilities are MySQL 5.6+ only. 
  

MySQL Cluster (NDB)

●Until recently, ran entirely in memory, but now disk-based tables are available (with some limitations).

Handles sharding and data redistribution automatically as nodes are added.
If your application is of the type that NDB was designed for (lots of small, simple writes, simple key-value lookups that don't require JOINs), it will likely outperform any other cluster/multi-machine solution in the MySQL ecosphere.

●But... Setup, tuning and configuration is extremely complicated; NDB-specific knowledge is required. 

  

Galera-based Solutions

Percona XtraDB Cluster (PXC), MariaDB + Galera
Synchronous replication

Can support true multi-master writing, reads are served up locally.
InnoDB-only (MyISAM support is experimental)
In the CAP theorem, Galera's focus is on consistency over availability.
Tremendous potential here; seems to solve a lot of the most common gripes about MySQL replication, but still in the process of maturing. 

Semi-sync replication
  - Can be paired with MHA or PRM.
   Probably won't work very well if all the slaves are in remote datacenters.
Tungsten
   - Doesn't appear to work with PXC.
   NDB CLuster 
 - Extremely fast for a specific class of applications (small writes, key-value lookups without JOINs)
 - Can achieve 99.999% uptime if configured properly. Setup, tuning, and troubleshooting are complex.

PXC / MariaDB+Galera
Good for InnoDB where all the tables have PKs. FK handling has had some issues recently.
Can be used over the WAN if the application or the end users can handle the increased latency at COMMIT time, but this can also be a deal-breaker.
A PXC cluster can automatically repair itself when a node drops out / returns; likewise, it can automatically expand the cluster if a new node joins.