0

I have a Master-Master Replication with the following Configuration. Due to some patches, we have to Reboot the both the masters. But when i check the replication i s broken.

MASTER 1:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0

bind-address=Master 1
port=3306
query_cache_size=16M
query_cache_limit=4M
tmp_table_size=64M
max_heap_table_size=64M
key_buffer_size=32M
table_open_cache=32
innodb_file_per_table=1
max_connections=1000
server_id=2
binlog_format='MIXED'
log-bin=/var/log/mariadb/mariadb-bin.log
max_allowed_packet=1000M
general_log=1
auto_increment_increment=2
auto_increment_offset=2



[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

MASTER2:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
bind-address=Master2
port=3306
query_cache_size=16M
query_cache_limit=4M
tmp_table_size=64M
max_heap_table_size=64M
key_buffer_size=32M
table_open_cache=32
innodb_file_per_table=1
#innodb_force_recovery=5
max_connections=1000
server_id=1
binlog_format='MIXED'
log-bin=/var/log/mariadb/mariadb-bin.log
max_allowed_packet=1000M
general_log=1
auto_increment_increment=2
auto_increment_offset=1

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

While configuring, i researched a lot and found if we set increment values, the replication resumes after the replication crashes, but in my case i see the replication break with the following error.

ERROR:

190428  3:21:02 [ERROR] Slave SQL: Could not execute Update_rows event on table nagios.nagios_servicestatus; Can't find record in 'nagios_servicestatus', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mariadb-bin.000002, end_log_pos 259533668, Error_code: 1032
190428  3:21:02 [Warning] Slave: Can't find record in 'nagios_servicestatus' Error_code: 1032
190428  3:21:02 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mariadb-bin.000002' position 259532745
190429  9:31:32 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
190429  9:31:32 [Note] Slave I/O thread killed while reading event
190429  9:31:32 [Note] Slave I/O thread exiting, read up to log 'mariadb-bin.000002', position 369070380
190429  9:31:33 [Note] Slave SQL thread initialized, starting replication in log 'mariadb-bin.000002' at position 259532745, relay log './mariadb-relay-bin.000002' position: 143476361
190429  9:31:33 [Note] 'SQL_SLAVE_SKIP_COUNTER=1' executed at relay_log_file='./mariadb-relay-bin.000002', relay_log_pos='143476361', master_log_name='mariadb-bin.000002', master_log_pos='259532745' and new position at relay_log_file='./mariadb-relay-bin.000002', relay_log_pos='143477368', master_log_name='mariadb-bin.000002', master_log_pos='259533752'
190429  9:31:33 [ERROR] Slave SQL: Could not execute Update_rows event on table nagios.nagios_customvariablestatus; Can't find record in 'nagios_customvariablestatus', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mariadb-bin.000002, end_log_pos 259534029, Error_code: 1032
190429  9:31:33 [Warning] Slave: Can't find record in 'nagios_customvariablestatus' Error_code: 1032
190429  9:31:33 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mariadb-bin.000002' position 259533752

Do i need to add any more configuration to my.cnf to auto-recover replication after reboot?

Thanks,

  • Smells like a bug or mis-configuration in Nagios. – Rick James Jul 15 at 0:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.