导航：首页 > 数据库 >

MySQL 5.6复制报错Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;

发表于：2024-11-20 作者：千家信息网编辑

千家信息网最后更新 2024年11月20日，MySQL 5.6复制报错 Slave_IO_Running: Yes Slave_SQL_Running: No Replic

千家信息网最后更新 2024年11月20日MySQL 5.6复制报错Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;

MySQL 5.6复制报错

             Slave_IO_Running: Yes            Slave_SQL_Running: No              Replicate_Do_DB: app_push_center          Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 1032                   Last_Error: Could not execute Update_rows event on table app_push_center.app_device; Can't find record in 'who_app_device', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000693, end_log_pos 28549933                 Skip_Counter: 0          Exec_Master_Log_Pos: 28549155              Relay_Log_Space: 37322136              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 1032               Last_SQL_Error: Could not execute Update_rows event on table who_app_push_center.app_device; Can't find record in 'who_app_device', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000693, end_log_pos 28549933  Replicate_Ignore_Server_Ids:

报错原因：

从库上面执行更新语句时，找不到对应的记录。

修复方法：

去主库上面找到对应的记录，手动插入缺少的条目到从库对应的表中。

使用mysqlbinlog解析binlog日志，找到从库停止的记录点，进而找到对应的语句（找到更新的主键条件@1=96029227）。

# cp /data/mysql3308/mysql-bin.000693 .# mysqlbinlog mysql-bin.000693 -v > 20181025.log# grep -B50 28549933 20181025.loggwAMU2F1ZGkgQXJhYmlhAmVuAAAAIHOBCw=='/*!*/;### UPDATE `app_push_center`.`app_device`### WHERE###   @1=99457133###   @2=47189303###   @3='e-s-Zv-0FRs:APA91bHgKnXcKClKisl3P1KWiii0SuQtWsNrTRpwyByz86Sa8d2Vzj5g8LTMWeot0ue1g26fQnLpl1gmZVONKpIrLqbVS7GGuxdn6wgn0vewntMW-mQowZibAZ1jvJB41ThrAgIWQQMu'###   @4=1###   @5=0###   @6='2018-09-13 06:21:24'###   @7='2018-10-25 13:23:43'###   @8=1###   @9='c5051a9f-a54c-49a0-80a3-2290d14a5397'###   @10='7.4.1'###   @11=1###   @12=3.00###   @13='Saudi Arabia'###   @14='en'###   @15=0###   @16=0###   @17=0### SET###   @1=99457133###   @2=47189303###   @3='e-s-Zv-0FRs:APA91bHgKnXcKClKisl3P1KWiii0SuQtWsNrTRpwyByz86Sa8d2Vzj5g8LTMWeot0ue1g26fQnLpl1gmZVONKpIrLqbVS7GGuxdn6wgn0vewntMW-mQowZibAZ1jvJB41ThrAgIWQQMu'###   @4=1###   @5=0###   @6='2018-09-13 06:21:24'###   @7='2018-10-25 13:24:08'###   @8=1###   @9='c5051a9f-a54c-49a0-80a3-2290d14a5397'###   @10='7.4.1'###   @11=1###   @12=3.00###   @13='Saudi Arabia'###   @14='en'###   @15=0###   @16=0###   @17=0# at 28549124#181025 13:24:08 server id 4024108  end_log_pos 28549155 CRC32 0x1df3385b     Xid = 5088151913COMMIT/*!*/;# at 28549155#181025 13:24:08 server id 4024108  end_log_pos 28549247 CRC32 0xaf120f59     Query   thread_id=12489417      exec_time=0     error_code=0SET TIMESTAMP=1540445048/*!*/;BEGIN/*!*/;# at 28549247#181025 13:24:08 server id 4024108  end_log_pos 28549351 CRC32 0xd609f120     Table_map: `who_app_push_center`.`who_app_device` mapped to number 180# at 28549351#181025 13:24:08 server id 4024108  end_log_pos 28549933 CRC32 0xbe120930     Update_rows: table id 180 flags: STMT_END_F--ZGV2aWNlABEDAw8DAxISAQ8PAfYP/gEBAQ79AgAAlgAeAAQC8AD+CXpIACDxCdY=eFPRWx8sZz0ARgIAAC2jswEAALQAAAAAAAEAAgAR////////AAD+K0q5BbVqkAKuAGRQOE9TRDFfNFU4OkFQQTkxYkc1b1FXUHMwcl95aW9ORmZadlkxaHFoY1RsOUowUGZocU5EaGRISEh3Mm54eFh0dWt2azZjTE00U25uRTZuT1hVbFZDSTNDZUhkQ25YZmx3VC1sSjJCLTBpUXh5ZUcwdnVwRlF1Rmh3c2Q2dVQxUDFTTjNlaDBvLU1tMl9fRjMtWEZXNU1XZHR4WERLZGpvTzRkWUhkT1NCamhmQQEAAAAAAAAAmaCgm1GZoS8b0AEkNjFlN2U2NWEtZTFlYi00OTA3LWIyMzEtOGVkZmZmYjI2NDMwBTcuNC4xAYMADFNhdWRpIEFyYWJpYQJhcgAAAAAA/itKuQW1apACrgBkUDhPU0QxXzRVODpBUEE5MWJHNW9RV1BzMHJfeWlvTkZmWnZZMWhxaGNUbDlKMFBmaHFORGhkSEhIdjJueHhYdHVrdms2Y0xNNFNubkU2bk9YVWxWQ0kzQ2VIZENuWGZsd1QtbEoyQi0waVF4eGVHMHZ1cEZRdUZodnNkNnVUMVAxU04zZWgwby1NbTJfX0YzLVhGVzVNV2R0eFhES2Rqb080ZFlIZE9TQmpoZkEBAAAAAAAAAJmgoJtRmaEy1ggBJDYxZTdlNjVhLWUxZWItNDkwNy1iMjMxLThlZGZmZmIyNjQzMAU3LjQuMQGDAAxTYXVkaSBBcmFiaWECYXIAAAAwCRK+'/*!*/;### UPDATE `who_app_push_center`.`who_app_device`### WHERE###   @1=96029227###   @2=43018933###   @3='dP8OSD1_4U8:APA91bG5oQWPs0r_yioNFfZvY1hqhcTl9J0PfhqNDhdHHHv2nxxXtukvk6cLM4SnnE6nOXUlVCI3CeHdCnXflwT-lJ2B-0iQxxeG0vupFQuFhvsd6uT1P1SN3eh0o-Mm2__F3-XFW5MWdtxXDKdjoO4dYHdOSBjhfA'###   @4=1###   @5=0###   @6='2018-08-16 09:45:17'###   @7='2018-10-23 17:47:16'###   @8=1###   @9='61e7e65a-e1eb-4907-b231-8edfffb26430'###   @10='7.4.1'###   @11=1###   @12=3.00###   @13='Saudi Arabia'###   @14='ar'###   @15=0###   @16=0###   @17=0### SET###   @1=96029227###   @2=43018933###   @3='dP8OSD1_4U8:APA91bG5oQWPs0r_yioNFfZvY1hqhcTl9J0PfhqNDhdHHHv2nxxXtukvk6cLM4SnnE6nOXUlVCI3CeHdCnXflwT-lJ2B-0iQxxeG0vupFQuFhvsd6uT1P1SN3eh0o-Mm2__F3-XFW5MWdtxXDKdjoO4dYHdOSBjhfA'###   @4=1###   @5=0###   @6='2018-08-16 09:45:17'###   @7='2018-10-25 13:24:08'###   @8=1###   @9='61e7e65a-e1eb-4907-b231-8edfffb26430'###   @10='7.4.1'###   @11=1###   @12=3.00###   @13='Saudi Arabia'###   @14='ar'###   @15=0###   @16=0###   @17=0# at 28549933

