Beware Starting Slaves in the Position in the file

Jan 12, 2011 / By Singer Wang

Tags: ,

I’ve seen many a good DBA make the master of starting slaves from the position in the file, most recently this week, that I want to bring it to everyone’s attention. Of course I mean the underlying issue and not the names of the DBA because that would be cruel.

In the typical scenario where this is an issue, the sequence of events is roughly the same with some small variation. A cold backup or a snapshot is restored onto a new server to build out a new slave. The binary log position from the file, which is part of the backup, is used to start replication. Eventually after a short while, someone notices data discrepancies on the new slave compared to the master or replication stops due to an error.

The problem can be best looked by looking the slave status output in MySQL like below:

mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_User: msandbox
                  Master_Port: 26768
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4723
               Relay_Log_File: mysql_sandbox26769-relay-bin.000002
                Relay_Log_Pos: 874
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                   Last_Errno: 0
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 729
              Relay_Log_Space: 1042
              Until_Condition: None
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
               Last_SQL_Errno: 0
1 row in set (0.00 sec)


There are two sets of coordinates for replication in consideration here, the Execute Master Position and the Read Master Position. The Execute Master Position, denoted by the ‘Relay_Master_Log_File’ and Exec_Master_Log_Pos’ in the output above, is the position in the master’s binary log of the current statement being executed by the slave from the relay log. The Read Master Position, denoted by the ‘Master_Log_File’ and Exec_Master_Log_Pos’ in the output above, is the position in the master’s binary logs which the slave is reading from and writing to the relay log. So if the slave is lagged for some reason, which can happen often in the MySQL world, the Read Master Position will be different and in fact newer then the Execute Master Position. On the file system, the Read Master Position is stored in the file and the Execute Master Position is stored instead in the file.

So if the slave was lagged when it was shut down for the cold backup or when the snapshot was taken, then the two positions would be different. Consider the following example from a server which was lagged behind.

The file shows

--(Wed:20110112:1527)-(0:$)-- more


The file shows

--(Wed:20110112:1527)-(0:$)-- more

When the replication is started on the new server from the position in the file, the statements in the binary logs between the Execute Master Position and the Read Master Position get skipped and cause data corruption.

In the end, the solution is to always start a new slave using the position in the file.

2 Responses to “Beware Starting Slaves in the Position in the file”

  • huarong says:

    Why not get Master_Log_File and Exec_Master_Log_Pos from `show slave status` ?

    Never try to get information from a file.

  • Singer Wang says:

    You could get it from ‘show slave status’. The only thing you must take care is to stop the slave, get the position, and then do the backup (snapshot or cold backup).

    But when MySQL starts up, it reads it from both the file and the file. Hence we need to parse it properly as DBAs.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>