MySQL 5.6复制报错Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;
短信预约 -IT技能 免费直播动态提醒
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: NULL
Master_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.log
gwAMU2F1ZGkgQXJhYmlhAmVuAAAAIHOBCw==
';
### 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 = 5088151913
COMMIT;
# at 28549155
#181025 13:24:08 server id 4024108 end_log_pos 28549247 CRC32 0xaf120f59 Query thread_id=12489417 exec_time=0 error_code=0
SET 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+K0q5BbVqkAKuAGRQOE9TRDFf
NFU4OkFQQTkxYkc1b1FXUHMwcl95aW9ORmZadlkxaHFoY1RsOUowUGZocU5EaGRISEh3Mm54eFh0
dWt2azZjTE00U25uRTZuT1hVbFZDSTNDZUhkQ25YZmx3VC1sSjJCLTBpUXh5ZUcwdnVwRlF1Rmh3
c2Q2dVQxUDFTTjNlaDBvLU1tMl9fRjMtWEZXNU1XZHR4WERLZGpvTzRkWUhkT1NCamhmQQEAAAAA
AAAAmaCgm1GZoS8b0AEkNjFlN2U2NWEtZTFlYi00OTA3LWIyMzEtOGVkZmZmYjI2NDMwBTcuNC4x
AYMADFNhdWRpIEFyYWJpYQJhcgAAAAAA/itKuQW1apACrgBkUDhPU0QxXzRVODpBUEE5MWJHNW9R
V1BzMHJfeWlvTkZmWnZZMWhxaGNUbDlKMFBmaHFORGhkSEhIdjJueHhYdHVrdms2Y0xNNFNubkU2
bk9YVWxWQ0kzQ2VIZENuWGZsd1QtbEoyQi0waVF4eGVHMHZ1cEZRdUZodnNkNnVUMVAxU04zZWgw
by1NbTJfX0YzLVhGVzVNV2R0eFhES2Rqb080ZFlIZE9TQmpoZkEBAAAAAAAAAJmgoJtRmaEy1ggB
JDYxZTdlNjVhLWUxZWItNDkwNy1iMjMxLThlZGZmZmIyNjQzMAU3LjQuMQGDAAxTYXVkaSBBcmFi
aWECYXIAAAAwCRK+
';
### 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.1
receive_notification: 1
timezone: 3.00
country_name: Saudi Arabia
lang: ar
push_flag: 0
app_id: 0
pkg: 0
1 row in set (0.00 sec)
从库执行查询
mysql > select * from app_device where id=96029227\G
Empty 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)
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/19.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
