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.

auto_increment variables and master-master replication

The solution is to use the variables auto_increment_increment and auto_increment_offset as explained below. Both configuration options are dynamic and will not need MySQL restart.

  • Configure auto_increment_increment and auto_increment_offset as follows on Primary and Stand-by Master
  • Persist the change in MySQL configuration file (my.cnf)

MySQL Server1 (Primary / Master):

SET GLOBAL auto_increment_increment=2
SET GLOBAL auto_increment_offset=2

MySQL Server2 (Stand-by / Secondary Master):

SET GLOBAL auto_increment_increment=2
SET GLOBAL auto_increment_offset=1

– Start Replica (/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.

Conclusion

In conclusion, understanding and correctly configuring auto_increment_increment and auto_increment_offset are crucial for maintaining data consistency and integrity in a MySQL Master-Master Replication setup, and can greatly improve the performance and reliability of your database replication architecture. However, it’s essential to adhere to best practices and avoid writing to both masters to ensure data consistency and integrity in your MySQL replication setup.

5 comments
  1. 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

    1. Hello Manjeet,

      This kind of setup is not conflicts on increment if you have active/passive M-M replication setup.

  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.

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

Leave a Reply

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