{"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<p>In this post we will see importance of replication related variables auto_increment_increment &amp; auto_increment_offset with respect to MySQL Master Master setup.<\/p>\n\n\n\n<p>Consider we&#8217;ve already set a master-master replication. Now create following table on Server1:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">`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<p>The table will will get replicated on MySQL Server2 in the master-master setup.<\/p>\n\n\n\n<p>Now insert value on MySQL Server1 as follows:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><em>mysql&gt;insert into temp values(null);<\/em><\/pre>\n\n\n\n<p>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<pre class=\"wp-block-preformatted\"><em>mysql&gt;insert into temp values(null);<\/em><\/pre>\n\n\n\n<p>You should see an error:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><em>Error 'Duplicate entry '1' for key 'PRIMARY'' on query...<\/em><\/pre>\n\n\n\n<p>The obvious problem of maintaining auto increments in sync will persist on both MySQL servers as AUTO_INCREMENT&#8217;s value.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">auto_increment variables and master-master replication<\/h2>\n\n\n\n<p>The solution is to use the variables <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/replication-options-master.html#sysvar_auto_increment_increment\" target=\"_blank\" rel=\"nofollow noopener\">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<ul class=\"wp-block-list\">\n<li>Configure auto_increment_increment and auto_increment_offset as follows on Primary and Stand-by Master<\/li>\n\n\n\n<li>Persist the change in MySQL configuration file (my.cnf)<br><\/li>\n<\/ul>\n\n\n\n<p>MySQL Server1 (Primary \/ Master):<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SET GLOBAL auto_increment_increment=2\nSET GLOBAL auto_increment_offset=2<\/pre>\n\n\n\n<p>MySQL Server2 (Stand-by \/ Secondary Master):<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SET GLOBAL auto_increment_increment=2\nSET GLOBAL auto_increment_offset=1<\/pre>\n\n\n\n<p>&#8211; Start Replica (\/Slave).<\/p>\n\n\n\n<p>Remember:<br>&#8211; <b>auto_increment_increment<\/b> controls the interval between successive column values.<br>&#8211; <b>auto_increment_offset<\/b> determines the starting point for the AUTO_INCREMENT column value.<br>&#8211; It&#8217;s advisable to have these configured to avoid any accidental conflicts for all master-master setup.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Conclusion<\/h4>\n\n\n\n<p>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&#8217;s essential to adhere to best practices and avoid writing to both masters to ensure data consistency and integrity in your MySQL replication setup.<\/p>\n","protected":false},"excerpt":{"rendered":"In this post we will see importance of replication related variables auto_increment_increment &amp; auto_increment_offset with respect to MySQL Master Master setup. Consider we&#8217;ve already set a master-master replication. Now create&hellip;\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,377],"tags":[259,338,551,258,427,100],"class_list":{"0":"post-36","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-articles","8":"tag-auto-increment","9":"tag-auto-increment-offset","10":"tag-duplicate-entry-1","11":"tag-master-master-replication","12":"tag-mysql","13":"tag-replication"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/36","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=36"}],"version-history":[{"count":8,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/36\/revisions"}],"predecessor-version":[{"id":2818,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/36\/revisions\/2818"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=36"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=36"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=36"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}