Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Does MySQL come with a built-in automatic failover? Or, are there load-balancers out there that can "detect" whether the database is alive...and if not...shoot it to the failover database?

I know basic MySQL, but do not know the best way to design failover for one master DB.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

MySQL alone doesn't provide the failover mechanism. This is actually quite difficult problem to solve universally, so it's best to solve it on a case-by-case basis with specific tools.

As you don't mention which OS you're using, so I'll just throw some examples how it could go with Linux. When you have a regular replication set up, you might want to take look at Linux HA project for the tools to make the failover happen. With these, you can promote the slave DB when the master fails.

Another possibility is to use DRBD, which would make the replication happen at the storage level, so MySQL wouldn't actually know there's replication going on. Problems with this approach are similar to the ones with regular replication, your primary and master must be aware what the situation is. For this, Linux HA tools would be the best choice.

MySQL also has clustering features, but they will require at least three servers, impose some limitations for the data model. With small databases it's not really worth the trouble.

On top of these, you'll want to use some kind of connection pooling. It'll make the failover transparent for clients, as they won't have to know which database is actually doing the work.

share|improve this answer
    
+1 for Linux-HA (heartbeat) but be warned DRBD is a pain in the ass if you're synching over non-dedicated links (latency is the issue). In most cases, the built-in replication features of MySQL are best in my opinion. –  François Feugeas Nov 4 '09 at 11:34

MySQL can be used with a replicated strategy, but last time I looked the support for it wasn't as good as other Databases. You need to look into MySQL replication to read up on this.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.