I am trying to configure a replication topology where there will be one slave which will connect to two masters. Percona Server 8.0.25.
Master1- main master
bkp_server - slave to master1 and abz_master
abz_master - slave to master1
Master1 has 10 schema (A to J)out of these 10, seven (A to G) schema are to be replicated to bkp_server and the remaining three schema (H,I,J) are to be replicated to abz_master. From abz_master, then these three schema (H I J) will be replicated to bkp_server.
I have attached an image to show the setup. I have to add the image to make it clear what I am trying to achieve. For some reason, I have to use such a topology. I know I can directly replicate all the schema from master1 to the bkp_server but that is not the requirement and my main question is about the failure of replicate-wild-do-table filters.
Replication From master1 to the bkp_server - CHANGE MASTER TO is as follows bkp_server> CHANGE MASTER TO master_host='master1', master_user='replica', master_password='Pass@123', master_log_file='master1_binlog.000004', master_log_pos=2692 for channel "master1"; Replication From abz_master to the bkp_server - CHANGE MASTER TO is as follows bkp_server> CHANGE MASTER TO master_host='abz_master', master_user='replica', master_password='Pass@123', master_log_file='abz_master_binlog.000005', master_log_pos=6684 for channel "abz_master";
On the slave bkp_server I am using following replication filters
replicate-wild-do-table=master1:A.% replicate-wild-do-table=master1:B.% replicate-wild-do-table=master1:C.% replicate-wild-do-table=master1:D.% replicate-wild-do-table=master1:E.% replicate-wild-do-table=master1:F.% replicate-wild-do-table=master1:G.% replicate-wild-do-table=abz_master:H.% replicate-wild-do-table=abz_master:I.% replicate-wild-do-table=abz_master:J.%
but these filters replicate-wild-do-table seem to do nothing. I have gone through this https://dev.mysql.com/doc/refman/8.0/en/replication-rules-channel-based-filters.html where it mentions the following about replicate-wild-do-table --replicate-wild-do-table=channel:table regexid.
It doesn't provide the options like --replicate-wild-do-table=channel:db_name.table% but if I read the description for --replicate-wild-do-table on the link https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html#option_mysqld_replicate-wild-do-table
it certainly provides for that syntax.
This option supports channel specific replication filters, enabling multi-source replicas to use specific filters for different sources. To configure a channel specific replication filter on a channel named channel_1 use --replicate-wild-do-table:channel_1:db_name.tbl_name. In this case, the first colon is interpreted as a separator and subsequent colons are literal colons. See Section 22.214.171.124, “Replication Channel Based Filters” for more information.
Could you help me understand what I am missing. I am able to start replication using replicate-ignore-db and replicate-do-db. The slave shows channel based replication filters for replicate-ignore-db and replicate-do-db but it doesn't show correct replication filters for replicate-wild-do-table.
For --replicate-ignore-db it shows only the names of the schema to be ignored in the SHOW SLAVE STATUS output, but for --replicate-wild-do-table, it shows the entire rule mentioned in the config file like master1:A.%,master1:B.%,master1:C% and so on.