[nycbug-talk] mysql replication and failover

[Hans Zaunere]

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/2006/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