基于GTID
1032（从库缺少数据）
Slave端查看slave状态
mysql> show slave status\G
*************************** 1. row ***************************
省略...
Slave_IO_Running: Yes
Slave_SQL_Running: No
省略...
Last_Errno: 1032
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 2 failed executing transaction 'bebd7b80-9904-11ec-99e9-525400adb547:116979496' at master log mysql-bin.002957, end_log_pos 884410851. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
省略...
Last_SQL_Errno: 1032
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 2 failed executing transaction 'bebd7b80-9904-11ec-99e9-525400adb547:116979496' at master log mysql-bin.002957, end_log_pos 884410851. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
省略...
根据上面报错中的提示查看performance_schema.replication_applier_status_by_worker表，可以看到在Slave端pantao.nic表不能执行DELETE操作，说明pantao.nic表中这部分数据不存在
mysql> select * from performance_schema.replication_applier_status_by_worker\G
*************************** 1. row ***************************
省略...
LAST_ERROR_NUMBER: 1032
LAST_ERROR_MESSAGE: Worker 2 failed executing transaction 'bebd7b80-9904-11ec-99e9-525400adb547:116979496' at master log mysql-bin.002957, end_log_pos 884410851; Could not execute Delete_rows event on table pantao.nic; Can't find record in 'nic', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.002957, end_log_pos 884410851
LAST_ERROR_TIMESTAMP: 2022-07-29 00:12:54.220544
省略...
分析Master端binlog确认出问题的DELETE事务
[mysql]# mysqlbinlog --base64-output=decode-rows -vvvv mysql-bin.002957 --stop-position 884410851 | tail -500
# at 884409620
#220728 18:58:53 server id 118 end_log_pos 884410851 CRC32 0xf7fa5f4c Delete_rows: table id 203 flags: STMT_END_F
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936818 /* INT meta=0 nullable=0 is_null=0 */
### @2='476f37a7-2e6a-43a9-be10-7b8f3b2e1c8a' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='GigabitEthernet5/0/0' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='WAN1' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5='internet' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=1 /* INT meta=0 nullable=1 is_null=0 */
### @9='10550a1b-facd-477c-882f-9ed1737fcb24' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10='10.10.251.1/24' /* VARSTRING(75) meta=75 nullable=1 is_null=0 */
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936822 /* INT meta=0 nullable=0 is_null=0 */
### @2='aac687ce-1ad0-4c27-a73e-191865a57d21' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='GigabitEthernet6/0/0' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='WAN2' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5='internet' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=0 /* INT meta=0 nullable=1 is_null=0 */
### @9='10550a1b-facd-477c-882f-9ed1737fcb24' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936827 /* INT meta=0 nullable=0 is_null=0 */
### @2='beb29175-2fce-4868-a9a9-ad8c96399ba1' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='GigabitEthernet3/0/0' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='LAN' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=1 /* INT meta=0 nullable=1 is_null=0 */
### @9='10550a1b-facd-477c-882f-9ed1737fcb24' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936832 /* INT meta=0 nullable=0 is_null=0 */
### @2='0d43add3-dbe7-4f96-808b-0f8820f1f07c' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='GigabitEthernet4/0/0' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='LAN' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=0 /* INT meta=0 nullable=1 is_null=0 */
### @9='10550a1b-facd-477c-882f-9ed1737fcb24' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936837 /* INT meta=0 nullable=0 is_null=0 */
### @2='98e3c08d-263c-4ad4-a52f-27de277090f3' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='loop0' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='LTE' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5='4G' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=0 /* INT meta=0 nullable=1 is_null=0 */
### @9='10550a1b-facd-477c-882f-9ed1737fcb24' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936838 /* INT meta=0 nullable=0 is_null=0 */
### @2='6b769a71-ffe7-47bc-b321-dbc22f14b04a' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='ipsec4968' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='BACKUP-IPSEC-LTE' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=0 /* INT meta=0 nullable=1 is_null=0 */
### @9='10550a1b-facd-477c-882f-9ed1737fcb24' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936840 /* INT meta=0 nullable=0 is_null=0 */
### @2='a5b45ce8-e512-4506-ad9a-b930063d38e0' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='ipsec42' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='IPSEC-LTE' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=0 /* INT meta=0 nullable=1 is_null=0 */
### @9='10550a1b-facd-477c-882f-9ed1737fcb24' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936841 /* INT meta=0 nullable=0 is_null=0 */
### @2='4018faa4-1da5-4443-b601-886a012b2f35' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='ipsec4969' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='BACKUP-IPSEC2' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=0 /* INT meta=0 nullable=1 is_null=0 */
### @9='10550a1b-facd-477c-882f-9ed1737fcb24' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936843 /* INT meta=0 nullable=0 is_null=0 */
### @2='bd2c231f-9c8f-4e84-9025-576c98a26b85' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='ipsec41' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='IPSEC2' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=0 /* INT meta=0 nullable=1 is_null=0 */
### @9='10550a1b-facd-477c-882f-9ed1737fcb24' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936844 /* INT meta=0 nullable=0 is_null=0 */
### @2='4f0d538b-4a75-4d3b-be15-ba436838c08f' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='ipsec4970' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='BACKUP-IPSEC1' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=1 /* INT meta=0 nullable=1 is_null=0 */
### @9='10550a1b-facd-477c-882f-9ed1737fcb24' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936845 /* INT meta=0 nullable=0 is_null=0 */
### @2='e619a7e2-8371-42fb-83cc-64dd25ecd350' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='ipsec40' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='IPSEC1' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=1 /* INT meta=0 nullable=1 is_null=0 */
### @9='10550a1b-facd-477c-882f-9ed1737fcb24' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
ROLLBACK /* added by mysqlbinlog */ /*!*/;
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*/;
确认好Slave端确实不存在DELETE删除的这部分数据，Slave跳过这部分事务
mysql> stop slave;
mysql> set sql_log_bin = off;
mysql> set gtid_next='bebd7b80-9904-11ec-99e9-525400adb547:116979496';
mysql> begin;commit;
mysql> set gtid_next='automatic';
mysql> set sql_log_bin = on;
mysql> start slave;330（Slave端不允许写入）
Slave端查看slave状态
mysql>show slave status\G
*************************** 1. row ***************************
省略...
Slave_IO_Running: Yes
Slave_SQL_Running: No
省略...
Last_Errno: 3030
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 2 failed executing transaction 'bebd7b80-9904-11ec-99e9-525400adb547:116979494' at master log mysql-bin.002957, end_log_pos 884404523. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
省略...
Last_SQL_Errno: 3030
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 2 failed executing transaction 'bebd7b80-9904-11ec-99e9-525400adb547:116979494' at master log mysql-bin.002957, end_log_pos 884404523. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
省略...
根据报错提示查看performance_schema.replication_applier_status_by_worker，包含1032（Slave端缺少数据）错误和330（Slave端写入错误）错误
mysql>select * from performance_schema.replication_applier_status_by_worker \G
*************************** 1. row ***************************
CHANNEL_NAME:
WORKER_ID: 1
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_ERROR_NUMBER: 1032
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'bebd7b80-9904-11ec-99e9-525400adb547:116979493' at master log mysql-bin.002957, end_log_pos 884404088; Could not execute Delete_rows event on table pantao.nic; Can't find record in 'nic', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.002957, end_log_pos 884404088
省略...
*************************** 2. row ***************************
CHANNEL_NAME:
WORKER_ID: 2
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_ERROR_NUMBER: 3030
LAST_ERROR_MESSAGE: Worker 2 failed executing transaction 'bebd7b80-9904-11ec-99e9-525400adb547:116979494' at master log mysql-bin.002957, end_log_pos 884404523; Could not execute Write_rows event on table pantao.nic; Slave worker has stopped after at least one previous worker encountered an error when replica-preserve-commit-order was enabled. To preserve commit order, the last transaction executed by this thread has not been committed. When restarting the slave after fixing any failed threads, you should fix this worker as well., Error_code: 3030; the event's master log FIRST, end_log_pos 884404523
省略...
1032报错：pantao.nic表执行DELETE，经查看备库中不存在此部分数据，此报错可以跳过
[mysql]# mysqlbinlog --base64-output=decode-rows -vvvv mysql-bin.002957 --stop-position 884404088 | tail -500
# at 884402855
#220728 18:58:53 server id 118 end_log_pos 884404088 CRC32 0x34b7081a Delete_rows: table id 203 flags: STMT_END_F
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936801 /* INT meta=0 nullable=0 is_null=0 */
### @2='85bb9751-0250-4ea9-a9d7-04f1607451d7' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='ipsec213' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='IPSEC1' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=1 /* INT meta=0 nullable=1 is_null=0 */
### @9='1ab48fe1-6922-4f8a-bafd-6fa7f49e2f99' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936804 /* INT meta=0 nullable=0 is_null=0 */
### @2='9518adf6-7f8d-4db0-b6b4-612e229ee2bf' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='ipsec4797' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='BACKUP-IPSEC1' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=1 /* INT meta=0 nullable=1 is_null=0 */
### @9='1ab48fe1-6922-4f8a-bafd-6fa7f49e2f99' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936808 /* INT meta=0 nullable=0 is_null=0 */
### @2='daf668ec-9506-4fd8-a185-eb4a616a1c76' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='ipsec214' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='IPSEC2' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=0 /* INT meta=0 nullable=1 is_null=0 */
### @9='1ab48fe1-6922-4f8a-bafd-6fa7f49e2f99' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936810 /* INT meta=0 nullable=0 is_null=0 */
### @2='ca77d1b9-e99e-4614-9d66-eb262b439df7' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='ipsec4796' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='BACKUP-IPSEC2' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=0 /* INT meta=0 nullable=1 is_null=0 */
### @9='1ab48fe1-6922-4f8a-bafd-6fa7f49e2f99' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936814 /* INT meta=0 nullable=0 is_null=0 */
### @2='ea41569a-c320-4fcb-ad96-bfc9b9ced42d' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='ipsec215' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='IPSEC-LTE' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=0 /* INT meta=0 nullable=1 is_null=0 */
### @9='1ab48fe1-6922-4f8a-bafd-6fa7f49e2f99' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936819 /* INT meta=0 nullable=0 is_null=0 */
### @2='ff25b4bb-44e1-4291-b52d-ea7c8ccf45f7' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='ipsec4795' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='BACKUP-IPSEC-LTE' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=0 /* INT meta=0 nullable=1 is_null=0 */
### @9='1ab48fe1-6922-4f8a-bafd-6fa7f49e2f99' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936824 /* INT meta=0 nullable=0 is_null=0 */
### @2='134b6470-8a6f-4e2d-b22e-ab1d62d03cb0' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='loop0' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='LTE' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5='5G' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=0 /* INT meta=0 nullable=1 is_null=0 */
### @9='1ab48fe1-6922-4f8a-bafd-6fa7f49e2f99' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936828 /* INT meta=0 nullable=0 is_null=0 */
### @2='aa3df76a-4c82-473d-af27-0fea8b7f7b0b' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='GigabitEthernet4/0/0' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='LAN' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=0 /* INT meta=0 nullable=1 is_null=0 */
### @9='1ab48fe1-6922-4f8a-bafd-6fa7f49e2f99' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936833 /* INT meta=0 nullable=0 is_null=0 */
### @2='e4c7c469-f48a-4271-8bf7-e327072df2c6' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='GigabitEthernet3/0/0' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='LAN' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=1 /* INT meta=0 nullable=1 is_null=0 */
### @9='1ab48fe1-6922-4f8a-bafd-6fa7f49e2f99' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936835 /* INT meta=0 nullable=0 is_null=0 */
### @2='31685b96-3647-46dd-b1dc-f0057e52480c' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='GigabitEthernet6/0/0' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='WAN2' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5='internet' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=0 /* INT meta=0 nullable=1 is_null=0 */
### @9='1ab48fe1-6922-4f8a-bafd-6fa7f49e2f99' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
### DELETE FROM `pantao`.`nic`
### WHERE
### @1=512936836 /* INT meta=0 nullable=0 is_null=0 */
### @2='243efd8e-9886-40da-8200-22b2178ce30f' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='GigabitEthernet5/0/0' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='WAN1' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5='internet' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=1 /* INT meta=0 nullable=1 is_null=0 */
### @9='1ab48fe1-6922-4f8a-bafd-6fa7f49e2f99' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10='172.16.5.2/27' /* VARSTRING(75) meta=75 nullable=1 is_null=0 */
ROLLBACK /* added by mysqlbinlog */ /*!*/;
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*/;mysql> stop slave;
mysql> set sql_log_bin = off;
mysql> set gtid_next='bebd7b80-9904-11ec-99e9-525400adb547:116979494';
mysql> begin;commit;
mysql> set gtid_next='automatic';
mysql> set sql_log_bin = on;
mysql> start slave;
330报错：因其他原因导致往pantao.nic表中插入1条数据失败，经查看主、备库中均不存在这条数据，可以跳过此报错
根据报错翻译为：无法在pantao.nic表上执行Write_rows事务;当启用replica-preserve-commit-order时，至少前面有一个worker遇到错误，Slave worker已经停止。为了保持提交顺序，这个线程执行的最后一个事务还没有提交。修复任何失败的线程后重新启动slave时，你也应该修复这个worker
[mysql]# mysqlbinlog --base64-output=decode-rows -vvvv mysql-bin.002957 --stop-position 884404523 | tail -200
# at 884404275
#220728 18:58:53 server id 118 end_log_pos 884404353 CRC32 0x6bc706cf Table_map: `pantao`.`nic` mapped to number 203
# at 884404353
#220728 18:58:53 server id 118 end_log_pos 884404523 CRC32 0x10e593fd Write_rows: table id 203 flags: STMT_END_F
### INSERT INTO `pantao`.`nic`
### SET
### @1=512937228 /* INT meta=0 nullable=0 is_null=0 */
### @2='7e138ba7-e79b-4746-900b-737ff6b4227b' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='GigabitEthernet5/0/0' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='WAN1' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5='internet' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=1 /* INT meta=0 nullable=1 is_null=0 */
### @9='10550a1b-facd-477c-882f-9ed1737fcb24' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10='10.10.251.1/24' /* VARSTRING(75) meta=75 nullable=1 is_null=0 */
ROLLBACK /* added by mysqlbinlog */ /*!*/;
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*/;mysql> stop slave;
mysql> set sql_log_bin = off;
mysql> set gtid_next='bebd7b80-9904-11ec-99e9-525400adb547:116979494';
mysql> begin;commit;
mysql> set gtid_next='automatic';
mysql> set sql_log_bin = on;
mysql> start slave;1062（主键冲突）
Slave端查看slave状态
mysql>show slave status\G
*************************** 1. row ***************************
省略...
Slave_IO_Running: Yes
Slave_SQL_Running: No
省略...
Last_Errno: 1062
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 2 failed executing transaction 'aa66e253-46f1-11ec-9841-5254009c0c42:329839232' at master log mysql-bin.002997, end_log_pos 277432829. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
省略...
Last_SQL_Errno: 1062
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 2 failed executing transaction 'aa66e253-46f1-11ec-9841-5254009c0c42:329839232' at master log mysql-bin.002997, end_log_pos 277432829. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
省略...
根据报错提示查看performance_schema.replication_applier_status_by_worker，提示无法在pantao.nic表上执行Write_rows操作，nic表中有重复的主键值'517602648'
mysql>select * from performance_schema.replication_applier_status_by_worker \G
CHANNEL_NAME:
WORKER_ID: 2
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_ERROR_NUMBER: 1062
LAST_ERROR_MESSAGE: Worker 2 failed executing transaction 'aa66e253-46f1-11ec-9841-5254009c0c42:329839232' at master log mysql-bin.002997, end_log_pos 277432829; Could not execute Write_rows event on table pantao.nic; Duplicate entry '517602648' for key 'nic.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.002997, end_log_pos 277432829
...
查看binlog
[mysql]# mysqlbinlog --base64-output=decode-rows -vvvv mysql-bin.002997 --stop-position 277432829 | tail -300
# at 277432693
#220731 2:42:52 server id 117 end_log_pos 277432829 CRC32 0x5d8de321 Write_rows: table id 2758 flags: STMT_END_F
### INSERT INTO `pantao`.`nic`
### SET
### @1=517602648 /* INT meta=0 nullable=0 is_null=0 */
### @2='3412ad66-97a7-4920-818a-40ab413e2273' /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @3='ipsec277' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @4='IPSEC1' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @5=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @6=NULL /* VARSTRING(51) meta=51 nullable=1 is_null=1 */
### @7=NULL /* INT meta=0 nullable=1 is_null=1 */
### @8=0 /* INT meta=0 nullable=1 is_null=0 */
### @9='71a7f788-627e-4b87-885b-9b86f758cde3' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### @10=NULL /* VARSTRING(75) meta=75 nullable=1 is_null=1 */
ROLLBACK /* added by mysqlbinlog */ /*!*/;
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*/;Slave查看存在主键相同的数据
mysql>select * from nic where id='517602648';
+-----------+--------------------------------------+----------+--------+--------------+------+------+--------+--------------------------------------+---------+
| id | uuid | name | type | network_type | mac | mtu | status | nes_uuid | address |
+-----------+--------------------------------------+----------+--------+--------------+------+------+--------+--------------------------------------+---------+
| 517602648 | 559c6c7e-2f75-458a-994f-afaa7bc054e8 | ipsec216 | IPSEC1 | NULL | NULL | NULL | 1 | 8d969411-af7f-44a6-8be6-73e3850006e9 | NULL |
+-----------+--------------------------------------+----------+--------+--------------+------+------+--------+--------------------------------------+---------+Slave端删除该数据
mysql> stop slave;
mysql> set sql_log_bin = off;
mysql> delete from nic where id='517602648';
mysql> set sql_log_bin = on;
mysql> start slave;
