Choosing a MySQL High Availability Option

From AgileApps Support Wiki
Revision as of 02:18, 19 March 2014 by imported>Aeric
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

MySQL can be configured for High Availability using the following schemes supported by MySQL:

MySQL Replication
Replication provides data safety. It also lets you run reports and backups from the secondary server, reducing the load on the primary server and improving the response time seen by users. There is some exposure to data loss, since recently-seen data may not have been replicated when a failover occurs--but on the other hand, transactions don't incur the lag time added by serialized (synchronous) replication of data.
MySQL Replication + Heartbeat
Using Linux HA capabilities, MySQL can be configured for automatic IP-address failover.
Learn More: http://dev.mysql.com/doc/refman/5.5/en/ha-overview.html
DRBD + MySQL Heartbeat
While the previous two schemes do statement-based replication, Distributed Replicated Block Devices (DRBD) does synchronous replication of modified disk blocks. The advantage is consistently fast failover times. And because it is synchronous, incoming data is virtually guaranteed to be replicated, for maximum protection against data loss. The disadvantage is that traffic-handling volume may be decreased, due to the serial writes (replication must complete before the original data write can finish). This mechanism is also expensive to set up and maintain.
Learn More: http://dev.mysql.com/doc/refman/5.5/en/ha-drbd.html
MySQL Replication + DRDB + MySQL Heartbeat
Replication and DRDB are not mutually exclusive. They can be used in combination with one another.

Comparing the Options

The following table compares those replication schemes:

Requirements Replication Replication + Heartbeat DRBD + Heartbeat Replication + DRBD + Heartbeat
Availability
Automated IP failover No Yes Yes Yes
Automated database failover No No Yes Yes
Typical failover time User/script-dependent Varies < 30 seconds < 30 seconds
Automatic resynchronization of data No No Yes Yes
Geographic redundancy support Yes Yes No Yes
Scalability
Supports Read-intensive applications Yes Yes No Yes
Supports Write-intensive applications No No Yes Yes
Maximum number of nodes per group One master, multiple slaves One master, multiple slaves One active (primary), one passive (secondary) node
Maximum number of slaves Unlimited (reads only) Unlimited (reads only) One (failover only)
Note: This table is based on information obtained from http://dev.mysql.com/doc/refman/5.5/en/ha-overview.html

MySQL Cluster Not Supported

MySQL Cluster has functionality limitations that prevent it from working with the platform. Some of the more significant limitations include:

  1. Limitation on number of objects in an instance (around 20,000)
  2. Temporary tables are not supported
  3. Limitations on Index size
  4. Savepoint and Rollback to Savepoint are not supported
  5. Replication is not supported
  6. Online schema changes are not supported