Problem with Master Master Replication and Auto Increment

Consider we’ve already set a master-master replication. Now create following table on Server1:

CREATE TABLE `temp` (
`id` int(10) NOT NULL auto_increment,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

The table will will get replicated on Mysql Server2 in the master-master setup.

Now insert value on Mysql Server1 as follows:

mysql>insert into temp values(null);

On Mysql Server2 in replication you will see single row inserted. Now insert one row from Mysql Server2 as follows:

mysql>insert into temp values(null);

You should see an error:

Error ‘Duplicate entry ’1′ for key ‘PRIMARY” on query…

The obvious problem of maintaining auto increments in sync will persist on both mysql servers as AUTO_INCREMENT’s value.

The solution is to use the variables auto_increment_increment and auto_increment_offset as explained below.

- Stop both master-master replication servers.

- Start slave.

No related posts.