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:

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

Quick TIP:

If you have 3 servers, you may assign the values as follows: auto_increment_increment = 3 (number-of-masters) auto_increment_offset = 1 (id-of-master)
auto_increment_offset = 2 (id-of-master) auto_increment_offset = 3 (id-of-master)

sumber : http://kedar.nitty-witty.com/blog/problem-with-master-master-replication-and-auto-increment

Posted on: January 30, 2019, by : Julian's | 14 views