Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I recently experienced an issue where the binlog file in master rotated because network connectivity between the slave and master was unavailable.

After solving the network issue, the slave was not able to follow the master as it was doing through previous binlog and position.

That was solved by purging the binlog to last binlog in master and pointing the slave to that last binlog and previous binlog's position which was following.

I am wondering if this issue is normal?

share|improve this question
    
From my experience, the master couldn't care less about the slaves. Can you explain why you think the master behaved this way (because you lost connectivity)?? I would look at your expire_logs_days configuration. –  Alain Collins Nov 30 '12 at 17:08

1 Answer 1

Whenever a Slave loses network connectivity, the IO thread simply dies and tries to reconnect every 60 seconds. You did the right thing in pointing the Slave back but let's clarify what to use to point back with.

For the give SHOW SLAVE STATUS\G

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.17.20.102
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002814
          Read_Master_Log_Pos: 823078734
               Relay_Log_File: relay-bin.007364
                Relay_Log_Pos: 823078879
        Relay_Master_Log_File: mysql-bin.002814
             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: 0
          Exec_Master_Log_Pos: 823078734
              Relay_Log_Space: 823079071
              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:
1 row in set (0.00 sec)

WHAT YOU SHOULD NOT PICK

  • Master_Log_File represents the log file containing the last successfully executed SQL statement on the Master that was recorded in the Slave's Relay Logs.
  • Read_Master_Log_Pos represents the position within Master_Log_File of the last successfully executed SQL statement on the Master that was recorded in the Slave's Relay Logs.

WHAT YOU SHOULD PICK

  • Relay_Master_Log_File represents the log file containing the last successfully executed SQL statement on the Master that was executed on the Slave.
  • Exec_Master_Log_Pos represents the position within Relay_Master_Log_File of the last successfully executed SQL statement on the Master that was executed on the Slave.

In most cases, when caught early enough, Master_Log_File and Relay_Master_Log_File are the same. When there is significant replication lag or if the SQL thread breaks and the IO Thread keeps collecting, Master_Log_File and Relay_Master_Log_File will be different.

CONCLUSION

ALWAYS PICK Relay_Master_Log_File and Exec_Master_Log_Pos as a restart point.

Here are my other posts that reemphasize this paradigm:

CAVEAT #1

Please do not be concerned with binlog rotation on the Master when it comes to Replication's status. The Slave's IO Thread is very sensitive to the Master's change. Here is how you can verify this:

STEP 01 : On the Slave, run SHOW SLAVE STATUS\G

STEP 02 : On the Master

  • For MySQL 5.5, run FLUSH LOGS; or FLUSH BINARY LOGS;
  • Prior to MySQL 5.5, run FLUSH LOGS;

STEP 03 : On the Slave, run SHOW SLAVE STATUS\G

You will see the Master_Log_File and Read_Master_Log_Pos change immedaitely.

CAVEAT #2

Since Relay_Master_Log_File has the most recent binary from the Master to be executed on the Slave, that's the log you keep on the Master. From the above SHOW SLAVE STATUS\G, you would run

PURGE BINARY LOGS TO 'mysql-bin.002814';
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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