I’ve amassed enough mysql databases that it’s time there should be some high availability. Note that this isn’t a single huge database, it’s a pile of wordpress, request-tracker, mediawiki, etc databases. Performance isn’t the goal here, it is automatic failover in case of impending doom.
I happen to have an iSCSI san, but in the efforts of simplicity I’m looking at Heartbeat+DRBD or Heartbeat+Replication.
Most tutorials, and comments from colleagues lean towards using Heartbeat+DRBD. There is good discussion of the two, and more recent followup regarding when to use DRBD. There’s a nice little table at the bottom of this page. If you dig deeper, there are respectable comments about using what’s appropriate to the situation, the exercise of which is left up to the reader.
The problem is that mysql defaults to using MyISAM as the storage engine, which lacks a transactional journal. When your primary host crashes and your secondary host comes up, unless there’s a journal to replay you’re just assuming everything isn’t corrupt without some kind of through consistency check. Which sounds time consuming. So switch all your tables to a transactional storage engine like InnoDB?
Replication has both a slave IO and a SQL process running, which I believe avoids this, since the replication slave isn’t going to run an incomplete SQL statement if the master dies while sending it to the slave it is dropped. Which leaves you possibly behind the master, but consistent.
So I’m going to try to configure heartbeat with mysql running replication between two guests. The best information I’ve found so far is from Charles Bennington. I’ll post a followup when I’m done with that project.