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.