mysqlreplicationmulti-master-replication

Master-Master replication auto_increment_offset

I have set up 2 MYSQL servers:

my.cnf server1:

auto_increment_increment = 2
auto_increment_offset = 1

my.cnf server2:

auto_increment_increment = 2
auto_increment_offset = 2

But when I insert records one by one 10 times from different servers:

INSERT INTO `table1` (`id`, `text`) VALUES (NULL, '22222');

Result:

id  text
1   22222
2   22222
5   22222
6   22222
9   22222
...

But I want to:

id  text
1   22222
2   22222
3   22222
4   22222
5   22222
...

It is possible?

Solution

  • Partial reason for this could be because the inserts are invoked from separate sessions. With auto_increment_increment = 2, my guess is you will probably not get your desired result. Did you try setting both auto_increment_increment and auto_increment_offset to 1?