0

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.

2 Answers 2

3

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.

1
  • +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. Nov 4, 2009 at 11:34
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .