As in the subject, I have a problem with master-master replication for MySQL, but maybe at the beginning I will describe how it was implemented. I have Zabbix 6.0 LTS deployed in a native HA configuration in two data centers (DC1 and DC2) - one machine per data center (srv1.domain.int and srv2.domain.int). Access to databases is via static load balancing methods - specifically Global Availability. The zabbix-server.conf configuration contains DBHost=proxysql.domain.int. Under this FQDN there are 2 VMs with ProxySQL - one per data center (proxqSQL1.domain.int and proxqSQL2.domain.int), which have in their configuration indicated two MySQL database servers from both data centers (DB1.domain .int and DB2.domain.int). There is master-master replication between the databases. And this is where the problems begin. During the implementation itself, replication worked properly - almost no data went to the database. As the test hosts were added for monitoring purposes, after some time a replication error appeared:
I came across information that it may be due to auto increment, so I set it in the my.cnf file
for DB1.domain.int
and for DB2.domain.int
This temporarily solved the problem, but the next day I got the following error:
Have you ever encountered such a situation?
Worker 1 failed executing transaction 'ANONYMOUS' at master log log-bin.000027, end_log_pos 919734868; Error 'Duplicate entry '11006' for key 'hosts.PRIMARY'' on query. Default database: 'zabbix'. Query: 'insert into hosts (hostid,proxy_hostid,host,name,description) values (11006,10928,'host1.domain.int','host1.domain.int' ,'')'
for DB1.domain.int
auto_increment_increment=2
auto_increment_offset=1
auto_increment_offset=1
auto_increment_increment=2
auto_increment_offset=2
auto_increment_offset=2
This temporarily solved the problem, but the next day I got the following error:
Worker 1 failed executing transaction 'ANONYMOUS' at master log log-bin.000007, end_log_pos 42012585; Could not execute Delete_rows event on table zabbix.history_uint; Can't find record in 'history_uint', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log log-bin.000007, end_log_pos 42012585
Have you ever encountered such a situation?