4

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 ?

Jonathan Ross
  • 2,173
  • 11
  • 14

2 Answers2

7

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).

John Gardeniers
  • 27,262
  • 12
  • 53
  • 108
  • Thanks, John. I have both these values set but will that completely fix any issues with collision ? I saw this too: http://serverfault.com/questions/193112/two-way-multi-site-replication-in-mysql – Jonathan Ross Jun 14 '11 at 08:18
  • @Jonathan, those settings will solve the autoincrement issue but I do suggest you have a read of some of the howtos available on this subject. It's been a little while since I set this up myself and don't recall the gotchas involved. – John Gardeniers Jun 14 '11 at 08:23
  • @Jonathan, if I remember correctly this is the reference I used and experienced no problems: http://jayant7k.blogspot.com/2006/06/multi-master-replication-in-mysql.html – John Gardeniers Jun 14 '11 at 08:39
  • @Jonathan, not on a real busy site but I have used it with master-master-master in three sites across the country for a company intranet, security systems, monitoring, etc., so is similar to a web site with a few thousand users per day, with a good mix of reads and writes. It's been running since about 2006 and as far as I know is still being used today. – John Gardeniers Jun 14 '11 at 09:58
  • Interesting, thanks. Any other experiences welcome. – Jonathan Ross Jun 14 '11 at 10:37
1

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.

the-wabbit
  • 40,319
  • 13
  • 105
  • 169
  • Thanks. This seems to be present in Mysql 5.5, will it fix this problem ? http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html – Jonathan Ross Jun 14 '11 at 13:14
  • 1
    Since it is only "semi-synchronous" (read: asynchronous under some circumstances), the same general problem space still applies. Especially since it will behave as asynchronous in the common case where the network connection between two databases is interrupted. MySQLs replication is *really* designed for read-write-master/read-only-slave-lagging-behind operation and nothing else. – the-wabbit Jun 14 '11 at 13:18
  • Thanks, I think I'd feel safer with this in place any way and will probably go down this route. I plan on splitting 'easy' writes like page counts up and use a different method of counting to cut 80% of writes down. – Jonathan Ross Jun 14 '11 at 14:00