Mysql Master Master Replication errors master/slave

0
votes

i tried master-master replication; as a part of it first I tried master- slave replication. The replication connection is successful but when I made changes on master, the slave is throwing a lot of duplicate errors.

Last_SQL_Error: Error 'Duplicate entry '1576' for key 'PRIMARY'' on query. Default database: 'nagiosxi'. Query: 'INSERT INTO xi_auditlog (log_time,source,user,type,message,ip_address,details) VALUES ('2018-05-15 16:34:19','Nagios XI','NULL',32,'cmdsubsys: User [nagiosadmin] applied a new configuration to Nagios Core','localhost','')' Replicate_Ignore_Server_Ids:

As per my understandings the master cannot overwrite the values in the slave. the databases which am replicating are application databases, both master and slave databases throwing a lot of errors, as the values cannot be overridden.

Can someone please help me in master master replication. Do I need to script for this purpose?

1
mysqldatabase-replicationmaster-slave
DBA should be able to help you on this. Also, you're missing some key information here. OS, MySQL version. Give this a read - hungrykoala
the db came as a part of Nagiosxi install, the db is on RHEL 7.3 and the version is Mariadb 5.5.* version. - John smith
Then you're using MariaDB and not MySQL. - hungrykoala

1 Answers

0
votes

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.

https://mariadb.com/kb/en/library/auto_increment/

Set auto_increment_increment to 2 on both masters. Set auto_increment_offset to 2 on one server and to 1 (default) on the other.

This causes one server to only create even-numbered values, and the other to create only odd-numbered values. They will still tend to be sequential, because if one server creates 1, 3, 5, 7, and 9, and the next insert occurs on the other server after all of these have replicated, the next id will be 10. The gaps are inevitable, but as any experienced DBA will tell you, do not obsess about the gaps.

You should set your binlog_format to ROW or MIXED -- not STATEMENT -- on both servers. If you are backing up your binlogs as you should be, then log_slave_updates should also be enabled. If you have subtended replicas, the master to which they are connected must have this option enabled.

Also, you're going to need to destroy and rebuild one of the servers at this point, because their data will never be consistent.

The recommended practice for master/master is to only write to one server at a time. Your application can switch at will, but it is most stable if only one is treated as writable.