mysql replication and failover

Hi there,

I have been doing some research about how to deploy a
replication and
fail-over mysql solution with two servers (master/slave).

The replication part seems pretty easy to implement but the
fail-over
seems to be more complicated.

I have seen a couple of implementations that are based on
this:

. mysql is configured to do replication.
. the slave pings the master to see if it is alive
. if it is dead, the slave changes the dns information to
make
the master name point to its ip.
. when the master is back again the dns is changed back to
point
the master's ip.

I don't know what you guys think but I see tons of problems
with this
configuration, but the most important one is:

The replication solution is totally asynchronous, which
means that
the master could potentially commit information  before
those changes 
are also committed to the slave. This will end up with a
corrupted 
database.

The only real solution that I have seen so far is MySQL
clustering but
I don't have time, neither the resources to implement that
solution.

What do you guys think? Is MySQL clustering the only valid
solution
here?

Thanks,
David

_______________________________________________
% NYC*BUG talk mailing list
http://
lists.nycbug.org/mailman/listinfo/talk
%Be sure to check out our Jobs and NYCBUG-announce lists
%We meet the first Wednesday of the month

On 250506, 11:39, David Rio Deiros wrote:
> 
> What do you guys think? Is MySQL clustering the only
valid solution
> here?

Not really.  There is this new method, available only with
MySQL 5.1,
which enables you to have master/master replication.

http://www.onlamp.com/pub/a/onlamp/
2006/04/20/advanced-mysql-replication.html

It'a nice feature.

Ciao !
-- 

Massimiliano Stucchi, CTO & Director of Operations
WillyStudios.com - IT Consulting, Web and VoIP Services
stucchiwillystudios.com | Tel (+39) 0244417203 | Fax (+39)
0244417204
IT-20040, Carnate (Milano), via Carducci 9
								
_______________________________________________
% NYC*BUG talk mailing list
http://
lists.nycbug.org/mailman/listinfo/talk
%Be sure to check out our Jobs and NYCBUG-announce lists
%We meet the first Wednesday of the month

On Thu, May 25, 2006 at 11:20:31PM +0200, Massimiliano
Stucchi wrote:
> On 250506, 11:39, David Rio Deiros wrote:
> > 
> > What do you guys think? Is MySQL clustering the
only valid solution
> > here?
> 
> Not really.  There is this new method, available only
with MySQL 5.1,
> which enables you to have master/master replication.
> 
> http://www.onlamp.com/pub/a/onlamp/
2006/04/20/advanced-mysql-replication.html
> 
> It'a nice feature.

Thanks for the reply Massimiliano and sorry for the late
reply.

Finally I decided to implement a solution based on
replication only. The
reason is because the database is going to server reads most
of the 
time.

The article you sent is very interesting but there is
something that 
still don't understand. When you use the autoincrement
feature found
in mysql5, you're solving a problem but you are also
introducing another
one since the ids are totally different between servers.
That means
you have to make the application aware about it. 

Thanks,
David


_______________________________________________
% NYC*BUG talk mailing list
http://
lists.nycbug.org/mailman/listinfo/talk
%Be sure to check out our Jobs and NYCBUG-announce lists
%We meet the first Wednesday of the month

On 270506, 14:33, David Rio Deiros wrote:
> On Thu, May 25, 2006 at 11:20:31PM +0200, Massimiliano
Stucchi wrote:
> > On 250506, 11:39, David Rio Deiros wrote:
> > > 
> > > What do you guys think? Is MySQL clustering
the only valid solution
> > > here?
> > 
> > Not really.  There is this new method, available
only with MySQL 5.1,
> > which enables you to have master/master
replication.
> > 
> > http://www.onlamp.com/pub/a/onlamp/
2006/04/20/advanced-mysql-replication.html
> > 
> > It'a nice feature.
> 
> Thanks for the reply Massimiliano and sorry for the
late reply.

No problem.

> Finally I decided to implement a solution based on
replication only. The
> reason is because the database is going to server reads
most of the 
> time.

I see.

> The article you sent is very interesting but there is
something that 
> still don't understand. When you use the autoincrement
feature found
> in mysql5, you're solving a problem but you are also
introducing another
> one since the ids are totally different between
servers. That means
> you have to make the application aware about it. 

You have to make applications aware of multiple databases
they can
read/write to, but there's no compatibility problem for
what concerns
id's.

ID's are unique accross machines.  What you introduce is
some type of
randomization, in order no to have situations where a write
is done at
the same exact time on two different machines, replication
is
interrupted whoknowswhy, and you end up having two different
tuples with
the same id but different data.  I've experienced this
problem back in
the 3.23.x days with a master/master-like implementation
which lacked
this sort of mechanisms.

Ciao !
-- 

Massimiliano Stucchi, CTO & Director of Operations
WillyStudios.com - IT Consulting, Web and VoIP Services
stucchiwillystudios.com | Tel (+39) 0244417203 | Fax (+39)
0244417204
IT-20040, Carnate (Milano), via Carducci 9
								
