Mysql replication, replica not running inserts

Question

I was trying to rebuild a MASTER - MASTER replication after a server reinstall and ran into a replication error after starting the slave.

MASTER conf:

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION

# MyISAM #
key-buffer-size                = 32M

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING
server-id                      = 2
binlog_do_db                   = genericdbname
log-slave-updates
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 7
sync-binlog                    = 1
binlog_format                  = ROW
auto_increment_increment       = 2
auto_increment_offset          = 2
relay_log_info_repository=TABLE
relay_log_recovery=ON

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 2000
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 100M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 50G
innodb_buffer_pool_instances   = 3

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
slow-query-log                 = 1
slow-query-log-file            = /var/log/mysql/mysql-slow.log

SLAVE, former second master conf:

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION
explicit_defaults_for_timestamp=OFF

skip-slave-start

# MyISAM #
key-buffer-size                = 32M

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING 
server-id                      = 1
binlog_do_db                   = genericdbname
log-bin                        = /var/lib/mysql/mysql-bin
log-slave-updates
expire-logs-days               = 14
sync-binlog                    = 1
binlog_format                  = ROW
auto_increment_increment       = 2
auto_increment_offset          = 1
relay_log_info_repository=TABLE
relay_log_recovery=ON

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 2000
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 100M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 50G
innodb_buffer_pool_instances   = 3

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log
long_query_time                = 4

The issue is that inserts and updates from the relay log are not running on the SLAVE. The replication stops when it arrives to an update of a missing record. Binlog and relay log didn't offer any clues, I don't know how to catch the error replication receives when running the queries.

Slave: 5.7.34-37 Master: 5.7.26-29

PS: queries are written in MASTER binlog and in SLAVE relay log

Any hint on where to look or how to debug this would be greatly appreciated. I'll update the question with any info you'd like to look at.

Answer 1

Jesus, I finally got it.

So, in short, the problem was the server id, which was the same as the one from the old master, before reinstall.

When I prepared the reinstall, I made a snapshot on the master that will be remaining live, redirecting all traffic to the master that will be reinstalled. So queries with the server id of the master that would be reinstalled ended up in the binlog. I copied the old conf to the newly installed server and ended up with the same server id. The trouble is that replicas ignore queries from binlog that are signed with the same server id they currently have.

I fixed the issue setting a new server id to the reinstalled master.