List:Replication« Previous MessageNext Message »
From:<list Date:October 27 2011 7:00pm
Subject:Re: Master-Master -> duplicate entry
View as plain text  
Rick,

Very good point.  We have been testing with four nodes, two in each data
center about 100 miles apart.  We own our own network and have dedicated
10gb links (we're spoiled), so latency is never an issue.  I spoke with a
Galera user who has a four node system stretched between Salt Lake City
Utah, Atlanta Georgia , London UK, and Amsterdam Netherlands hosting a
wordpress blog.  He had to account for some latency in timeouts settings,
but told us that it's working really well for synchronous write operations.
The replication Galera uses is called 'write set replication' or wsrep,
which uses certificates (based on Fernando Pedone's Ph.D. thesis about
certification-based conflict resolution) instead of a two phase commit,
drastically reducing the overhead of replication on the backend.  The draw
back is the more nodes you add, the more latency as you have to wait for
everyone in the cluster to apply the changes.  The big positive is that
reads are done locally, so you can house the data right next to the
application if you're willing to deal with possibly slower write
operations.  It also only supports innodb right now, and should support
myisam someday in the future.  It's certainly not for everyone, but still a
really cool idea.

On Thu, 27 Oct 2011 10:56:59 -0700, Rick James <rjames@stripped>
wrote:
> Synchronous replication has a serious limitation --
> If the second master exists for HA, and the building with both masters 
> is hit by a tornado, earthquake, flood, etc, then dual-master did not
help.
> If you move the other master to a remote location, then the delays to do

> the sync could be unacceptable.
> 
> On 10/27/11 9:08 AM, list@stripped wrote:
>> On Wed, 26 Oct 2011 20:20:18 +0100, "Ricardo
Freitas"<ricardo@stripped>
>> wrote:
>>> Hello, my fellow developers and admins
>>>
>>>
>>>
>>> I have a question regarding a master-master replication system.
>>>
>>>
>>>
>>> I implemented one with the help of some of you guys - however I had
some
>>> problems regarding the agent so I have a master-master and not a mmm
>> system
>>> - and now I come across the awfull 1062 error - the 'Error 'Duplicate
>> entry
>>> '123020901' for key 'PRIMARY'' on query.'
>>>
>>>
>>>
>>> The issue here is I sometimes insert on master 1 (let's call it like
>> this)
>>> and sometimes on master 2. I thought this wouldn't be an issue as both
>>> database would talk to one another however I think this might be the
>> origin
>>> of the problem.
>>>
>>>
>>>
>>> I already changed the system to always force the inserts query to be
on
>>> Master 1 however I would like to know how to avoid this. I read
>> something
>>> about configuring a skip-slave-error but I don't think this would be
>> that
>>> much of a solution as sometimes things happens and it's important to
>> have
>>> some kind of notion or warning.
>>>
>>>
>>>
>>> I read about the auto-increment-increment = 2 or even the increment
>> offset.
>>>
>>>
>>> If I configure Master 2 to auto-increment-increment = 2 and master 1
to
>>> auto-increment-increment = 1, would this help?
>>>
>>>
>>>
>>> Master 1 - example
>>>
>>> Row 1
>>>
>>> Row 2
>>>
>>> Row 3
>>>
>>>
>>>
>>> Master 2 - example
>>>
>>>
>>>
>>> Row 2
>>>
>>> Row 4
>>>
>>> Row 6
>>>
>>>
>>>
>>> As far as I'm concerned, eventually sometimes collisions would occur.
I
>>> thought that PK's weren't replicated as the database would just
receive
>> the
>>> insert query and viola.
>>>
>>>
>>>
>>> How can I avoid this in the future?
>>>
>>>
>>>
>>> I read about UUID as well but as I've inherit this system I have a lot
>> of
>>> sites already relaying on the pk (for query purposes) so I can't just
>>> changed this.
>>>
>>>
>>>
>>> Thanks a lot!
>>>
>>>
>>>
>>> Ricardo
>> Ricardo,
>>
>> I feel your pain as we had attempted to setup a master/active
>> master/passive replications strategy much like this, and ran into many
of
>> the same problems.  We eventually gave up after finding a really neat
>> open
>>
>> source project called Galera, which gives you synchronous multi-master
>> replication (yes I said synchronous).  We've been testing for a few
weeks
>> and we're very impressed.
>>
>> Here is the home page:
>>
>> http://codership.com/products/mysql_galera
>>
>> And here is a really good explanation of Galera by the folks at
>> severalnines:
>>
>> http://www.severalnines.com/clustercontrol-mysql-galera-tutorial
>>
>> Hope this helps!
>>
>>
>>
> 
> -- 
> Rick James - MySQL Geek

Thread
Master-Master -> duplicate entryRicardo Freitas26 Oct
  • Re: Master-Master -> duplicate entrynavid@navid.it26 Oct
    • Re: Master-Master -> duplicate entryRicardo26 Oct
  • Re: Master-Master -> duplicate entryRick James27 Oct
    • Re: Master-Master -> duplicate entryRicardo27 Oct
      • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
        • Re: Master-Master -> duplicate entryJohan De Meersman27 Oct
          • Re: Master-Master -> duplicate entryRicardo27 Oct
          • Re: Master-Master -> duplicate entryRicardo27 Oct
          • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
            • RE: Master-Master -> duplicate entryRicardo Freitas27 Oct
              • Re: Master-Master -> duplicate entrynavid@navid.it27 Oct
              • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
                • RE: Master-Master -> duplicate entryRicardo Freitas27 Oct
                  • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
                    • RE: Master-Master -> duplicate entryRicardo Freitas27 Oct
                    • Re: Master-Master -> duplicate entryRicardo27 Oct
                      • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
                        • RE: Master-Master -> duplicate entryRicardo Freitas27 Oct
                          • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
                            • Re: Master-Master -> duplicate entry (UUID lecture)Rick James27 Oct
            • Re: Master-Master -> duplicate entryJohan De Meersman28 Oct
        • Re: Master-Master -> duplicate entryRicardo27 Oct
  • Re: Master-Master -> duplicate entrylist27 Oct
    • RE: Master-Master -> duplicate entryRicardo Freitas27 Oct
      • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
        • Re: Master-Master -> duplicate entryMySQL)27 Oct
    • Re: Master-Master -> duplicate entryRick James27 Oct
      • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
        • Re: Master-Master -> duplicate entryRick James27 Oct
          • Re: Master-Master -> duplicate entryJohan De Meersman28 Oct
            • Re: Master-Master -> duplicate entryRick James28 Oct
              • Re: Master-Master -> duplicate entryMats Kindahl31 Oct
      • Re: Master-Master -> duplicate entrylist27 Oct