# MySQL Warm Standby **URL:** \ **Category:** Rails\ **Created:** [October 31, 2006, 6:25pm UTC](https://www.ruby-forum.com/t/mysql-warm-standby/74164 "2006-10-31T18:25:24Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [October 31, 2006, 6:25pm UTC](https://www.ruby-forum.com/t/mysql-warm-standby/74164/1 "2006-10-31T18:25:24Z")
I’m designing a new Railsapplication that needs to offer high availability. With Rails, we are already getting scalability out of the box. We are considering using a Kemp LM1500 hardware load balancer to manage requests to our Rails application servers. 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. The other alternative is to create a MySQL Warm Standby using frequent transaction log backups and a monitor to detect when to fail over. Has anyone done something like this before and if so, can you offer any advice? Regards, ---
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [November 7, 2006, 10:49am UTC](https://www.ruby-forum.com/t/mysql-warm-standby/74164/2 "2006-11-07T10:49:33Z")
> Warm Standby using frequent transaction log backups and a monitor > to detect when to fail over. > > Has anyone done something like this before and if so, can you offer any > advice? We’re using their NDB cluster right now. It works pretty well, but has some big caveats in terms of overall DB size, row size, limitations in numbers of inserts/updates per transaction, etc… I think if Rails supported the concept of a write\_db and a read\_db we would have simply gone with a single master with multiple slaves, but it doesn’t so we didn’t. If one server is enough for your needs, I would simply setup another server as a slave, then use whatever is necessary to cut over when the master fails. -philip ---
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [November 7, 2006, 10:49am UTC](https://www.ruby-forum.com/t/mysql-warm-standby/74164/3 "2006-11-07T10:49:35Z")
On Oct 31, 2006, at 9:25 AM, Jim Dandy wrote: > 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. ☹ > 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. – – Tom M., CTO – Engine Y., Ruby on Rails Hosting – Reliability, Ease of Use, Scalability – (866) 518-YARD (9273) ---
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [November 7, 2006, 10:49am UTC](https://www.ruby-forum.com/t/mysql-warm-standby/74164/4 "2006-11-07T10:49:37Z")
> > 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. ☹ 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 😕 At least, that’s what our db guy does (or tells us he does 🙂 -philip ---
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [November 7, 2006, 10:49am UTC](https://www.ruby-forum.com/t/mysql-warm-standby/74164/5 "2006-11-07T10:49:42Z")
On Oct 31, 2006, at 10:07 AM, Philip H. wrote: > > > alternatives for MySQL fault tolerance. MySQL clustering seems too > > > then the net affect is that yes, you have to take it offline 😕 > > At least, that’s what our db guy does (or tells us he does 🙂 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? – – Tom M., CTO – Engine Y., Ruby on Rails Hosting – Reliability, Ease of Use, Scalability – (866) 518-YARD (9273) ---
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [November 7, 2006, 10:49am UTC](https://www.ruby-forum.com/t/mysql-warm-standby/74164/6 "2006-11-07T10:49:44Z")
> 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…