0

I am running a multi master replication from 3 master servers to 1 slave. Each of the masters has 2 DBs but I am replicating just 1 of the DBs.

Server version: 8.0.35-0ubuntu0.20.04.1 (Ubuntu)

Replication is working fine but my question is, why when i run the

show replica status \G

I do not see the parameters i have set for Replicate_Do_DB or replicate-wild-do-table per channel in the status report. If I set them as globals (not specifying a channel) they appear in the report, but not when set per channel, any idea why is that?

This is extract from the MySQL config file (/etc/mysql/mysql.conf.d/mysqld.cnf) :

master-info-repository=table
relay-log-info-repository=table

replicate-wild-do-table='src3_s#####':s#####v.%
replicate-wild-do-table='src2_b#####':b#####v.%
replicate-wild-do-table='src1_d#####':d#####v.%

and this it the result from the 'show replication status \G':

mysql> show replica status \G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 101.114.110.113
                  Source_User: slave_user
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql###-bin.001307
          Read_Source_Log_Pos: 85496
               Relay_Log_File: plfamily-replication-slave-relay-bin-src1_#####.000086
                Relay_Log_Pos: 323
        Relay_Source_Log_File: mysql###-bin.001307
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: 
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 85496
              Relay_Log_Space: 86304
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 1
                  Source_UUID: a604d2af-ed30-11ea-a99d-8657a156e939
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name: src1_d######
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
*************************** 2. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 237.239.251.262
                  Source_User: slave_user
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql###-bin.000002
          Read_Source_Log_Pos: 45872
               Relay_Log_File: plfamily-replication-slave-relay-bin-src2_#####.000029
                Relay_Log_Pos: 326
        Relay_Source_Log_File: mysql###-bin.000002
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: 
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 45872
              Relay_Log_Space: 921
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 3
                  Source_UUID: ab801adf-ddb3-11eb-96b2-a27c967973a1
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name: src2_b#####
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
*************************** 3. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 134.148.228.213
                  Source_User: slave_user
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql####-bin.000002
          Read_Source_Log_Pos: 47931
               Relay_Log_File: plfamily-replication-slave-relay-bin-src3_s####.000025
                Relay_Log_Pos: 328
        Relay_Source_Log_File: mysql####-bin.000002
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: 
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 47931
              Relay_Log_Space: 925
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 4
                  Source_UUID: ab801adf-ddb3-11eb-96b2-a27c967973a1
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name: src3_s######
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
3 rows in set (0.00 sec)


1 Answer 1

0

If anyone ever crosses such an issue, i have found the issues, it was ease to see in the error log and it was because i had these ' ' surrounding my DB names in the config file.

Insteand of this replicate-wild-do-table='src3_s#####':s#####v.% have it like this replicate-wild-do-table=src3_s#####:s#####v.%

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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