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