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.
All of our production stuff is master-master MySQL replication. It’s pretty solid, gets you a place to do intense read-only queries, and also allows you to do other nifty stuff like online schema changes. I also like having two totally separate sets of datafiles, lest some corrupted data hork the InnoDB tablespace.
I started with heartbeat, but quickly ran away screaming. Some of it’s more intense features (STONITH) are needed for DRBD, but it’s a downright pain otherwise. That and it bit me in the ass a couple times. Now, I use keepalived to simply manage a VIP.
What do you mean by master master?
Are you using circular replication? Are you writing to the same databases on both servers simultaneously or did you split the databases in a more traditional active/active setup (with two VIPs?) with either server able to back up the other?
Yeah, circular replication with two hosts — each host is the replication master for the other. They are generally active/passive. I can (and do) have various background jobs that write to the ‘passive’ host, but this must be done with care.
Since MySQL replication is single threaded, lots of writing to both is sure to cause replication lag, which will make life painful.
Right. That’s basically what I’ve done except I used heartbeat (was relatively simple actually) and I haven’t added the circular dependency. If I do, it will be after I take down the legacy server. I’ll have to rebuild the slave anyways because I’m currently: legacy -> master -> slave, without log-slave-updates to the slave is likely out of sync already.
My only concern with circular replication is knowing if there was any corruption on the master if it crashes before I bring it back online.
I like leaving log-slave-updates on even if you don’t loop it. Makes patching things back up a little easier if you have to switch roles.
I don’t let MySQL start slave automatically….just like I don’t let MySQL start on boot. Events of that magnitude need a human touch:)
You might check out: http://code.google.com/p/mysql-master-master/