Home / server / Questions / 280197
In Process
Jonathan Ross
Asked: 2011-06-14 23:53:13 +0800 CST

Are Master-Master database collisions a problem even with auto increment?

  • 772

I've done quite a lot of reading into this and I'm still I'm not sure where I stand.

Am I going to get database collisions (and then corruption) with two Masters both receiving writes ?

Now that MySQL (I'm using 5.1 on Debian and Ubuntu) has auto-increment running is this avoided and would it suit production ?

I've looked at MMM for only enabling one Master for writes but it has it's own issues and my topology is really geared towards having two Masters both receiving writes.

I'm not sure I believe this: http://www.neocodesoftware.com/replication/

Can any MySQL experts please help ? Thanks very much.

--EDIT--

Half the stuff I read or people I ask say you'll still get corruption even using auto-increment ... and the other half say you won't ! Can someone who has run busy sites please clarify ?

mysql mysql-replication mysql5

2 Answers

  1. Unless you take the appropriate precations this is a very real problem. Briefly, your configuration on each server needs two values set.

    1. auto_increment_increment
    2. auto_increment_offset

    auto_increment_offset should be set such that each master has a unique value, normally being 1 for the first, 2 for the second, etc.

    auto_increment_increment is the step size of the increments and should be at least the number of masters in the chain. e.g. For a simple two server setup the value would be 2 (or more).

    • 7

  2. There is just no way to make it work reliably without a lot of custom code. The problem is that asynchronous replication will incur collisions and conflicts which needs to be resolved smartly by smartly-written and flawlessly implemented algorithms. You won't have that. Use MySQL cluster setup instead, if you need multi-master operations and can handle the limitations it brings.

    • 1