Replication Setup what's the value of MySQL auto_increment and offset in my.ini

Question

I have setup master master MySQL replication to sync changes in both servers.

Now I want to setup 6 servers, is that possible? I'm making 6 because when an area lost it connection over WAN it will continue it's service and then sync it when the connection is back.

Now I'm having problems with the auto increment increment and auto increment offset to avoid data collision.

I'm confused with this and I've read an article and made it worse.

So now I wonder what should be the proper value of my auto increment and offset in each server.

Answer 1

I'd answer your confusion about auto-increment* variables.

Remember:

• auto_increment_increment controls the interval between successive column values.

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

So if you're setting 6 servers, you can set them as: auto_increment_offset=1,2,3,4,5,6 auto_increment_increment=6

so on each servers insertion series will be like: 1,7,13... 2,8,14... 3,9,15... 4,10,16... 5,11,17... 6,12,18...

Idea here is just to generate unique auto-increment numbers.

BUT WAIT!!!!!

Before you begin, when you say 6 servers it will be pair of 3 master-master or are they slaves of original master? If they're slaves, are they going to accept writes (not good!)? Are you going to write on all nodes (in case of master-master? The architecture isn't clear enough and hence your solution of auto-increments might not be right / required as well.

Anyways... I hope we fixed the confusion of auto-increment. Trust the documentation and don't get confused by too many articles.

Answer 2

6 masters in circular replication? Don't even think about it! When one master goes down, you will have a nightmare repairing the mess.

Switch to MariaDB with Galera. It can handle 6 live Masters and deal with auto_increment_increment, failures, etc.