Mysql replication - Error No. 1146&1062

Question

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:

[mysqld]
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>'  
IDENTIFIED BY 'somePass'

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

CHANGE MASTER TO MASTER_HOST='<the-other-VM-ip>', 
MASTER_USER='<replicator>',  
MASTER_PASSWORD='somePass';

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
Replicate_Ignore_Server_Ids:
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.

Answer 1

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

Master-1:

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

Master-2:

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

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