MySQL Warm Standby

I'm designing a new Railsapplication that needs to offer high availability. With Rails, we are already getting scalability out of the box.

Hello Jim.

We are considering using a Kemp LM1500 hardware load balancer to manage requests to our Rails application servers.

If you want true HA, you need TWO load balancers.

There will be a separate server running MySQL 5.0 and all Rails application servers will be accessing this. I've been considering a couple of different alternatives for MySQL fault tolerance. MySQL clustering seems too complicated and still not 100% bulletproof.

It also doesn't allow for schema changes while online. :frowning:

The other alternative is to create a MySQL Warm Standby using frequent transaction log backups and a monitor to detect when to fail over.

Yes. This is a good solution, and MySQL can replicate online in near real time. In many cases, you can also read-balance across the master and/or replicas as well.

We are considering using a Kemp LM1500 hardware load balancer to manage requests to our Rails application servers.

If you want true HA, you need TWO load balancers.

There will be a separate server running MySQL 5.0 and all Rails application servers will be accessing this. I've been considering a couple of different alternatives for MySQL fault tolerance. MySQL clustering seems too complicated and still not 100% bulletproof.

It also doesn't allow for schema changes while online. :frowning:

Actually it does... sort of... you just have to put it into "single node mode" and then make your change. But you can still serve requests while that happens... granted if you're site falls flat with a single node, then the net affect is that yes, you have to take it offline :confused:

At least, that's what our db guy does (or tells us he does :slight_smile:

-philip

Interesting. I picked up a book from MySQL Press on MySQL Cluster, and on page 9, it lists inability to change schema while online as a limitation of MySQL cluster.

I stand corrected, but am left wondering what that book meant by that comment. Perhaps this has been changed since the book was released?

It also doesn't allow for schema changes while online. :frowning:

Actually it does... sort of... you just have to put it into "single node mode" and then make your change. But you can still serve requests while that happens... granted if you're site falls flat with a single node, then the net affect is that yes, you have to take it offline :confused:

At least, that's what our db guy does (or tells us he does :slight_smile:

Interesting. I picked up a book from MySQL Press on MySQL Cluster, and on page 9, it lists inability to change schema while online as a limitation of MySQL cluster.

I stand corrected, but am left wondering what that book meant by that comment. Perhaps this has been changed since the book was released?

Hrm... that's a little vague, but I could see that being correct as well.

We have three front end nodes. When we need to make a change we put it into single node mode (i don't remember the exact command) which effectively disables two of the nodes. However that last one will still serve up requests.

So, the database is still available, but the "cluster" isn't really online..

If that makes any sense...