溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
MySQL 5.6復(fù)制報(bào)錯(cuò)
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:
報(bào)錯(cuò)原因：
從庫上面執(zhí)行更新語句時(shí)，找不到對(duì)應(yīng)的記錄。
修復(fù)方法：
去主庫上面找到對(duì)應(yīng)的記錄，手動(dòng)插入缺少的條目到從庫對(duì)應(yīng)的表中。
使用mysqlbinlog解析binlog日志，找到從庫停止的記錄點(diǎn)，進(jìn)而找到對(duì)應(yīng)的語句（找到更新的主鍵條件@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
主庫執(zhí)行查詢
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)
從庫執(zhí)行查詢
mysql > select * from app_device where id=96029227\G Empty set (0.00 sec)
之后手動(dòng)在從庫上面插入缺少的記錄，之后重啟復(fù)制。
使用下面的條目，作為插入條件，按照字段順序?qū)懸粭lSQL，在從庫上面執(zhí)行。
### 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
那么為什么會(huì)出現(xiàn)數(shù)據(jù)條目缺失呢？
報(bào)錯(cuò)的從庫所使用的備份是在備份從庫上面做的。
檢查備份從庫的復(fù)制參數(shù)，發(fā)現(xiàn)參數(shù)里面有復(fù)制報(bào)錯(cuò)跳過參數(shù)。
這是導(dǎo)致發(fā)生復(fù)制中斷的原因。去掉復(fù)制報(bào)錯(cuò)跳過參數(shù)，重新搭建備份從庫。
mysql> show global variables like 'slave_skip_errors'; +-------------------+-----------+ | Variable_name | Value | +-------------------+-----------+ | slave_skip_errors | 1032,1062 | +-------------------+-----------+ 1 row in set (0.00 sec)
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼
