Shulou(Shulou.com)06/01 Report--

MySQL 5.6replication error

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:

Reason for reporting an error:

When the update statement was executed from above the library, the corresponding record was not found.

Repair method:

Go to the master library to find the corresponding record, and manually insert the missing entries into the table corresponding to the slave library.

Use mysqlbinlog to parse the binlog log, find the record point stopped from the library, and then find the corresponding statement (find the updated primary key condition @ 1room96029227).

# cp / data/mysql3308/mysql-bin.000693. # mysqlbinlog mysql-bin.000693-v > 20181025.log# grep-B50 28549933 20181025.loggwAMU2F1ZGkgQXJhYmlhAmAmVuAAAAIHOBCwcountries # UPDATE `center`.`app _ device` # WHERE### @ 1 "99457133" # @ 2 "47189303" # @ 3='e-s-Zv-0FRs:APA91bHgKnXcKClKisl3P1KWiii0SuQtWsNrTRpwyByz86Sa8d2Vzj5g8LTMWeot0ue1g26fQnLpl1gmZVONKpIrLqbVS7GGuxdn6wgn0vewntMW-mQowZibAZ1jvJB41ThrAgIWQQMu'### @ 4 "1", "2018-09-13 0621 24", "7", 2018-10-25, 13, 13, 23, 24, "43", "9", c5051a9f- A54c-49a0-80a3-2290d14a5397'### @ 10, "7.4.1", "11", "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'##". 6, 2018-09-13 06, 21, 21, 24, 8, 8, 10, 25, 13, 13, 13, 25, 13, 24, 24, 8, 8, 8, 8, 1, 8, 8, 8, 1, 8, 8, 8, 1, 8, 8, 8, 1, 8, 8, 1, 8, 8, 8, 1, 8, 8, 8, 1, 8, 8, 8, 1, 8, 8, 8, 8, 1, 8, 8, 8, 8, 1, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 1, 8, 8, 8, 8, 8, 1, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, @ 14='en'### @ 150mm at 28549124181025 13:24:08 server id 4024108 end_log_pos 28549155 CRC32 0x1df3385b Xid = 5088151913COMMIT Universe # 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 1540445048According to the BEGINAccording to the BEGINAccording to # 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 `center`.`who _ app_ device` # WHERE### @ 1 "96029227" # @ 2" 43018933 "# @ 3='dP8OSD1_4U8:APA91bG5oQWPs0r_yioNFfZvY1hqhcTl9J0PfhqNDhdHHHv2nxxXtukvk6cLM4SnnE6nOXUlVCI3CeHdCnXflwT-lJ2B-0iQxxeG0vupFQuFhvsd6uT1P1SN3eh0o-Mm2__F3-XFW5MWdtxXDKdjoO4dYHdOSBjhfA'### @ 4" 1 "," 2018-08-16 0945 purr 17 "" 2018-08-16 0945 "17" 7 "" 2018-10-23 17 display 4714 " 8 / 1 / 10 / 7 / 4 / 1 / 11 / 1 / 11 / 1 / 12 / 12 / 3 / 00 / 10 / 10 / 12 / 12 / 3 / 00 / 10 / 10 / 10 / 10 / 12 / 12 / 3 / 10 / 10 / 10 / 10 / 10 / 11 / 11 / 1 / 11 / 11 / 1 / 10 / 10 / 10 / 10 / 11 / 11 / 1 / 11 / 11 / 11 / 11 / 1 / 11 / 11 / 1 / 10 / 10 / 10 / 10 / 11 / 11 / 1 / 11 / 11 / 1 / 10 / 10 / 10 / 10 / 11 / 11 / 1 / 11 / 11 / 1 / 11 / 11 / 1 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 1 / 10 / 10 / 10 / 10 / 10 / : APA91bG5oQWPs0r_yioNFfZvY1hqhcTl9J0PfhqNDhdHHHv2nxxXtukvk6cLM4SnnE6nOXUlVCI3CeHdCnXflwT-lJ2B-0iQxxeG0vupFQuFhvsd6uT1P1SN3eh0o-Mm2__F3-XFW5MWdtxXDKdjoO4dYHdOSBjhfA'### @ 4 "1" # @ 5 "0" # @ 6 "" 2018-08-16 09 "45" 17 "" 7 "" 2018-10-25 13 24 24 "08"8"8" 1 "" # @ 9='61e7e65a-e1eb-4907-b231-8edfffb26430'### @ 10 "" 7.4.1 "" # @ 11 "1" 12 percent 3.00 percent # @ 13='Saudi Arabia'### @ 14='ar'### @ 15 percent percent # @ 16 percent at 28549933

