15

I have a Master-Slave replication setup and it looks like that it is running fine. Below is a result of SHOW SLAVE STATUS command:

show slave STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: *.*.*.*
                  Master_User: repliV1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 10726644
               Relay_Log_File: mysqld-relay-bin.000056
                Relay_Log_Pos: 231871
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: data1
          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_Master_Log_Pos: 10726644
              Relay_Log_Space: 232172
              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:

I would like to understand further about the Relay_Log_File, Relay_Log_Pos and Relay_Master_Log_File.

My questions are:

  • Is it true that the relay log file is one which is being read and stored locally for the replication to run ?

  • What about the relay_master_log_file then; how is it different from the Master_Log_File?

  • What are both of these values, viz., Read_Master_Log_Pos and Relay_Log_Pos ? Why are they showing up even though the replication is complete and in sync ?

  • Is it true that these files are in binary format, and hence I cannot view them ?

3 Answers 3

12

I want to understand further is the Relay_Log_File, Relay_Log_Pos and Relay_Master_Log_File.

From SHOW SLAVE STATUS\G, get two values

  • Relay_Log_File : Current relay log accepting new entries during replication
  • Relay_Log_Pos : Current position of the current relay log accepting new entries during replication
  • Relay_Master_Log_File : Relay log file containing the last successfully executed SQL statement on the Master that was executed on the Slave.
  • Master_Log_File : The log on the Master that corresponds to the current relay log file and current relay log position

The files are in binary so not able to view them right

Yes, you can view the relay logs. Since they have the same structure as a regular binary log, just run the mysqlbinlog program. For example to view the SQL within any of you relay logs, such as the last one, do this:

mysqlbinlog mysqld-relay-bin.000056 > /root/SQLForCurrentRelayLog.txt
less /root/SQLForCurrentRelayLog.txt
3
  • I am now clearer how about Exec_Master_Log_Pos (is this the executing master log position) and until_log_pos where to stop is it?
    – newbie14
    May 26, 2012 at 16:52
  • Minor mistake here. Relay_Master_Log_File is the name of the master binary log file containing the most recent event executed by the SQL thread, not the relay log file.
    – crishoj
    Sep 18, 2016 at 13:03
  • @RolandoMySQLDBA ayudame porfavor dba.stackexchange.com/questions/177277/… Jun 26, 2017 at 15:36
10

Relay_Master_Log_File is actually the name of the binlog on the master containing the last SQL statement successfully executed on the slave. It is not the name of a relay log on the slave. See: http://dev.mysql.com/doc/refman/5.5/en/show-slave-status.html

Exec_Master_Log_Pos is the position in the relay_master_log_file that the slave SQL thread has executed up to. So in your example, the slave db has executed all statements up until binlog mysql-bin.000001 pos 10726644 on the master db.

until_log_pos is really only used if you start your slave with the syntax START SLAVE UNTIL master_log_pos = $integer. That syntax will replicate up to that position (it will be the exec_master_log_pos) and then stop. You would normally only do this if you wanted to replicate to a specific point, but no further (like if the next statement is an accidental table drop or something). The value of until_log_pos is 0 when it is not specified, so in your case means that replication will just keep moving forward.

1
  • thank you I am getting even clearer now. Anything else to monitor now?
    – newbie14
    May 30, 2012 at 16:42
0

To determine the binlog file name/position pair on the master that corresponds to the current logical state of the slave use Relay_Master_Log_File/Exec_Master_Log_Pos. I apologize for the confusing names of the parameters, which happened back in the very early days of replication (MySQL versions 3.23/4.0). Initially we just had one slave thread that read the events and immediately applied them. Then Jeremy Zawodny pointed out that this architecture could result in significant data loss if the master permanently died while the slave was running a very long update, so we implemented his suggestion for the relay log. I named the new variables with what made sense to me at the moment, nobody objected, the code was released, and then we had to stick with the names that were originally chosen due to our strong commitment to backwards compatibility. In retrospect, Relay_Master_Log_File perhaps should have been Exec_Master_Log_File, and Master_Log_File should have been Read_Master_Log_File.

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.