MySQL 5.6复制报错

Slave_IO_Running:YesSlave_SQL_Running:NoReplicate_Do_DB:app_push_centerReplicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:1032Last_Error:CouldnotexecuteUpdate_rowseventontableapp_push_center.app_device;Can'tfindrecordin'who_app_device',Error_code:1032;handlererrorHA_ERR_KEY_NOT_FOUND;theevent'smasterlogmysql-bin.000693,end_log_pos28549933Skip_Counter:0Exec_Master_Log_Pos:28549155Relay_Log_Space:37322136Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:NULLMaster_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:1032Last_SQL_Error:CouldnotexecuteUpdate_rowseventontablewho_app_push_center.app_device;Can'tfindrecordin'who_app_device',Error_code:1032;handlererrorHA_ERR_KEY_NOT_FOUND;theevent'smasterlogmysql-bin.000693,end_log_pos28549933Replicate_Ignore_Server_Ids:

报错原因：

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

修复方法：

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

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

#cp/data/mysql3308/mysql-bin.000693.#mysqlbinlogmysql-bin.000693-v>20181025.log#grep-B502854993320181025.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-1306:21:24'###@7='2018-10-2513:23:43'###@8=1###@9='c5051a9f-a54c-49a0-80a3-2290d14a5397'###@10='7.4.1'###@11=1###@12=3.00###@13='SaudiArabia'###@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-1306:21:24'###@7='2018-10-2513:24:08'###@8=1###@9='c5051a9f-a54c-49a0-80a3-2290d14a5397'###@10='7.4.1'###@11=1###@12=3.00###@13='SaudiArabia'###@14='en'###@15=0###@16=0###@17=0#at28549124#18102513:24:08serverid4024108end_log_pos28549155CRC320x1df3385bXid=5088151913COMMIT/*!*/;#at28549155#18102513:24:08serverid4024108end_log_pos28549247CRC320xaf120f59Querythread_id=12489417exec_time=0error_code=0SETTIMESTAMP=1540445048/*!*/;BEGIN/*!*/;#at28549247#18102513:24:08serverid4024108end_log_pos28549351CRC320xd609f120Table_map:`who_app_push_center`.`who_app_device`mappedtonumber180#at28549351#18102513:24:08serverid4024108end_log_pos28549933CRC320xbe120930Update_rows:tableid180flags: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-1609:45:17'###@7='2018-10-2317:47:16'###@8=1###@9='61e7e65a-e1eb-4907-b231-8edfffb26430'###@10='7.4.1'###@11=1###@12=3.00###@13='SaudiArabia'###@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-1609:45:17'###@7='2018-10-2513:24:08'###@8=1###@9='61e7e65a-e1eb-4907-b231-8edfffb26430'###@10='7.4.1'###@11=1###@12=3.00###@13='SaudiArabia'###@14='ar'###@15=0###@16=0###@17=0#at28549933

主库执行查询

mysql>select*fromdevicewhereid=96029227\G***************************1.row***************************id:96029227user_id:43018933register_id:dP8OSD1_4U8:APA91bG5oQWPs0r_yioNFfZvY1hqhcTl9J0PfhqNDhdHHHv2nxxXtukvk6cLM4SnnE6nOXUlVCI3CeHdCnXflwT-lJ2B-0iQxxeG0vupFQuFhvsd6uT1P1SN3eh0o-Mm2__F3-XFW5MWdtxXDKdjoO4dYHdOSBjhfAtype:1is_start:0create_time:2018-08-1609:45:17update_time:2018-10-2513:24:08status:1cookie_id:61e7e65a-e1eb-4907-b231-8edfffb26430app_version:7.4.1receive_notification:1timezone:3.00country_name:SaudiArabialang:arpush_flag:0app_id:0pkg:01rowinset(0.00sec)

从库执行查询

mysql>select*fromapp_devicewhereid=96029227\GEmptyset(0.00sec)

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

使用下面的条目，作为插入条件，按照字段顺序写一条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-1609:45:17'###@7='2018-10-2317:47:16'###@8=1###@9='61e7e65a-e1eb-4907-b231-8edfffb26430'###@10='7.4.1'###@11=1###@12=3.00###@13='SaudiArabia'###@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-1609:45:17'###@7='2018-10-2513:24:08'###@8=1###@9='61e7e65a-e1eb-4907-b231-8edfffb26430'###@10='7.4.1'###@11=1###@12=3.00###@13='SaudiArabia'###@14='ar'###@15=0###@16=0###@17=0

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

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

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

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

mysql>showglobalvariableslike'slave_skip_errors';+-------------------+-----------+|Variable_name|Value|+-------------------+-----------+|slave_skip_errors|1032,1062|+-------------------+-----------+1rowinset(0.00sec)