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)

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

  3. Hello Kedar,

    i have a master master replication , but they are in active / passive mod via Haproxy.

    it writes only on Master A which is active and Master B is passive , when A goes down , B takes over.

    Up to my understanding there will no conflicts on increment ID as there’s no writes or inserts on Master B, it only replicate Master A. Can you please confirm me that ?
    thanks you very much

Leave a Reply

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

*

-- Kedar Vaijanapurkar --