0

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?

scorch
  • 1
  • 1
  • setting seems fine. Make sure, during testing for traffic switch db was in sync. – ROHIT KHURANA Jan 21 '21 at 10:48
  • Yes, the databases were identical. 2 new servers were created, all installed and configured, and the same database dump was deployed. – scorch Jan 21 '21 at 11:30

1 Answers1

0

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.

ROHIT KHURANA
  • 903
  • 7
  • 13
  • Sorry, but I don't understand why this check is needed. Can you explain? I am sure that replication will work, provided that no changes are made. I did exactly the same check, provided that both servers had write and read permissions. And it worked correctly for two days. – scorch Jan 21 '21 at 11:27
  • it depends on application logic as well. If application read from db then write accordingly. so case is if any transaction is not replicated and application read stale data so new write would be accordingly which cause duplicate issue. – ROHIT KHURANA Jan 21 '21 at 11:46