Not the answer you need?
Register and ask your own question!

Default values for MASTER_LOG_POS in CHANGE MASTER TO statement

ChrisCardChrisCard Current User Role Novice
we have a Perl script which we use to perform failover of a slave to become a master, and I am trying to ensure it works with Percona Server 5.6.
Part of the processing in the script involves calling "CHANGE MASTER TO ..." on all the other slaves, to get replication going to them from the new master.
The script is quite old and has a section which decides on the value to use for MASTER_LOG_POS in the CHANGE MASTER TO statement, based on the version of MySQL running on the new master.
It has the following values defined for different versions of MySQL : 4.1 => 79, 5.1 => 106, 5.5 => 107 and 5.7 => 123, but I don't know what value to use for 5.6 and I can't find any MySQL documentation that tells me why these values have been chosen.
Can anyone help me with this?


  • vaibhav_upadhyay40vaibhav_upadhyay40 Contributor Current User Role Patron
    Hi @ChrisCard
    Basically master_log_pos is the position from which slave should start reading the master binary logs.
    Any values defined in documentation is usually for understanding and for example. It doesn't suggest to use in real-time scenario.
    coming to the which position to specify, it depends at which point the fail-over happened and at that point what is the sync status and binlog position on slave. 

    Hope i have got your question right, hope this helps.

  • ChrisCardChrisCard Current User Role Novice
    edited June 27
    Hi @vaibhav_upadhyay40
    Thanks for you reply.
    I understand what master_log_pos is, but your answer has made be realise that I missed an important point, which is that before calling "CHANGE MASTER TO" on the slaves, the script calls "RESET MASTER" on the new master. So these "magic numbers" are what to set master_log_pos to in that situation for different MySQL  versions, I assume because the format of the binary logs change slightly on each release. What I need is the "magic number" for Percona 5.6.
    UPDATE: some experimentation appears to show that the number is 120 for Percona 5.6, but I'd like some firmer confirmation.
  • vaibhav_upadhyay40vaibhav_upadhyay40 Contributor Current User Role Patron
    edited June 27
    Yes you are right its 120.
    basically the logs position in the prompt will show position # 120 by default, however binary logs internally starts at position #4.
    so i think, this is the magic no/position you are referring to (position no. 4). i am assuming this is what you are looking for. 
    You can use below command to read and check the binarylog position. 
    shell> mysqlbinlog /var/lib/mysql/master-bin.000xxxx --base64-output=DECODE-ROWS > test.txt
    Hope this helps.
  • ChrisCardChrisCard Current User Role Novice
    Thanks for the confirmation. Is the fact that the default master_log_pos is 120 for Percona Server 5.6 documented anywhere?
  • vaibhav_upadhyay40vaibhav_upadhyay40 Contributor Current User Role Patron
    sorry @ChrisCard i have no information on this.

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.