从库跳过错误的gtid

从库同步出错

                   [root@localhost][test] show slave status\G
*************************** 1. row ***************************
                                        ...
                   Last_Errno: 1062
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '022fd4af-e35e-11e9-aa13-000c292559a8:133365' at master log mysql-bin.000019, end_log_pos 12551. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                   ....

根据错误代码，查看具体的错误

[root@mysql2 ~]# perror 1062
MySQL error code 1062 (ER_DUP_ENTRY): Duplicate entry '%-.192s' for key %d

主键唯一键冲突导致同步失败

根据错误信息，找到错误的日志以及相应的gtid

解析日志：
[root@mysql1 binlog]# mysqlbinlog --base64-output=decode-rows -vv /mysql/binlog/mysql-bin.000019 > 19.sql

找到具体的gtid
SET @@SESSION.GTID_NEXT= '022fd4af-e35e-11e9-aa13-000c292559a8:133365'/*!*/;
# at 12321
#210713 15:36:26 server id 24750  end_log_pos 12393 CRC32 0x1d05efde    Query   thread_id=11960 exec_time=0     error_code=0
SET TIMESTAMP=1626161786/*!*/;
BEGIN
/*!*/;
# at 12393
#210713 15:36:26 server id 24750  end_log_pos 12452 CRC32 0x5a3d922d    Rows_query
# insert into t values(null,33,5,100)
# at 12452
#210713 15:36:26 server id 24750  end_log_pos 12499 CRC32 0x8cfa884e    Table_map: `test`.`t` mapped to number 975
# at 12499
#210713 15:36:26 server id 24750  end_log_pos 12551 CRC32 0xe17e7abc    Write_rows: table id 975 flags: STMT_END_F
### INSERT INTO `test`.`t`
### SET
###   @1=11 /* INT meta=0 nullable=0 is_null=0 */
###   @2=33 /* INT meta=0 nullable=1 is_null=0 */
###   @3=5 /* INT meta=0 nullable=1 is_null=0 */
###   @4=100 /* INT meta=0 nullable=1 is_null=0 */
# at 12551
#210713 15:36:26 server id 24750  end_log_pos 12582 CRC32 0x1cfc996b    Xid = 186035
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

可以看到错误的语句和相应的值

# insert into t values(null,33,5,100)

### INSERT INTO `test`.`t`
### SET
###   @1=11 /* INT meta=0 nullable=0 is_null=0 */
###   @2=33 /* INT meta=0 nullable=1 is_null=0 */
###   @3=5 /* INT meta=0 nullable=1 is_null=0 */
###   @4=100 /* INT meta=0 nullable=1 is_null=0 */

从库查看相应的数据，由于id2唯一键冲突，所以报错

[root@localhost][(none)] select * from test.t;
+----+------+------+------+
| id | id2  | a    | b    |
+----+------+------+------+
|  1 |   11 |    1 |    1 |
|  3 |   33 |    5 |  100 |
|  9 |   22 |    3 |   10 |
+----+------+------+------+

从库修改id的值，并通过提交一个空事务，跳过相应的gtid

[root@localhost][(none)] stop slave;
Query OK, 0 rows affected (0.09 sec)

[root@localhost][(none)] update test.t set id=11 where id2=33;
Query OK, 1 row affected (0.30 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[root@localhost][(none)] set gtid_next='022fd4af-e35e-11e9-aa13-000c292559a8:133365';
Query OK, 0 rows affected (0.07 sec)

[root@localhost][(none)] begin;
Query OK, 0 rows affected (0.00 sec)

[root@localhost][(none)] commit;
Query OK, 0 rows affected (0.00 sec)

恢复GTID的默认分配行为
[root@localhost][(none)] set gtid_next=automatic;
Query OK, 0 rows affected (0.00 sec)

[root@localhost][(none)] start slave;
Query OK, 0 rows affected (0.43 sec)

错误的gtid已经加到了从库gtid集合里
[root@localhost][(none)] show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000006
         Position: 51283948
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 022fd4af-e35e-11e9-aa13-000c292559a8:1-133365,
5601335e-e37e-11e9-abc7-000c2947ab9b:1-192
1 row in set (0.00 sec)