主页 / dba / 问题 / 335132
Accepted
Andrei Hristov
Asked: 2024-01-24 16:38:14 +0800 CST

MySQL 复制状态不报告 mysqld.cnf 参数

  • 772

我正在运行从 3 个主服务器到 1 个从服务器的多主复制。每个主服务器都有 2 个数据库，但我只复制其中 1 个数据库。

服务器版本：8.0.35-0ubuntu0.20.04.1（Ubuntu）

复制工作正常，但我的问题是，为什么当我运行

show replica status \G

我在状态报告中没有看到为每个通道的Replicate_Do_DB或replicate-wild-do-table设置的参数。如果我将它们设置为全局变量（不指定通道），它们会出现在报告中，但在每个通道设置时不会出现，知道为什么吗？

这是从 MySQL 配置文件 ( /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.%

这是“显示复制状态\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)
replication

1 个回答

  1. Best Answer

    如果有人遇到过这样的问题，我已经发现了这些问题，很容易在错误日志中看到，这是因为我在配置文件中的数据库名称周围有这些“”。

    而不是这样replicate-wild-do-table='src3_s#####':s#####v.%的replicate-wild-do-table=src3_s#####:s#####v.%

    • 0

相关问题