MySQL Master-Master replication error 1062

Question

The question is about MySQL-Master-Master replication. I have read a lot on the Internet, but there is a problem.

Source data:

A. MySQL 5.7. Part of my. cnf about replication:

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay-log = /var/log/mysql/mysql-relay-bin
relay-log-index = /var/log/mysql/mysql-relay-bin.index
binlog_do_db = example
expire-logs-days = 7
auto_increment_increment = 2
auto_increment_offset = 1
log-slave-updates = 1

B. MySQL 5.7. Part of my.cnf about replication:

server-id = 2
log_bin = /var/log/mysql/mysql-bin. log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay-log = /var/log/mysql/mysql-relay-bin
relay-log-index = /var/log/mysql/mysql-relay-bin.index
binlog_do_db = example
expire-logs-days = 7
auto_increment_increment = 2
auto_increment_offset = 2
log-slave-updates = 1

Server A is the active (primary) database server. Server B connects when server A is disabled / unavailable. In the beginning, the Master-Master replication was assembled (replication worked in both directions) and successfully worked for 2 days until I decided to test it. I started blocking access to server A to switch to server B and back. After several switches replication error 1062 appeared (Could not execute Write_rows event on table...).

And now question. What is incorrectly configured in my replication configuration? I understand that a lot depends on the product and the requests to it, but is there a universal Master-Master config?

Answer 1

Please consider below steps to test connection or failover:

let's assume, server A is active. So

• enable read only first on server A.
• then wait for couple of seconds and check show slave status twice
• if positions are not changing. then move traffic on server B.

Follow same steps to reverse traffic on server A.