Mysql – Replicate_Do_DB and Replicate_Wild_Do_Table – Replication does not work

MySQLmysql-5.7replication

I have been tasked to set replication up on a slave host. The master database is a "data store" where tables are dropped, recreated, and reloaded on a daily basis.

My initial setup of the slave host worked fine by replication was always days behind the master. After talking to the users of the "data store", I realized that not all of the databases and not all of the tables need to be replicated over. So this is what I did

CI-DB002-PRD [root@localhost] ON (none)>  STOP SLAVE\G

CI-DB002-PRD [root@localhost] ON (none)> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (bidw,cf2_fact,ct_fact,ez_fact,gt_fact,sfdc,soa_fact,tesla_fact,tmc_fact);

CI-DB002-PRD [root@localhost] ON (none)> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('bidw.domo%', 'bidw.consolidated%', 'bidw.cf2%', 'bidw.tesla%');

CI-DB002-PRD [root@localhost] ON (none)>  START SLAVE\G

Very quickly, replication caught up and now, the slave is 3-7 seconds behind the master.

But replication is not touching the databases I want to be replicated. Even though data changes on master every 10 minutes for the schemas I list above. I did several validation queries and results now differ. When I check the status of my slave, nothing stands out

CI-DB002-PRD [root@localhost] ON (none)> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: 10.239.0.34
                  Master_User: ci02replicadb
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binary-log.009871
          Read_Master_Log_Pos: 14678596
               Relay_Log_File: ci-db002-prd-relay-bin.007914
                Relay_Log_Pos: 814824
        Relay_Master_Log_File: binary-log.009871
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: bidw,cf2_fact,ct_fact,ez_fact,gt_fact,sfdc,soa_fact,tesla_fact,tmc_fact,staging,phoenix,data_science
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table: bidw.domo%,bidw.consolidated%,bidw.cf2%,bidw.tesla%,bidw.ez%,bidw.ct%,bidw.gt%,bidw.tmc%,bidw.did%,bidw.Shortened%,bidw.other_revenue%,bidw.revenue%,bidw.advanced_cohort%
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 814626
              Relay_Log_Space: 14679056
              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: 22
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: a485ab14-aa57-11ea-bef5-42010aef0022
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.01 sec)

For "Replicate_Wild_Do_Table", I replicate only the tables listed there because the entire "bidw" database is lousy with junk tables.

What am I missing here? Am I not allowed to use both filters at the same time? I am on version 5.7.31.

Best Answer

Here is an old blog from my company : Best Practice For Setting Up MySQL Replication Filters.

It says in part:

MySQL provides 3 levels of filters for setting up replication: Binary log, DB and Table. The binlog filters apply on the master to control how to log the changes. Since MySQL replication is based on the binlog, it is the first level filter and has the highest priority. While the DB-level and Table-level filters apply on the slaves, since each table belongs to a schema, the DB-level filters have higher priority than the Table-level ones. Inside the Table-level filters, MySQL will evaluate the options in the order of: –replicate-do-table, –replicate-ignore-table , –replicate-wild-do-table , –replicate-wild-ignore-table.

Given this statement and looking at your SHOW SLAVE STATUS\G, I would do one of the following:

  1. Remove bidw from the Replicate_Do_DB list (at the very least)
  2. Remove Replicate_Wild_Do_Table and migrate away all junk tables
  3. Remove Replicate_Do_DB list altogether (last resort)

UPDATE 2020-08-12 10:54 EDT

To further clarify, Replicate_Do_DB is allowing everything and its grandmother from the midw database to replicate and the Replicate_Wild_Do_Table is actually ignore as a result.

Related Question