MySQL數(shù)據(jù)庫(kù)經(jīng)典錯(cuò)誤四 Last_SQL_Errno: 1032(從庫(kù)少數(shù)據(jù),主庫(kù)更新的時(shí)候,從庫(kù)報(bào)錯(cuò))
2018-11-08 20:15:48
10307
Last_SQL_Errno: 1032(從庫(kù)少數(shù)據(jù),主庫(kù)更新的時(shí)候,從庫(kù)報(bào)錯(cuò))
Last_SQL_Error:
Could not execute Update_rows event on table test.t; Can’t find record
in ‘t’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the
event’s master log mysql-bin.000014, end_log_pos 1708
解決問題的辦法:根據(jù)報(bào)錯(cuò)信息,我們可以獲取到報(bào)錯(cuò)日志和position號(hào),然后就能找到主庫(kù)執(zhí)行的哪條sql,導(dǎo)致的主從報(bào)錯(cuò)。
在主庫(kù)執(zhí)行:
/usr/local/mysql/bin/mysqlbinlog –no-defaults -v -v –base64-output=decode-rows /data/mysql/mysql-bin.000014 |grep -A 10 1708 > 1.log
cat 1.log
#170720 14:20:15 server id 3 end_log_pos 1708 CRC32 0x97b6bdec Update_rows: table id 113 flags: STMT_END_F
### UPDATE `test`.`t`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2=’dd’ /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2=’ddd’ /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 1708
#170720 14:20:15 server id 3 end_log_pos 1739 CRC32 0xecaf1922 Xid = 654
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
獲取到SQL語句之后,就可以在從庫(kù)反向執(zhí)行SQL語句。把從庫(kù)缺少的SQL語句補(bǔ)全,解決報(bào)錯(cuò)信息。
在從庫(kù)依次執(zhí)行:
mysql> insert into t (b) values (‘ddd’);
Query OK, 1 row affected (0.01 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@node4 bin]# ./pt-slave-restart -uroot -proot123
2017-07-20T14:31:37 p=…,u=root node4-relay-bin.000005 283 1032