4

Question about using DRBD to provide HA for MySQL.

I need to be sure that my backup MySQL instance is always going to be in a functional state when the failover occurs. What happens, for example, if the primary dies part way through committing a transaction?

Are we going to end up with data copied to the secondary that mysql can't handle? Or, what if the network goes away while the two are syncing, and not all of the data makes it across.

It seems like it's possible to get into a state where incomplete data on the secondary makes it impossible for mysql to start up and read the database.

Am I missing something?

tdimmig
  • 283
  • 2
  • 6

5 Answers5

11

It depends, naturally, on the nature of the failover. It also sounds like you already know the answer to your question.

DRBD is, fundamentally, network RAID mirroring. Blocks in -> blocks out. You can run synchronously or asynchronously, depending on your latency requirements. Which of these you choose tremendously affects whether your replica is crash-consistent or not.

Reduced to that level, your question becomes: "what happens when MySQL starts up and it reads data files?" Either your data is well-formed and quiesced, and it starts without a hitch, or it's crash-consistent, and you might have consistency issues. (There's also the possibility that you have on-disk corruption, of course, and this can also be a problem with DRBD, especially if you somehow end up with a split-brain scenario.) Usually, it can recover itself by replaying logs if you're using a transactional engine, but sometimes you will have more serious issues. This is as true with DRBD as with other shared block storage, like a shared SAN volume or (heaven forbid) database files on NFS.

Hypothetically, an ACID-compliant database should always recover gracefully from incomplete transactions. In practice, and especially with some MySQL versions, this isn't always the case (largely because MySQL doesn't have the greatest legacy of ACID compliance, though things have improved in recent years). Keeping frequent backups is always a sensible thing to do.

There is no way to ensure that any high-availability system will always continue working on a failover. The best you can do is make the right decisions when architecting your HA solution, and test the crap out of them to validate your assumptions about how it's going to behave when things go wrong.

In your case, you may want to consider a standby slave in case you run into a consistency issue on the master's disk. It takes manual work to promote it, of course, but at least you won't be restoring hours- or days-old data.

jgoldschrafe
  • 4,385
  • 17
  • 18
  • This is a very good answer (+1), provided one avoids MyISAM like the bubonic plague and only use InnoDB. Ijhust answered a similar question like this for using DRBD with PostgreSQL : http://dba.stackexchange.com/a/27123/877 – RolandoMySQLDBA Oct 18 '12 at 16:31
1

I don't think DRBD is the right solution here.

Depending on your work load you probably want one or a combination of below

  • Master - slave replication
  • Master - Master
  • Master - Master with slaves
  • MySQL cluster

The first is fairly trivial setup wise the second has a few caveats like Split brain, STONITH (Shoot The Other Node In The Head) amongst others.

This can be a complex topic and I recommend you research and Test in depth for your intended use. There are plenty of guides around for each of them.

daxroc
  • 274
  • 1
  • 7
  • MySQL cluster is not an option, I can't use the ndbcluster engine as it doesn't support foreign keys. I am looking to provide HA for an existing product, and re-designing the database to eliminate the need for foreign keys is not an option. Further, I need a setup with exactly 2 servers. I need to provide the best HA solution available with only 2 servers involved. Master-slave replication is the other solution I've been experimenting with. – tdimmig Oct 15 '12 at 21:26
  • I also need the ability to failback (fallback?) to the primary when it becomes operational again after a failure. Is that the purpose of the master-master configuration? – tdimmig Oct 15 '12 at 21:28
  • MySQL cluster might not be that far away from FK support https://blogs.oracle.com/MySQL/entry/mysql_cluster_7_3_labs – daxroc Oct 15 '12 at 21:28
  • Master Master replication would provide active high availability, where Master slave, wouldn't you would go to read only when the master failed, tho you could manualy fail the writes over. – daxroc Oct 15 '12 at 21:30
  • I've read about the FK support in 7.3, but I can't base my solution on a preview version and unfortunately 7.3 isn't officially released yet. Thanks for the info, I'll be reading more about MySQL replication to see if I can use it to solve my problem. I'm marking jgoldschrafe's answer as the accepted answer as he directly answered what I asked, but thank you for seeing the basis for the question and providing more information. – tdimmig Oct 15 '12 at 21:38
1

If you have control over the application code you can use MySQL Galera synchronous replication instead of DRBD. Galera has the requirement of odd number of cluster node members preferably at least three so majority vote wins who was correct data. You can augment MySQL Galera with HAProxy. So on each web brick you run HAProxy which then connects and checks that the MySQL servers are alive.

Here are some of the limitations http://www.codership.com/wiki/doku.php?id=limitations

Christian
  • 317
  • 1
  • 2
  • 8
  • This answer is great. It is worth nothing that you can just install Percona XtraDB Cluster. All the Installation headaches are taken care of because the binaries are Galera-aware. +1 !!! – RolandoMySQLDBA Oct 18 '12 at 16:33
1

If you

  • Run DRBD in synchronous mode (I think mode C?)
  • Use STONITH (fencing so that when DRBD elects a node it can shutdown the other node via a 'out of bounds' mechanism. (I.e APC smart power strip, lights out, drac, etc). This ensures there will only be one 'master' possible.
  • verify your drives/ RAID controller aren't lying about actually writing to disk. (Or they have appropiate battery backed cache)
  • thoroughly test all failure modes. (Power, network, dumb administrator command, dumb application)

Then you can reasonably sure your database is highly available. In your example, if it fails in the middle of the transaction it will be aborted and your application should hopefully retry and should hopefully be able to connect to your second node which should hopefully have a consistent copy of the data (because all writes are synchronously written to both nodes before returning to the database that it has been written).

David
  • 491
  • 4
  • 2
0

I tried DRBD a number of years back, but had many problems after a failover.

I removed DRBD from the picture by moving all the data & logs to a separate drive array connected via dual SAS controllers. We use an IBM DS-3525 for this. What is good about this setup is the secondary system is always connected, just does not have the partition mounted. I used Corosync to control the fail-over. When the primary comes back, Corosync shuts MySQL down, un-mounts the partitions, remounts them on the master, starts MySQL back up. Even if the master machine died in the middle of a transaction, InnoDB would recover.

Drive arrays run about $15-20K in this range. If you take into account that you need 2 of everything (not to mention that you need equivalent hardware per node), the costs of an array are well justified. Another benefit of an Drive Array is speed. In my case, I use the Multi-path drivers so the systems can use both controllers at the same time. The throughput compared to an internal raid is usually much higher.

Christian mentioned Galera. Check out Percona Cluster. It uses Galera, and is a very promising addition to bring MySQL up a notch in reliability.

gmck
  • 132
  • 1