0

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 17.2.5.4, “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.

Please help me understand what I am missing.enter image description here

6
  • I think I got I am using replicate-wild-do-table= instead I ought to use replicate-wild-do-table: — am I correct, I will try this on Monday though. Oct 14 at 15:59
  • That is a very non-standard replication topology. bkp receives the same data from master1 via two routes. Do you prevent this duplication? Are you using GTIDs?
    – Rick James
    Oct 14 at 16:47
  • "but that is not the requirement" -- Please justify the "requirement", else we will spend our time explaining why it is s.....
    – Rick James
    Oct 14 at 16:50
  • Are there any other replicate_... or binlog_... lines in the configuration on any of the 3 servers?
    – Rick James
    Oct 14 at 16:51
  • Thanks for response Mr James. I am sorry I didn’t mention all the details. The setup is bigger than what I showed in the image actually. I personally don’t like this setup, but, once the migration of the 3 schema from master1 to abz_master is done, the three schema will be dropped from master1 and some applications will connect to abz_master instead of master1. Oct 15 at 11:24
0

The correct syntax for channel replication is

replicate-wild-do-table=channel_name:db_name.%

I was able to use the replicate-wild-do-table options in channel replication.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.