short version

In master master replication, How to have one master write data with odd IDs and the other one with even IDs? Is it the way to avoid conflict?

detailed version

I wanted to setup a MySQL master-master replication on two virtual machine I have on my system, to just play around with replication and get familiar with the concept.

I edited [mysqld] section of /etc/my.cnf of Master-1 and it looks like:

port            = 3306
socket          = /var/lib/mysql/mysql.sock
datadir         = /var/lib/mysql

log-bin         = mysql-bin
binlog-ignore_db= mysql
binlog_format   = mixed

server-id       = 1

my.cnf of the Master-2 is almost identical to Master-2 except server-id which I set to 2. Then I restart the mysql service, and create slave users in each VMs:

GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'<the-other-VM-ip>'  

Followed to that I set the Master config in each VM:


And finally start slave on both machines. I created a database in Master-1 and added a table with some value. When I commit and check in Master-2 I can see all the data. But When I do the same in Master-2, the changes are not committed to the Master-1. SHOW SLAVE STATUS \G in Master-2 show me the following error:

Last_Errno: 1146
Last_Error: Error 'Table 'mydb.taggregate_temp_1212047760' doesn't exist' 
            on query. Default database: 'mydb'. 
Query: 'UPDATE thread AS thread,taggregate_temp_1212047760 AS aggregate
       SET thread.views = thread.views + aggregate.views WHERE thread.threadid = aggregate.threadid'

I stopped the salve, wrote SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; in mysql and then start the slave and ask for the status, which I get a new error:

Last_SQL_Errno: 1062
Last_SQL_Error: Could not execute Write_rows event on table mysql.user;
                Duplicate entry 'localhost-root' for key 'PRIMARY', 
                Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; 
                the event's master log mysql-bin.000001, end_log_pos 26231
Master_Server_Id: 1

I believe this could be solve using auto-increment-offset = 2 in one config file, although I'm not sure about the procedure... beside not quite sure what SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; is and what it does, when Googled my first problem found it here.

I would be thankful for a description of the errors and how to overcome them.

  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Aug 29, 2017 at 10:23

2 Answers 2


I did as described here and it solved my problem. So basically I added the following to [mysqld] section in my.cnf files:


server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1


server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2

Example, you have two DBs named DB_A and DB_B.

1/Master Slave Replication: You have a server SRV_A which is master with DB_A and DB_B and slave SRV_B with DB_A and DB_B, all modification made on master (DB_A or DB_B) will be replicate on slave. You can't modify data on slave : SINGLE DIRECTION

2/Master Master Replication: You have a server MASTER_A with DB_A and DB_B and MASTER_B with DB_A and DB_B.

MASTER_A and MASTER_B could'nt be master for the same databases so for instance you could have MASTER_A is master for DB_A and MASTER_B is master for DB_B.

All modifications made on MASTER_A on DB_A will replicate to MASTER_B and all modictions made on MASTER_B on DB_B will replicate to MASTER_A.

Real MASTER/MASTER concept is much more reserved for NoSQL DB...

  • This is wrong man! I have SRV_A and SRV_B, and I have only one DB on both with only one table in it! No difference which server I commit my changes to, it would be replicate on the other server as well.
    – mrz
    Dec 25, 2012 at 6:04
  • Yes, it´ll work cause your are alone on your server. Try to put 100 users in each node updating same table... Réplication will after few seconds cause mysql have no idea how to resolve conflict. Solutions like mysql ndb or nosql databases manage and resolve those xonflict. Iam not here for fighting its not my prod i just want to help. Mysql will not refuse your conf to, but you will what appens after few transactions. Dec 25, 2012 at 11:33
  • what if I store data with diff ID (even on one server, and odd on the other one)? plus I'm not going to use it like that, I'm gonna use some sort of load balancing strategy: either write with one and read with the other, or have one of them like hot standby, or... any how, as I wrote in my question, I'm just playing around to learn...
    – mrz
    Dec 25, 2012 at 11:40
  • "what if I store data with diff ID" if you manage IDs manualy you can avoid replication errors but for me you take a big risk... But Caution i didn't say you can't store same data on different servers, i said you could'nt have more than one master for a particular dataset. Usualy (i use this solution in production) we set one Master for "modification" queries (CREATE, INSERT, DELETE, TRUNCATE...) and several slaves for "SELECT queries". With this solution you can load balanced activity without replication errors. If you want some help to setup the solution, let me know. Dec 25, 2012 at 16:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.