MySQL Master Master Replication and auto_increment_increment / auto_increment_offset

MySQL Master Master Replication and auto_increment_increment / auto_increment_offset

In this post we will see importance of replication related variables auto_increment_increment & auto_increment_offset with respect to MySQL Master Master setup.

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.

– Add variables to my.[ini|cnf] file.

Server1:

auto_increment_increment=2

auto_increment_offset=2

Server2:

auto_increment_increment=2

auto_increment_offset=1

– Restart mysql servers.

– Start slave.

Remember:
– auto_increment_increment controls the interval between successive column values.
– auto_increment_offset determines the starting point for the AUTO_INCREMENT column value.
– It’s advisable to have these configured to avoid any accidental conflicts for all master-master setup.