A lot of people are running MySQL Master-Master replication pairs in Active-Passive mode for purpose of high availabilities using MMM or other solutions. Such solutions generally have one major problem – you have to be very carefully switching writes as if you do not do it atomically (such as some scripts continue to write to old master) or if you had slave lag during the switch you can have replication stopped with the error or silently become inconsistent. You can pick what you dislike the most.
There are people to tell you you just should use auto_increment_increment and auto_increment_offset and you would not have any conflicts, I would tell you they are wrong but there is still a good reason you may consider using these options.
Let me start by saying these options help to avoid some conflicts – if you have insert in table with auto_increment column you will almost for sure get duplicate key error in replication thread if you happen to insert data to 2 masters at once. This can be solved with auto_increment_increment and auto_increment_offset, however a lot of other things can’t – updates to the data, deletes as well as insertion to the table with unique keys and non auto increment primary keys. The problem is duplicate key error is one of the few errors statement based replication will raise. deletes and updates resulting in different data will just happen silently in most cases.
If we use row based replication it can be configured to be more or less picky. In default, slave_exec_mode=STRICT mode replication will stop if row to be updated or deleted is not found in addition to duplicate key errors, if you set slave_exec_mode=IDEMPOTENT both duplicate key error and row not found errors will be ignored. Strict Mode with row level replication will not detect all conflicts – it is for example possible for the same row to get different updates on master and slave and replication will get silently out of sync.
So back of our topic of auto_increment_increment and auto_increment_offset – besides being able to avoid some problems they can be helpful in manually resolving inconsistencies later on as they allow to identify which server has inserted the data in question (and which was master at least for that application at that time). Sure you can find same information in binary logs but having it in the database too allows to speed up process in many cases.
I think is better use auto_increment_increment , auto_increment_offset.
auto_increment_increment = 2 // number of masters
auto_increment_offset = 2 // id of master
example if have 3 servers
for server 1, this server generate ids 1,4,7,10,etc..
auto_increment_increment = 3
auto_increment_offset = 1
for server 2, this server generate ids 2,5,8,11,etc..
auto_increment_increment = 3
auto_increment_offset = 2
for server 2, this server generate ids 3,6,9,12,etc..
auto_increment_increment = 3
auto_increment_offset = 3
if you use slave_exec_mode=IDEMPOTENT and have 1 server in usa , other in mexico and other in spain you have big problem (latency) and the slave_exec_mode=IDEMPOTENT increases the time of insert..
Is this suites for master-slave replication perfectly?