Choosing a MySQL High Availability Option

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