MySQL Master Master Replication and auto_increment_increment / auto_increment_offset
Kedar
In this post we will see importance of replication related variables auto_increment_increment & auto_increment_offset with respect to MySQL Master Master setup. Consider we’ve already set a master-master replication. Now create…
5 responses to “MySQL Master Master Replication and auto_increment_increment / auto_increment_offset”
Hello Kedar,
i have a master master replication , but they are in active / passive mod via Haproxy.
it writes only on Master A which is active and Master B is passive , when A goes down , B takes over.
Up to my understanding there will no conflicts on increment ID as there’s no writes or inserts on Master B, it only replicate Master A. Can you please confirm me that ?
thanks you very much
Hello Manjeet,
This kind of setup is not conflicts on increment if you have active/passive M-M replication setup.
I have Master/Master on two MariaDB 10.1.31 servers with GTID implementation.
I also set the following settings for auto_increment_increment and auto_increment_offset:
Server1:
auto_increment_increment=2
auto_increment_offset=1
Server2:
auto_increment_increment=2
auto_increment_offset=2
I executed the following statements on server 1:
create table tb1 (id int AUTO_INCREMENT primary key,name varchar(45),age int);
insert into tb1 (name,age) values (“fouad”,25);
The problem is occurred, let say support I enter three records in server 1. We get id 1,3 and 5. When I enter two records on server 2, I get id 6 and 8.
My Query is why server 2 does not create id of 2 and 4.
Thanks in advance.
Because auto increment changes as you insert the records. The values 1,3,5 are inserted on server-1 and then they got replicated to server-2.
So the max value on server-2 is 5 as well. It will start with the next higher increment number. The same will happen if you insert 8,10,12 on server-2 -> next value on server-1 will be 13 and not 7.
Hope this is clear.
Kedar,
I have Mysql Master -slave setup, Is it possible for me to convert the current setup to mysql master, if so could you please outline the steps