主库执行查询

mysql> select * from device where id=96029227\G*************************** 1. row ***************************                  id: 96029227             user_id: 43018933         register_id: dP8OSD1_4U8:APA91bG5oQWPs0r_yioNFfZvY1hqhcTl9J0PfhqNDhdHHHv2nxxXtukvk6cLM4SnnE6nOXUlVCI3CeHdCnXflwT-lJ2B-0iQxxeG0vupFQuFhvsd6uT1P1SN3eh0o-Mm2__F3-XFW5MWdtxXDKdjoO4dYHdOSBjhfA                type: 1            is_start: 0         create_time: 2018-08-16 09:45:17         update_time: 2018-10-25 13:24:08              status: 1           cookie_id: 61e7e65a-e1eb-4907-b231-8edfffb26430         app_version: 7.4.1receive_notification: 1            timezone: 3.00        country_name: Saudi Arabia                lang: ar           push_flag: 0              app_id: 0                 pkg: 01 row in set (0.00 sec)

从库执行查询

mysql > select * from app_device where id=96029227\GEmpty set (0.00 sec)

之后手动在从库上面插入缺少的记录，之后重启复制。

使用下面的条目，作为插入条件，按照字段顺序写一条SQL，在从库上面执行。

### UPDATE `who_app_push_center`.`who_app_device`### WHERE###   @1=96029227###   @2=43018933###   @3='dP8OSD1_4U8:APA91bG5oQWPs0r_yioNFfZvY1hqhcTl9J0PfhqNDhdHHHv2nxxXtukvk6cLM4SnnE6nOXUlVCI3CeHdCnXflwT-lJ2B-0iQxxeG0vupFQuFhvsd6uT1P1SN3eh0o-Mm2__F3-XFW5MWdtxXDKdjoO4dYHdOSBjhfA'###   @4=1###   @5=0###   @6='2018-08-16 09:45:17'###   @7='2018-10-23 17:47:16'###   @8=1###   @9='61e7e65a-e1eb-4907-b231-8edfffb26430'###   @10='7.4.1'###   @11=1###   @12=3.00###   @13='Saudi Arabia'###   @14='ar'###   @15=0###   @16=0###   @17=0### SET###   @1=96029227###   @2=43018933###   @3='dP8OSD1_4U8:APA91bG5oQWPs0r_yioNFfZvY1hqhcTl9J0PfhqNDhdHHHv2nxxXtukvk6cLM4SnnE6nOXUlVCI3CeHdCnXflwT-lJ2B-0iQxxeG0vupFQuFhvsd6uT1P1SN3eh0o-Mm2__F3-XFW5MWdtxXDKdjoO4dYHdOSBjhfA'###   @4=1###   @5=0###   @6='2018-08-16 09:45:17'###   @7='2018-10-25 13:24:08'###   @8=1###   @9='61e7e65a-e1eb-4907-b231-8edfffb26430'###   @10='7.4.1'###   @11=1###   @12=3.00###   @13='Saudi Arabia'###   @14='ar'###   @15=0###   @16=0###   @17=0

那么为什么会出现数据条目缺失呢？

报错的从库所使用的备份是在备份从库上面做的。

检查备份从库的复制参数，发现参数里面有复制报错跳过参数。

这是导致发生复制中断的原因。去掉复制报错跳过参数，重新搭建备份从库。

mysql> show global variables like 'slave_skip_errors';+-------------------+-----------+| Variable_name     | Value     |+-------------------+-----------+| slave_skip_errors | 1032,1062 |+-------------------+-----------+1 row in set (0.00 sec)

很赞哦！