_______________________________________________
% NYC*BUG talk mailing list
http://
lists.nycbug.org/mailman/listinfo/talk
%Be sure to check out our Jobs and NYCBUG-announce lists
%We meet the first Wednesday of the month

Massimiliano Stucchi wrote on Monday, May 29, 2006 9:46 AM:
> On 270506, 14:33, David Rio Deiros wrote:
> > On Thu, May 25, 2006 at 11:20:31PM +0200,
Massimiliano Stucchi
> > wrote: 
> > > On 250506, 11:39, David Rio Deiros wrote:
> > > > 
> > > > What do you guys think? Is MySQL
clustering the only valid
> > > > solution here?
> > > 
> > > Not really.  There is this new method,
available only with MySQL
> > > 5.1, which enables you to have master/master
replication.
> > > 
> > >
http://www.onlamp.com/pub/a/onlamp/2
006/04/20/advanced-mysql-replication.htm
l
> > > 
> > > It'a nice feature.
> > 
> > Thanks for the reply Massimiliano and sorry for
the late reply.
> 
> No problem.
> 
> > Finally I decided to implement a solution based on
replication
> > only. The reason is because the database is going
to server reads
> > most of the time.
> 
> I see.
> 
> > The article you sent is very interesting but there
is something that
> > still don't understand. When you use the
autoincrement feature found
> > in mysql5, you're solving a problem but you are
also introducing
> > another one since the ids are totally different
between servers.
> > That means you have to make the application aware
about it.

Absolutely.

It should be noted that the auto_increment "fix"
for master-master
replication in MySQL is only a small part of the problem. 
Master-master, or
"update anywhere" as some databases call it, is
quite a complex animal.
Without considerable application or database intelligence
for what's called
conflict resolution, subtle problems with your data can
develop.  This is
why, for instance, even the conflict resolution protocols of
the likes of
DB2 and Oracle are not always full proof.  With zero
application awareness,
either data inconsistencies or performancae issues arise. 
And there's mor
than auto_increments/sequences - anything based on unique
timestamps or the
ordering of timestamps become meaningless, for instance

Just remember that MySQL still uses async. replication with
no conflict
resolution, even with their patch for the auto_increments. 
Only Cluster
(which is a different animal completely) uses synchronous
replication.
Thus, if you're writing in more than one place with async.
replication, your
application has to keep on it's toes.

---
Hans Zaunere / President / New York PHP
   www.nyphp.org  /  www.nyphp.com


_______________________________________________
% NYC*BUG talk mailing list
http://
lists.nycbug.org/mailman/listinfo/talk
%Be sure to check out our Jobs and NYCBUG-announce lists
%We meet the first Wednesday of the month

Massimiliano Stucchi wrote on Monday, May 29, 2006 9:46 AM:
> On 270506, 14:33, David Rio Deiros wrote:
> > On Thu, May 25, 2006 at 11:20:31PM +0200,
Massimiliano Stucchi
> > wrote: 
> > > On 250506, 11:39, David Rio Deiros wrote:
> > > > 
> > > > What do you guys think? Is MySQL
clustering the only valid
> > > > solution here?
> > > 
> > > Not really.  There is this new method,
available only with MySQL
> > > 5.1, which enables you to have master/master
replication.
> > > 
> > >
http://www.onlamp.com/pub/a/onlamp/2
006/04/20/advanced-mysql-replication.htm
l
> > > 
> > > It'a nice feature.
> > 
> > Thanks for the reply Massimiliano and sorry for
the late reply.
> 
> No problem.
> 
> > Finally I decided to implement a solution based on
replication
> > only. The reason is because the database is going
to server reads
> > most of the time.
> 
> I see.
> 
> > The article you sent is very interesting but there
is something that
> > still don't understand. When you use the
autoincrement feature found
> > in mysql5, you're solving a problem but you are
also introducing
> > another one since the ids are totally different
between servers.
> > That means you have to make the application aware
about it.

Absolutely.

It should be noted that the auto_increment "fix"
for master-master
replication in MySQL is only a small part of the problem. 
Master-master, or
"update anywhere" as some databases call it, is
quite a complex animal.
Without considerable application or database intelligence
for what's called
conflict resolution, subtle problems with your data can
develop.  This is
why, for instance, even the conflict resolution protocols of
the likes of
DB2 and Oracle are not always full proof.  With zero
application awareness,
either data inconsistencies or performancae issues arise. 
And there's mor
than auto_increments/sequences - anything based on unique
timestamps or the
ordering of timestamps become meaningless, for instance

Just remember that MySQL still uses async. replication with
no conflict
resolution, even with their patch for the auto_increments. 
Only Cluster
(which is a different animal completely) uses synchronous
replication.
Thus, if you're writing in more than one place with async.
replication, your
application has to keep on it's toes.

---
Hans Zaunere / President / New York PHP
   www.nyphp.org  /  www.nyphp.com


_______________________________________________
% NYC*BUG talk mailing list
http://
lists.nycbug.org/mailman/listinfo/talk
%Be sure to check out our Jobs and NYCBUG-announce lists
%We meet the first Wednesday of the month