{"id":36,"date":"2009-09-20T07:59:11","date_gmt":"2009-09-20T07:59:11","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=36"},"modified":"2023-04-24T05:05:23","modified_gmt":"2023-04-24T05:05:23","slug":"problem-with-master-master-replication-and-auto-increment","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/problem-with-master-master-replication-and-auto-increment","title":{"rendered":"MySQL Master Master Replication and auto_increment_increment \/ auto_increment_offset"},"content":{"rendered":"\n

In this post we will see importance of replication related variables auto_increment_increment & auto_increment_offset with respect to MySQL Master Master setup.<\/p>\n\n\n\n

Consider we’ve already set a master-master replication. Now create following table on Server1:<\/p>\n\n\n\n

`id` int(10) NOT NULL auto_increment,\n PRIMARY KEY (`id`)\n ) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;<\/pre>\n\n\n\n
The table will will get replicated on MySQL Server2 in the master-master setup.<\/p>\n\n\n\n
Now insert value on MySQL Server1 as follows:<\/p>\n\n\n\n
mysql>insert into temp values(null);<\/em><\/pre>\n\n\n\n
On MySQL Server2 in replication you will see single row inserted. Now insert one row from MySQL Server2 as follows:<\/p>\n\n\n\n
mysql>insert into temp values(null);<\/em><\/pre>\n\n\n\n
You should see an error:<\/p>\n\n\n\n
Error 'Duplicate entry '1' for key 'PRIMARY'' on query...<\/em><\/pre>\n\n\n\n
The obvious problem of maintaining auto increments in sync will persist on both MySQL servers as AUTO_INCREMENT’s value.<\/p>\n\n\n\n
auto_increment variables and master-master replication<\/h2>\n\n\n\n
The solution is to use the variables auto_increment_increment and auto_increment_offset<\/a> as explained below. Both configuration options are dynamic and will not need MySQL restart.<\/p>\n\n\n\n