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!
