0

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,

  • To avoid more troubles, don't write to both masters. – Rick James Jun 13 '18 at 18:35
  • @RickJames If you're only writing to one master, doesn't that effectively make it master-slave rather than master-master? Or at least would seem to defeat the purpose of master-master replication? – dbdemon Jun 15 '18 at 19:19
  • I suppose a master-master setup where you're only writing to one master still has an advantage over a master-slave setup because it's easier to redefine which node is the master, i.e. you only have to modify stuff on the application side, not the MySQL side. – dbdemon Jun 16 '18 at 12:21
  • The purpose of M-M (vs. M-S) is ease of failover. You already have the replication set up going the "other direction" (even though it is doing 'nothing'). M-M does not provide "write scaling". – Rick James Jun 16 '18 at 15:54
0

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:

To make master-master or Galera safe to use AUTO_INCREMENT one should use the system variables auto_increment_increment and auto_increment_offset to generate unique values for each server.

So in other words, with two masters: On master1 set auto_increment_increment=2 and auto_increment_offset=1, and on master2 set auto_increment_increment=2 and auto_increment_offset=2.

  • its worked for me. how can we check replication is working 100%? – mohan reddy Jun 15 '18 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.