Mysql Failover Recommendations

Hi all,

I'm about to set master-master replication and would like to get
recommendations as to the best method for getting my applications to failover
when needed.

Here are my options, so far:

MySQL Proxy http://forge.mysql.com/wiki/MySQL_Proxy
This seems like the obvious choice, but I've not been able to find a cookbook
recipe to simply pick the "closest" server and fail over to the next server if
the first one dies. I think I'm missing something.

Ha Proxy http://haproxy.1wt.eu/#fiab
Seems like much more than I need...

Pen http://siag.nu/pen/
This one might be too http-centric. Would it work for Mysql?

Any comments/pointers would be most appreciated.

--

Take care and have fun,
Mike Diehl.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Mysql Failover Recommendations

----- Original Message -----
> From: "Mike Diehl"
>
> I'm about to set master-master replication and would like to get
> recommendations as to the best method for getting my applications to
> failover when needed.

I should already be in bed, but as a very brief reply: I find automatic failover of MySQL servers to be a thoroughly bad idea. Stick to manual failover, regardless of what mechanism you implement. If you absolutely must go automatic, be damn sure the supposedly failed node is dead and buried before switching - kill it yourself if you have to (think IP-enabled power switches).

> MySQL Proxy http://forge.mysql.com/wiki/MySQL_Proxy

I've seen many people rave, but my experience is close to yours: if you want something, figure it out yourself.

> Ha Proxy http://haproxy.1wt.eu/#fiab

Works very well as a general solution, but may be a bit of overkill. Have a look at Ultramonkey for good documentation and setups.

> Pen http://siag.nu/pen/

Unknown to me.

People also rave about MMM, haven't much looked at it, yet.

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Mysql Failover Recommendations

I received a recommendation to look at MySQL-MMM http://mysql-mmm.org/.

However, my two servers are in different parts of the country, so I can't use
any IP-based fail over solution. I truly need a proxy that listens on
127.0.0.1 and forwards to either the "local" server, of if needed, to another
"remote" server.

My system isn't under heavy load, but it simply can not be down... if at all
possible.

That said, Mysql Proxy and HA Proxy seem to be the front-runners.

Any additional comments are certainly welcome.

Mike.

On Monday 13 June 2011 3:16:26 pm Johan De Meersman wrote:
> ----- Original Message -----
>
> > From: "Mike Diehl"
> >
> > I'm about to set master-master replication and would like to get
> > recommendations as to the best method for getting my applications to
> > failover when needed.
>
> I should already be in bed, but as a very brief reply: I find automatic
> failover of MySQL servers to be a thoroughly bad idea. Stick to manual
> failover, regardless of what mechanism you implement. If you absolutely
> must go automatic, be damn sure the supposedly failed node is dead and
> buried before switching - kill it yourself if you have to (think
> IP-enabled power switches).
>
> > MySQL Proxy http://forge.mysql.com/wiki/MySQL_Proxy
>
> I've seen many people rave, but my experience is close to yours: if you
> want something, figure it out yourself.
>
> > Ha Proxy http://haproxy.1wt.eu/#fiab
>
> Works very well as a general solution, but may be a bit of overkill. Have a
> look at Ultramonkey for good documentation and setups.
>
> > Pen http://siag.nu/pen/
>
> Unknown to me.
>
> People also rave about MMM, haven't much looked at it, yet.

--

Take care and have fun,
Mike Diehl.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Mysql Failover Recommendations

--20cf303dd2fa081dc904a59ead2a
Content-Type: text/plain; charset=ISO-8859-1

On the contrary; people say less then good things about MMM even it's
creator;
http://openlife.cc/blogs/2011/may/different-ways-doing-ha-my sql
http://www.xaprb.com/blog/2011/05/04/whats-wrong-with-mmm/

If you want to use master<->master replication consider that you're likely
to have to manage failover manually. I utilise VIP relocation to avoid
making app changes. You will also need to ensure what you have on each
master is the right data. Maatkit tools will allow you to keep your tables
sync'd so that should you need to perform a failover your data is
consistent. As Johan mentions automatic failover can cause it's own issues.
MySQL Proxy (if I'm not mistaken) remains in Alpha version so not always a
great idea for a production system unless you're happy pulling Lua code
apart? I have also seen MySQL 5.5's semi-synchronous replication used to get
closer to a consistent dataset `out of the box`.

It's a good solution but not without maintenance overhead.

On Mon, Jun 13, 2011 at 10:16 PM, Johan De Meersman wrote:

>
>
> ----- Original Message -----
> > From: "Mike Diehl"
> >
> > I'm about to set master-master replication and would like to get
> > recommendations as to the best method for getting my applications to
> > failover when needed.
>
> I should already be in bed, but as a very brief reply: I find automatic
> failover of MySQL servers to be a thoroughly bad idea. Stick to manual
> failover, regardless of what mechanism you implement. If you absolutely must
> go automatic, be damn sure the supposedly failed node is dead and buried
> before switching - kill it yourself if you have to (think IP-enabled power
> switches).
>
> > MySQL Proxy http://forge.mysql.com/wiki/MySQL_Proxy
>
> I've seen many people rave, but my experience is close to yours: if you
> want something, figure it out yourself.
>
> > Ha Proxy http://haproxy.1wt.eu/#fiab
>
> Works very well as a general solution, but may be a bit of overkill. Have a
> look at Ultramonkey for good documentation and setups.
>
> > Pen http://siag.nu/pen/
>
> Unknown to me.
>
> People also rave about MMM, haven't much looked at it, yet.
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=eroomydna@gmail.com
>
>

--20cf303dd2fa081dc904a59ead2a--