Command SHOW MASTER STATUS different in master and slave



  • I'm using Bitnami MariaDB. I've done fixing the replication and now both Slave_IO_Running and Slave_SQL_Running has value yes. This is the whole output:

    *************************** 1. row ***************************
                    Slave_IO_State: Waiting for master to send event
                       Master_Host: provisioner-peer
                       Master_User: repl
                       Master_Port: 3306
                     Connect_Retry: 10
                   Master_Log_File: mysql-bin.000025
               Read_Master_Log_Pos: 16161372
                    Relay_Log_File: mysql-relay-bin.000014
                     Relay_Log_Pos: 156283
             Relay_Master_Log_File: mysql-bin.000025
                  Slave_IO_Running: Yes
                 Slave_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: 7356
               Exec_Master_Log_Pos: 16161372
                   Relay_Log_Space: 187679
                   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: 0
     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: 89
                    Master_SSL_Crl: 
                Master_SSL_Crlpath: 
                        Using_Gtid: No
                       Gtid_IO_Pos: 
           Replicate_Do_Domain_Ids: 
       Replicate_Ignore_Domain_Ids: 
                     Parallel_Mode: optimistic
                         SQL_Delay: 0
               SQL_Remaining_Delay: NULL
           Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
                  Slave_DDL_Groups: 2
    Slave_Non_Transactional_Groups: 0
        Slave_Transactional_Groups: 12
    

    But when I run command SHOW MASTER STATUS on both master and slave, it has different output.

    on master:

    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000025 | 16817070 |              |                  |
    +------------------+----------+--------------+------------------+
    

    on slave:

    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000004 |      390 |              |                  |
    +------------------+----------+--------------+------------------+
    

    I search for this but ended found nothing. Is it okay? or there is something I need to fix?



  • There is a strong likelihood you forgot to configure https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_log_slave_updates on the slave. If you are using MySQL 8.0.26 and above, it would be https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_log_replica_updates .

    Please add this line to your my.cnf if you are using MySQL 8.0.26 and above

    [mysqld]
    log_replica_updates
    

    Please add this line to your my.cnf if you are using MySQL 8.0.25 and prior

    [mysqld]
    log_slave_updates
    

    Then, restart mysqld on the Slave and the binary logs on the Slave will start increasing.

    The binary logs events from the Master were executed on the Slave but were not recorded on the Slave's local binary logs. The data should be fine.

    NOTE : https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_log_slave_updates is deprecated but still works for now. Once you upgrade past MySQL 8.0.25, please start using https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_log_replica_updates .




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2