20 Sep

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:

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

3 thoughts on “MySQL Master Master Replication and auto_increment_increment / auto_increment_offset

  1. Kedar,

    I have Mysql Master -slave setup, Is it possible for me to convert the current setup to mysql master, if so could you please outline the steps

  2. I have Master/Master on two MariaDB 10.1.31 servers with GTID implementation.
    I also set the following settings for auto_increment_increment and auto_increment_offset:

    Server1:
    auto_increment_increment=2
    auto_increment_offset=1

    Server2:
    auto_increment_increment=2
    auto_increment_offset=2

    I executed the following statements on server 1:
    create table tb1 (id int AUTO_INCREMENT primary key,name varchar(45),age int);
    insert into tb1 (name,age) values (“fouad”,25);

    The problem is occurred, let say support I enter three records in server 1. We get id 1,3 and 5. When I enter two records on server 2, I get id 6 and 8.

    My Query is why server 2 does not create id of 2 and 4.

    Thanks in advance.

    • Because auto increment changes as you insert the records. The values 1,3,5 are inserted on server-1 and then they got replicated to server-2.
      So the max value on server-2 is 5 as well. It will start with the next higher increment number. The same will happen if you insert 8,10,12 on server-2 -> next value on server-1 will be 13 and not 7.

      Hope this is clear.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

-- Kedar Vaijanapurkar --