Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Maybe a stupid question but do you have loaded a dataset on each machine? Max. –  Maxime FOUILLEUL Dec 24 '12 at 10:23
    
After setting up the replication, I create a database on "Master-1" and added a table and some row, I could see the new database on "Master-2", but doing the same from "Master-2" would not update "Master-1" i.e I can't see the changes I have done in "Master-2" from "Master-1". –  mrz Dec 24 '12 at 10:31
    
When you have setted change master to, it was the good logbin pos on each? –  Maxime FOUILLEUL Dec 24 '12 at 11:14
    
I didn't put the logbin pos, and I didn't get error, (I got query ok for all of them). And getting master status showed me the pos....How can I tell if it was the good pos? –  mrz Dec 24 '12 at 11:34
    
Why do you want a master-master replication? Do you know how setting up simple replication? –  Maxime FOUILLEUL Dec 24 '12 at 12:58

2 Answers 2

up vote 1 down vote accepted

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
share|improve this answer

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

share|improve this answer
    
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 '12 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. –  Maxime FOUILLEUL Dec 25 '12 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 '12 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. –  Maxime FOUILLEUL Dec 25 '12 at 16:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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