How do I setup 2 MySql databases with failover?

April 4, 2008 3:57 AM

How do I setup 2 MySql databases with failover?

Asking for a friend: “I want to setup 2 MySql databases with failover. I am thinking about doing this with both instances of the RDBMS pointing to a shared drive. (a SAN). The idea being to have one RDBMS instance started and one stopped. If the active RDBMS failed we would then like to automatically stop that instance, and start the other RDBMS which would now point to the same mount point on the SAN, we should now have failover without the cost/ complication of replication. I know that Oracle offers similar functionality, but we are constrained to MySql. Does anyone have any links to people who have tried this. (underlying OS will probably be Redhat). I would also welcome comments on the sanity of this strategy. Thanks”

posted by blacksky to computers & internet (5 comments total)

The fundamental problem I can think of is that you don't know in advance why you had to fail over, and thus you don't know in advance that you can trust the data on that shared storage. That'd be the deal-killer to me.

Other than that (and I really wouldn't call MySQL replication complicated; it's basically a matter of following the steps in the manual) this will get answers a lot faster on the MySQL mailing lists.
posted by mendel at 5:17 AM on April 4, 2008

* the data in the database
* the disks
* the server
* the SAN

Your scheme only protects you against failure of the server. You'll probably buy two of the same server running the same OS revs, with all the same bugs. The most likely failure cause is that you (or a developer, an RDBMS bug, or a DBA) will delete/corrupt the data by accident. The second most likely failure is that the RAID croaks from neglect.

How many servers have you thrown away because they've just crapped out? If you're buying a decent (IBM/Dell/HP) server with some built-in redundancy, and you monitor it, it might never go down unexpectedly due to hardware issues (before you get rid of it because newer stuff is so much faster).
posted by popechunk at 5:31 AM on April 4, 2008

I am thinking about doing this with both instances of the RDBMS pointing to a shared drive.

But what about when the shared drive fails?

If you've got a few machines, why not look into setting up a MySQL Cluster? My understanding is that you need at least three machines to do it, but this is perhaps a more sane way to do it.
posted by fogster at 7:25 AM on April 4, 2008

Seconding the MySQL cluster. Three machines is the absolute minimum - do one of those for learning how to configure the API, NDB and manager nodes, but spec out something a little larger for production use.

You can also do a master-slave configuration with only 2 machines, though you'd need something to repoint your apps to the slave system if the master croaked. You might be able to hand-roll something with wackamole or dig around on the mysql forums.
posted by jquinby at 7:51 AM on April 4, 2008

I just implemented this using Heartbeat and DRBD. Works like a charm. It's even officially supported by the commercial MySQL people:

http://www.mysql.com/products/enterprise/drbd.html
posted by insyte at 1:17 AM on April 5, 2008