【MySQL 5.7 】从库1032 报错处理

一、简介：

　　mysql 5.7 多源复制，主从同步的时候，主库update一条数据，从库这条数据已经被删除，导致主从断掉。

　　Last_Errno: 1032

　　MySQL主从同步的1032错误，一般是指要更改的数据不存在，SQL_THREAD提取的日志无法应用故报错，造成同步失败

（Update、Delete、Insert一条已经delete的数据）。1032的错误本身对数据一致性没什么影响，影响最大的是造成了同步失败.

二、解决方案：

　　MySQL5.7.15-log版本，binlog模式为ROW。

　　show slave statusG,可以看到如下报错：

 1  Last_Errno: 1032
 2                    Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 3 failed executing transaction '1699
 3 7e5e-5707-11e7-a702-94188201787c:161982297' at master log mysql-bin.000309, end_log_pos 774308000. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.                 Skip_Counter: 0
 4           Exec_Master_Log_Pos: 774271552
 5               Relay_Log_Space: 1266940262
 6               Until_Condition: None
 7                Until_Log_File: 
 8                 Until_Log_Pos: 0
 9            Master_SSL_Allowed: No
10            Master_SSL_CA_File: 
11            Master_SSL_CA_Path: 
12               Master_SSL_Cert: 
13             Master_SSL_Cipher: 
14                Master_SSL_Key: 
15         Seconds_Behind_Master: NULL
16 Master_SSL_Verify_Server_Cert: No
17                 Last_IO_Errno: 0
18                 Last_IO_Error: 
19                Last_SQL_Errno: 1032
20                Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 3 failed executing transaction '1699
21 7e5e-5707-11e7-a702-94188201787c:161982297' at master log mysql-bin.000309, end_log_pos 774308000. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

查看 error.log:

vim error.log

2018-10-26T01:55:00.133136+08:00 16179 [ERROR] Slave SQL for channel 'master_6': Worker 3 failed executing transaction '16997e5e-5707-11e7-a702-94188201787c:161982297' at 
master log mysql-bin.000309, end_log_pos 774308000; Could not execute Update_rows event on table test.test_log; Can't find record in 'test_log', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000309, end_log_pos 774308000, Error_code: 1032

方法一：跳过错误

1）先跳过这一条错误,让主从同步恢复正常。（或者N条event，一条一条跳过）

　　set global sql_slave_skip_counter=1;

　　start slave sql_thread for channel 'master_6';

　　slave 起来之后，恢复数据，在从库上重新insert 进这条

2）The most recent failure being: Worker 3 failed executing transaction '169921 7e5e-5707-11e7-a702-94188201787c:161982297'

　　GTID 模式下还可以这样：

　　set gtid_next='169921 7e5e-5707-11e7-a702-94188201787c:161982297';

　　begin;

　　commit;

　　set gtid_next='automatic';

　　stop slave sql_thread for channel 'master_6';

　　start slave sql_thread for channel 'master_6';

方法二：还原被删除的数据

根据错误提示通过mysqlbinglog找到这条数据的sql，此事件在主服务器Master binlog中的位置是mysql-bin.000309, end_log_pos77430800

 1 [root@xxxxx binlog]# mysqlbinlog  --no-defaults --stop-position=774308000 mysql-bin.000309 -vv |grep -A 20 774308000
 2 #181026  1:55:00 server id 3663306  end_log_pos 774308000 CRC32 0x2969563a     Update_rows: table id 52745 flags: STMT_END_F
 3 
 4 BINLOG '
 5 dAPSWxPK5TcAWgAAAIr+Ji4AAAnOAAAAAAEAB21vbml0b3IAEWpvYl9leGVjdXRpb25fbG9nAAsP
 6 Dw8PDwMPDwMRERCgAJAB/AP8A8gAUACAPgAAgAbDz1ww
 7 dAPSWx/K5TcAFgIAAKAAJy4AAAnOAAAAAAEAAgAL/////4D8JDZlOGJjNGZlLTlmYzktNDllYS1h
 8 NjliLWE4YmNjMWZjOGJlZiQAb21zVXBkYXRlV2FyZWhvdXNlT3JkZXJUYXNrU3RhdHVzSm9iZgBv
 9 bXNVcGRhdGVXYXJlaG91c2VPcmRlclRhc2tTdGF0dXNKb2JALUAwQC1AUkVBRFlALUAxMC4xNTcu
10 MzIuMzlALUA2NTYyYzQ5YS0zMjQyLTQyNWQtODdhZC1lZTA4N2M0NmYzN2UXAGRwcHZkb2NzZXIx
11 OC5zZXBob3JhLmNuDDEwLjE1Ny4zMi4zOQAAAAAOTk9STUFMX1RSSUdHRVIAAAAAW9IDdID4JDZl
12 OGJjNGZlLTlmYzktNDllYS1hNjliLWE4YmNjMWZjOGJlZiQAb21zVXBkYXRlV2FyZWhvdXNlT3Jk
13 ZXJUYXNrU3RhdHVzSm9iZgBvbXNVcGRhdGVXYXJlaG91c2VPcmRlclRhc2tTdGF0dXNKb2JALUAw
14 QC1AUkVBRFlALUAxMC4xNTcuMzIuMzlALUA2NTYyYzQ5YS0zMjQyLTQyNWQtODdhZC1lZTA4N2M0
15 NmYzN2UXAGRwcHZkb2NzZXIxOC5zZXBob3JhLmNuDDEwLjE1Ny4zMi4zOQAAAAAOTk9STUFMX1RS
16 SUdHRVIBAAAAW9IDdFvSA3Q6Vmkp
17 '/*!*/;
18 ### UPDATE `test`.`test_log`
19 ### WHERE
20 ###   @1='6e8bc4fe-9fc9-49ea-a69b-a8bcc1fc8bef' /* VARSTRING(160) meta=160 nullable=0 is_null=0 */
21 ###   @2='omsUpdateWarehouseOrderTaskStatusJob' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
22 ###   @3='omsUpdateWarehouseOrderTaskStatusJob@-@0@-@READY@-@1@-@6562c49a-3242-425d-87ad-ee087c46f37e' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */

事务的语句为： UPDATE `test`.`test_log` where @1=‘’，@2=‘’ （@1为第一个字段，@2为第二个字段）

要是从库少了这个数据，把上面binglog中的update 改为insert

不要记录binlog：

set sql_log_bin=0;

insert into `test`.`test_log` values() ;

set sql_log_bin=1;

start slave sql_thread for channel 'master_6';