GreatSQL一个关于主从复制的限制描述与规避

一、背景

分享一个在项目运维中遇到的一个主从复制限制的一个坑，项目的架构为主集群+灾备集群，每个集群为一主两从模式。主集群到灾备集群的同步为主从复制的方式，根据业务需求灾备集群需要忽略系统库跟某些配置表，所以才会触发此限制，而这个限制如果我们之前没有遇到过，那么排查起来也是相对不易的。

二、限制描述

1、主从同步出现报错

greatsql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.xxx.xxx Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: greatsql-bin.000990 Read_Master_Log_Pos: 92274290 Relay_Log_File: greatsql-relay.002963 ----- Relay_Log_Pos: 701548899 Relay_Master_Log_File: greatsql-bin.000988 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: mysql,dbscale,dbscale_tmp,information_schema,performance_schema,sys Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: A.ab,B.bc Last_Errno: 1146 Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '9e668a93-2618-11ee-93ee-bc16954181bb:47508257' at master log greatdb-bin.000988, end_log_pos 701570116. 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 Exec_Master_Log_Pos: 701548690 Relay_Log_Space: 2246320360 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1146 Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '9e668a93-2618-11ee-93ee-bc16954181bb:47508257' at master log greatdb-bin.000988, end_log_pos 701570116. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Replicate_Ignore_Server_Ids: Master_Server_Id: 1943306 Master_UUID: 9e668a93-2618-11ee-93ee-bc16954181bb Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 230822 14:14:18 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 9e668a93-2618-11ee-93ee-bc16954181bb:2-47565802 Executed_Gtid_Set: 30873cfe-8750-11ed-b56f-744aa4073024:1-270, 9e668a93-2618-11ee-93ee-bc16954181bb:1-47508256 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)