One of the first thing in setting up MySQL replication system is to set how to incremented id's are created and setting up the offset per replicated MySQL server. This is solving duplicated id's generated on different server at the same time.

 

 

 

Photo by Scott Graham on Unsplash
Photo by Scott Graham on Unsplash

 

So, let's say we have five different server which are serving our users, and let say our site is very popular and all of these 5 servers have the users at the same time writing and reading your database.  The problem is when activity of user on server number 1 creates a record in DB and if this is for example first record in that table, it will have ID  1. But what if the user who is connected on server number 5 creates a new record in same table and for server number 5 this is also first record. Should server number 5 also assign ID = 1 to that first record? And what will happen when we merge this data, in for example showing the results of this table to the user on server number 4? 

 


So in this case, first we need to replicate data between servers to have the data on server Number 4, but what we are going to do with two record with same ID written or server number 1 and server number 5? Are going to overwrite the oldest one? Not good. Are we going to keep them both and store them by time creation? hmmm this sound s promising, and what about ID? This ID is already connected to user table ID to connect the activity to the user and we are talking about same content ID and two different user... ah no this is becoming messy

 

 

So what we can do?

 

Well, we can set up MySQL to start counting those incremented IDs, differently across the servers and by working like this we can not have duplicated IDs.


Sounds great?

OK let's do it.

To solve this we have to manually set auto_increment_increment and auto_increment_offset  in each database:

 

auto_increment_increment: controls the interval between successive column values. The default value is 1.

auto_increment_offset: determines the starting point for the AUTO_INCREMENT column value.

The default value is 1.

 

So let's set this on first server:

                    SET GLOBAL auto_increment_offset=1;

SET GLOBAL auto_increment_increment=5;

 

second server:

                    SET GLOBAL auto_increment_offset=2;

SET GLOBAL auto_increment_increment=5;

 

third server

                    SET GLOBAL auto_increment_offset=3;

SET GLOBAL auto_increment_increment=5;

 

forth server

                    SET GLOBAL auto_increment_offset=4;

SET GLOBAL auto_increment_increment=5;

 

fifth server

                    SET GLOBAL auto_increment_offset=5;

SET GLOBAL auto_increment_increment=5;