The reason for the error is that the master database service is restarted abnormally, which causes the slave database replication to report 1032 errors
In other cases, the slave does not limit the read-only permission. When the development or operation and maintenance personnel mistakenly delete the data on the slave database, which results in the master-slave copy error 1032 when the master database update data is generated, because the records on the slave database are mistakenly deleted in advance, the master-slave copy is out of sync.
resolvent:
Many novices choose my.cnf to configure slave skip errors = 1032 to skip 1032 ERROR messages in the log, or set global SQL > slave skip counter = 1; stop slave; start slave;
However, the above methods are not desirable, because there is no such one on the slave database. When the master database is updated again, the slave will report an error. So this 1032 error must be solved. How to solve it? Let's talk about it in detail
show master status \G;
The replication error is as follows:
Could not execute Update_rows event on table appdb.hlz_ad_voucher; Can't find record in 'hlz_ad_voucher', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000427, end_log_pos 9279278
Solution: according to the binglog location point at the end of error reporting, extract the SQL record when updating the record on the main database by parsing the binlog file of the main database.
The following is the SQL method to get the update record on the master:
[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /data/mysql/binlog/mysql-bin.000427|grep -A 15 9279278|sed -n '/### UPDATE `appdb`.`hlz_ad_voucher`/,/COMMIT/p'|grep -B 100 '# at 9279278' ### UPDATE `appdb`.`hlz_ad_voucher` ### WHERE ### @1=9400072 /* INT meta=0 nullable=0 is_null=0 */ ### @2=2994652 /* INT meta=0 nullable=0 is_null=0 */ ### @3=0.50 /* DECIMAL(5,2) meta=1282 nullable=0 is_null=0 */ ### @4=0 /* INT meta=0 nullable=0 is_null=0 */ ### @5=0 /* INT meta=0 nullable=0 is_null=0 */ ### @6=1 /* INT meta=0 nullable=0 is_null=0 */ ### @7=0 /* INT meta=0 nullable=0 is_null=0 */ ### @8='2020:05:05' /* DATE meta=0 nullable=0 is_null=0 */ ### @9 = 'new gift' / * varstring (1020) meta = 1020 nullable = 0 is null = 0*/ ### @10='2020-05-05 21:14:20.745' /* DATETIME(3) meta=3 nullable=0 is_null=0 */ ### @11='2020-05-05 21:14:20.745' /* DATETIME(3) meta=3 nullable=0 is_null=0 */ ### @12=0 /* INT meta=0 nullable=0 is_null=0 */ ### @13=0 /* INT meta=0 nullable=0 is_null=0 */ -- #at 9279278
Convert the SQL obtained from the update record to the insert into SQL statement, then insert it into the table corresponding to the slave database, and finally stop slave;start slave;show slave status\G
[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /data/mysql/binlog/mysql-bin.000427|grep -A 15 9279278|sed -n '/### UPDATE `appdb`.`hlz_ad_voucher`/,/COMMIT/p'|grep -B 100 '# at 9279278'|sed 's/### //g;s/\/\*.*/,/g;s/UPDATE/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@13.*),/\1;/g' | sed 's/@\([0-9]\+\)=//g;' INSERT INTO `appdb`.`hlz_ad_voucher` SELECT 9400072 , 2994652 , 0.50 , 0 , 0 , 1 , 0 , '2020:05:05' , 'New gift' , '2020-05-05 21:14:20.745' , '2020-05-05 21:14:20.745' , 0 , 0 ; -- #at 9279278
Repeat the above method to extract the SQL in the binlog file of the master, then convert it to insert into SQL, and then insert the table corresponding to the slave database, stop slave;start slave; until no more errors are reported.