Hi,
I am running MySQL DB server 8.0.31 (mysql-community-server-8.0.31-1.el7.x86_64) on the CentOS Linux release 7.9.2009 (Core) operating system and have enabled replication between Master and Standby.
I am currently encountering the error Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND as found in mysqld.log file on Standby server.
cat mysqld.log file ################################################################################################################################## 2023-06-12T07:34:49.698300Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31) starting as process 9942 2023-06-12T07:34:49.716114Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2023-06-12T07:34:50.203380Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2023-06-12T07:34:50.560267Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2023-06-12T07:34:50.560375Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 2023-06-12T07:34:50.565553Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/data' in the path is accessible to all OS users. Consider choosing a different directory. 2023-06-12T07:34:50.592935Z 0 [Warning] [MY-010604] [Repl] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=devportal2-relay-bin' to avoid this problem. 2023-06-12T07:34:50.608947Z 5 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 2023-06-12T07:34:50.611076Z 5 [System] [MY-010562] [Repl] Slave I/O thread for channel '': connected to master 'replicauser@10.1.12.4:3306',replication started in log 'mysql-bin.000007' at position 97065730 2023-06-12T07:34:50.618090Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.31' socket: '/data/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL. 2023-06-12T07:34:50.618355Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock 2023-06-12T07:41:32.562350Z 8 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000007, end_log_pos 97155197; Could not execute Update_rows event on table mb.cache_apigee_edge_entity; Can't find record in 'cache_apigee_edge_entity', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000007, end_log_pos 97155197, Error_code: MY-001032 2023-06-12T07:41:32.563449Z 6 [Warning] [MY-010584] [Repl] Slave SQL for channel '': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: MY-001756 ##################################################################################################################################
I am sharing the /etc/my.cnf file for both Master and Standby MySQL DB server.
Master -> /etc/my.cnf file --------------------------------------------------------------------------------------------------------------
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove the leading "# " to disable binary logging # Binary logging captures changes between backups and is enabled by # default. It's default setting is log_bin=binlog # disable_log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M # # Remove leading # to revert to previous value for default_authentication_plugin, # this will increase compatibility with older clients. For background, see: # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_... # default-authentication-plugin=mysql_native_password bind-address=192.168.1.10 server-id=1 log_bin = /data/mysql/logs/mysql-bin.log datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock
log-error=/data/mysql/logs/mysqld.log pid-file=/var/run/mysqld/mysqld.pid --------------------------------------------------------------------------------------------------------------
Standby -> /etc/my.cnf file
-------------------------------------------------------------------------------------------------------------- # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove the leading "# " to disable binary logging # Binary logging captures changes between backups and is enabled by # default. It's default setting is log_bin=binlog # disable_log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M # # Remove leading # to revert to previous value for default_authentication_plugin, # this will increase compatibility with older clients. For background, see: # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_... # default-authentication-plugin=mysql_native_password
server-id=2 read_only = 1 max_binlog_size = 500M log_bin = /data/mysql/logs/mysql-bin.log datadir=/data/mysql socket=/data/mysql/mysql.sock
log-error=/data/mysql/logs/mysqld.log pid-file=/data/mysql/mysqld.pid --------------------------------------------------------------------------------------------------------------
Please guide me. Thanks in Advance.
Best Regards,
Kaushal