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 ?

Improve this question

2 Answers 2

Reset to default
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).

Improve this answer
5
  • Thanks, John. I have both these values set but will that completely fix any issues with collision ? I saw this too: serverfault.com/questions/193112/… Commented Jun 14, 2011 at 8: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. Commented Jun 14, 2011 at 8:23
  • @Jonathan, if I remember correctly this is the reference I used and experienced no problems: jayant7k.blogspot.com/2006/06/… Commented Jun 14, 2011 at 8: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. Commented Jun 14, 2011 at 9:58
  • Interesting, thanks. Any other experiences welcome. Commented Jun 14, 2011 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.

Improve this answer
3
  • Thanks. This seems to be present in Mysql 5.5, will it fix this problem ? dev.mysql.com/doc/refman/5.5/en/replication-semisync.html Commented Jun 14, 2011 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. Commented Jun 14, 2011 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. Commented Jun 14, 2011 at 14:00

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.