I have a nagios application running on 2 servers, i am planning to have have master-master replication for HA. for master-slave replication, slave should be read-only. in this manner is there any pre-req for master-master replication. while doing master-master replication i ran into a lot of duplicate as both the databases are auto-increment on their own and trying to replicate each other.
can some one please help to resolve the issue.
Thanks,
Best Answer
To avoid the issue with duplicate keys when using auto_increment PKs, you need to configure the system variables auto_increment_increment and auto_increment_offset.
From the MariaDB Knowledgebase: AUTO_INCREMENT:
So in other words, with two masters: On master1 set
auto_increment_increment=2
andauto_increment_offset=1
, and on master2 setauto_increment_increment=2
andauto_increment_offset=2
.