Main database executes query

Mysql > select * from device where id=96029227\ gateway * 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)

Execute a query from the library

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

Then manually insert the missing record from the library, and then restart replication.

Using the following entry as a condition for insertion, write a SQL in the order of the fields and execute it on top of the slave library.

# UPDATE `center`.`who _ app_ device` # WHERE### @ 1 "96029227" # @ 2" 43018933 "# @ 3='dP8OSD1_4U8:APA91bG5oQWPs0r_yioNFfZvY1hqhcTl9J0PfhqNDhdHHHv2nxxXtukvk6cLM4SnnE6nOXUlVCI3CeHdCnXflwT-lJ2B-0iQxxeG0vupFQuFhvsd6uT1P1SN3eh0o-Mm2__F3-XFW5MWdtxXDKdjoO4dYHdOSBjhfA'### @ 4" 1 "," 2018-08-16 0945 purr 17 "" 2018-08-16 0945 "17" 7 "" 2018-10-23 17 display 4714 " 8 / 1 / 10 / 7 / 4 / 1 / 11 / 1 / 11 / 1 / 12 / 12 / 3 / 00 / 10 / 10 / 12 / 12 / 3 / 00 / 10 / 10 / 10 / 10 / 12 / 12 / 3 / 10 / 10 / 10 / 10 / 10 / 11 / 11 / 1 / 11 / 11 / 1 / 10 / 10 / 10 / 10 / 11 / 11 / 1 / 11 / 11 / 11 / 11 / 1 / 11 / 11 / 1 / 10 / 10 / 10 / 10 / 11 / 11 / 1 / 11 / 11 / 1 / 10 / 10 / 10 / 10 / 11 / 11 / 1 / 11 / 11 / 1 / 11 / 11 / 1 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 1 / 10 / 10 / 10 / 10 / 10 / : APA91bG5oQWPs0r_yioNFfZvY1hqhcTl9J0PfhqNDhdHHHv2nxxXtukvk6cLM4SnnE6nOXUlVCI3CeHdCnXflwT-lJ2B-0iQxxeG0vupFQuFhvsd6uT1P1SN3eh0o-Mm2__F3-XFW5MWdtxXDKdjoO4dYHdOSBjhfA'### @ 4 "1" # @ 5 "0" # @ 6 "" 2018-08-16 09 "45" 17 "" 7 "" 2018-10-25 13 24 24 "08"8"8" 1 "" # @ 9='61e7e65a-e1eb-4907-b231-8edfffb26430'### @ 10 "" 7.4.1 "" # @ 11 "1" 12 percent 3.00 percent # @ 13='Saudi Arabia'### @ 14='ar'### @ 15 percent percent # @ 16 percent percent number # @ 17 percent

So why are there missing data entries?

If an error is reported, the backup used by the slave library is made on the backup slave library.

Check the replication parameters of the backup slave library and find that there are replication error skip parameters in the parameters.

This is the cause of the replication interruption. Remove the replication error skip parameter and re-build the backup slave database.

Mysql > show global variables like 'slave_skip_errors' +-+-+ | Variable_name | Value | +-+-+ | slave_skip_errors | 1032 row in set 1062 | +-+-+ 1 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.