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.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.