Mysql – How to get the MySQL binary log coordinates from the binary files

MySQLmysqlbinlogreplication

I'm attempting to set up replication for a large (120 GB) MySQL database and I messed up.

I shut down the master, copied the data files, and restarted the master. Now I've set up the slave & started it up, and I realize that I neglected to get the MASTER_LOG_FILE and MASTER_LOG_POS values needed to setup replication.

I still have an untouched copy of the data files. Is there any way to determine MASTER_LOG_FILE and MASTER_LOG_POS from those files?

Related question:

  • what happens if you set the MASTER_LOG_POS to an earlier point in time?
  • Am I correct in assuming duplicate queries will be executed, possibly resulting in duplicate data?

Edit

I've been investigating the mysqlbinlog command and discovered what looks like the correct info by executing:

mysqlbinlog --to-last-log --start-datetime='2015-06-21 20:05:00'
    mysql-bin.000006 | grep end_log_pos

The output looks something like this:

... a whole bunch more lines ...

#150621 20:10:21 server id 1  end_log_pos 720159843     Query   thread_id=901489    exec_time=0 error_code=0
#150621 20:10:21 server id 1  end_log_pos 720161877     Query   thread_id=901489    exec_time=0 error_code=0
#150621 20:10:21 server id 1  end_log_pos 720162762     Query   thread_id=901489    exec_time=0 error_code=0
#150621 20:10:22 server id 1  end_log_pos 720164796     Query   thread_id=901489    exec_time=0 error_code=0
#150621 20:10:22 server id 1  end_log_pos 720164815     Stop

I'm guessing I should use one of those last two positions, but I'm not sure which.

Best Answer

Hopefully, you did not start mysqld on the slave yet.

It just so happens that a binlog position is also the binlog size at the end of every binlog event. In your case, you can use the last binlog the slave sees, which I can assume is mysql-bin.000006. Get the filesize and use that as the position.

Therefore, on the Slave you would run 

STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST='...',
    MASTER_PORT=3306,
    MASTER_USER='repluser',
    MASTER_PASSWORD='replpassword',
    MASTER_LOG_FILE='mysql-bin.00006',
    MASTER_LOG_POS=720164815;
START SLAVE;

and that should do it !!!

I have discussed the binlog position being the filesize before

You also asked

what happens if you set the MASTER_LOG_POS to an earlier point in time?

Yes, it will replay those binlog event and cause issues.

Related Question